Sunday, November 15, 2015

ERROR: ORA-12154: TNS:could not resolve the connect identifier specified

ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified

Note:  Before doing this activity Pls take a backup of init.ora.5132015144713 and listener.ora files.


C:\Users\Sindhu>lsnrctl start

LSNRCTL for 64-bit Windows: Version 12.1.0.1.0 - Production on 15-NOV-2015 14:01
:13

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Starting tnslsnr: please wait...

TNSLSNR for 64-bit Windows: Version 12.1.0.1.0 - Production
System parameter file is E:\app\Sindhu\product\12.1.0\dbhome_1\network\admin\lis
tener.ora
Log messages written to E:\app\Sindhu\diag\tnslsnr\lucky\listener\alert\log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521
ipc)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 64-bit Windows: Version 12.1.0.1.0 - Produ
ction
Start Date                15-NOV-2015 14:01:18
Uptime                    0 days 0 hr. 0 min. 8 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Default Service           CLRExtProc
Listener Parameter File   E:\app\Sindhu\product\12.1.0\dbhome_1\network\admin\li
stener.ora
Listener Log File         E:\app\Sindhu\diag\tnslsnr\lucky\listener\alert\log.xm
l
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
  Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully


Solution:

SQL> alter system set LOCAL_LISTENER='(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PO
RT=1521))' scope=both;

System altered.

SQL> alter system register;

System altered.


OR

Comment #local_listener=LISTENER_ORCL line in  init.ora.5132015144713 file.



SQL> startup pfile=C:\app\Sindhu\admin\orcl\pfile\init.ora.5132015144713;
ORACLE instance started.

Total System Global Area 2438529024 bytes
Fixed Size                  2405568 bytes
Variable Size             654314304 bytes
Database Buffers         1761607680 bytes
Redo Buffers               20201472 bytes
Database mounted.
Database opened.

SQL> create spfile from pfile='C:\app\Sindhu\admin\orcl\pfile\init.ora.513201514
4713';

File created.








Connected to an idle instance. ORA-00132: syntax error or unresolved network name 'LISTENER_ORCL'

SQL> conn sys as sysdba
Enter password:
Connected to an idle instance.
SQL> startup
ORA-00119: invalid specification for system parameter LOCAL_LISTENER
ORA-00132: syntax error or unresolved network name 'LISTENER_ORCL'



SQL> create pfile from spfile;

File created.

SQL> create spfile from pfile;

File created.

SQL> startup
ORACLE instance started.

Total System Global Area 6396977152 bytes
Fixed Size                  2416968 bytes
Variable Size            1207963320 bytes
Database Buffers         5167382528 bytes
Redo Buffers               19214336 bytes
Database mounted.
Database opened.
SQL> conn sys as sysdba
Enter password:
Connected.

SQL>





Sunday, July 19, 2015

ODISQLUNLOAD exaple


ODISQLUNLOAD syntax:

OdiSqlUnload -FILE=<file_name> -DRIVER=<driver> -URL=<url> -USER=<user> -PASS=<password> [-FILE_FORMAT=<file_format>] [-FIELD_SEP=<field_sep> | -XFIELD_SEP=<field_sep>] [-ROW_SEP=<row_sep> | -XROW_SEP=<row_sep>] [-DATE_FORMAT=<date_format>] [-ABS=<yes|no>] [-CHARSET_ENCODING=<encoding>] [-XML_CHARSET_ENCODING=<encoding>]  [-FETCH_SIZE=<array_fetch_size>] [CR/LF <sql_query> | -QUERY=<sql_query> | -QUERY_FILE=<sql_query_file> ]


 Example:

Create one procedure and below code in command on target

and select Source technology and logical schema on command on source.

OdiSqlUnload "-FILE=C:\temp\sessions.csv" "-DRIVER=<%=odiRef.getInfo( "SRC_JAVA_DRIVER" )%>" "-URL=<%=odiRef.getInfo( "SRC_JAVA_URL" )%>" "-USER=<%=odiRef.getInfo( "SRC_USER_NAME" )%>" "-PASS=<%=odiRef.getInfo( "SRC_ENCODED_PASS" )%>" "-FILE_FORMAT=VARIABLE" "-FIELD_SEP=," "-ROW_SEP=\r\n" "-DATE_FORMAT=yyyy/MM/dd HH:mm:ss" "-CHARSET_ENCODING=ISO8859_1" "-XML_CHARSET_ENCODING=UTF-8" "-FETCH_SIZE=5000" "-QUERY=Select sess_name,sess_beg,sess_status from dev1_odi_repo.snp_session"




Wednesday, June 24, 2015

export and import in oracle database using exp and imp.

export and import in oracle database using exp and imp.


-- Export


exp apps/apps@orcl FILE=E:\apps_exp.dmp LOG=E:\exp_apps.log

-- import

imp apps/apps@orcl file=E:\apps_exp.dmp LOG=E:\imp_apps.log  full=y


Creating Database Link in Orale 11G or 12c Database?

Creating Database Link in Orale 11G or 12c Database?


Before creating DB link first verify  remote database TNS name details are added into your database tnsnames.ora file.

VIS =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.46.128)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = VIS)
    )
  )







Example:  CREATE DATABASE LINK db_link_name CONNECT TO remote_db_username IDENTIFIED BY remote_db_password USING 'remove_db_sid';



CREATE DATABASE LINK appsr12 CONNECT TO apps IDENTIFIED BY apps USING 'vis';



How to kill Database session?

How to kill Database session?

Find SID and SERIAL# values from V$Session view (only admin users can access this table like SYSTEM, SYS..) or Admin role privileged users.

select sid,serial# from v$session where status='ACTIVE' and USERNAME=':MYUSER';

Use below command for killing running session.



ALTER SYSTEM KILL SESSION 'sid,serial#';
alter system kill session'368,169';

Note: Be care full while killing sessions in your database and verify SID and Serieal# values before killing.

Saturday, June 20, 2015

ERROR at line 1: ORA-00600: internal error code, arguments: [kcratr_scan_lastbwr], [], [], [], [], [], [], [], [], [], [], []

SQL> create user mrep identified by mrep;
create user mrep identified by mrep
                               *
ERROR at line 1:
ORA-01109: database not open

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [kcratr_scan_lastbwr], [], [], [],
[], [], [], [], [], [], [], []


SOLUTION: Database need to be recovered using below command.


SQL> alter database recover database;

Database altered.

SQL> alter database open;

Database altered.

SQL> create user mrep identified by mrep;

User created.




Thursday, June 18, 2015

ATG Upgrade error in BIAPPS 11.1.1.8.1 RA-01418: specified index does not exist

SEVERE : Error encountered executing SQL statement  FileName: 'C:\OBIEE\Oracle_BI1\biapps\admin\provisioning\update\11.1.
1.7.2\from11.1.1.7.0\schema\oracle\atg\upgrade.sql' LineNumber: '1985' Script log file: null
Jan 05 2015 01:18:45 SEVERE : Exception in runing sql script
oracle.sysman.assistants.common.dbutil.SQLFatalErrorException: java.sql.SQLException: ORA-01418: specified index does not exist

        at oracle.sysman.assistants.common.dbutil.jdbc.JDBCEngine.onException(JDBCEngine.java:869)
        at oracle.sysman.assistants.common.dbutil.jdbc.JDBCEngine.executeSql(JDBCEngine.java:833)
        at oracle.sysman.assistants.common.dbutil.jdbc.JDBCEngine.executeSql(JDBCEngine.java:773)
        at oracle.sysman.assistants.common.dbutil.jdbc.OracleDropStatement.execute(ANSISQLStatementType.java:749)
        at oracle.sysman.assistants.common.dbutil.jdbc.JDBCEngine.executeNextSQLStatement(JDBCEngine.java:1468)
        at oracle.sysman.assistants.common.dbutil.jdbc.JDBCEngine.parseNexecuteScript(JDBCEngine.java:1372)
        at oracle.as.biapps.upgrade.util.DBUpgradeUtil.executeJDBCEngineScript(DBUpgradeUtil.java:117)
        at oracle.as.biapps.upgrade.atg.ATGSchemaUpgradeTask.execute(ATGSchemaUpgradeTask.java:46)
        at oracle.as.biapps.upgrade.atg.ATGSchemaUpgradeTask.execute(ATGSchemaUpgradeTask.java:15)
        at oracle.as.biapps.upgrade.AbstractUpgradeCommand.executeTask(AbstractUpgradeCommand.java:78)
        at oracle.as.biapps.upgrade.AbstractUpgradeCommand.executeUpgradeTasks(AbstractUpgradeCommand.java:68)
        at oracle.as.biapps.upgrade.AbstractUpgradeCommand.upgrade(AbstractUpgradeCommand.java:28)
        at oracle.as.biapps.upgrade.AbstractUpgradeCommand.execute(AbstractUpgradeCommand.java:21)
        at oracle.as.biapps.upgrade.BIAppsRepositoryUtil.main(BIAppsRepositoryUtil.java:47)
Caused by: java.sql.SQLException: ORA-01418: specified index does not exist

        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:440)
        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
        at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:837)
        at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:445)
        at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:191)
        at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:523)
        at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:207)
        at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:1009)
        at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1315)
        at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3576)
        at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3677)
        at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1374)
        at oracle.sysman.assistants.common.dbutil.jdbc.JDBCEngine.runSqlStatement(JDBCEngine.java:1090)
        at oracle.sysman.assistants.common.dbutil.jdbc.JDBCEngine.callRunSqlStatement(JDBCEngine.java:779)
        at oracle.sysman.assistants.common.dbutil.jdbc.JDBCEngine.executeSql(JDBCEngine.java:794)
        ... 12 more


Solution:
------------
 edit update.sql file, available in
C:\OBIEE11G\Oracle_BI1\biapps\admin\provisioning\update\11.1.1.7.2\from11.1.1.7.0\schema\oracle\atg

issue with duplicate values in below table. due to this it is not allowing unique index.
CREATE UNIQUE INDEX  "FND_ISO_LANGUAGES_TL_U2" ON  "FND_ISO_LANGUAGES_TL" ("NAME", "LANGUAGE","ENTERPRISE_ID")
  PCTFREE 40 INITRANS 11 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 16384 NEXT 8192 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 4 FREELIST GROUPS 32 BUFFER_POOL DEFAULT) ;
  cannot CREATE UNIQUE INDEX; duplicate keys found

comment below lines before upgrading ATG and run those indexex manually in DEV_BIACOMP scema

line no 1985 to 2068

find the below code.

DROP INDEX FND_APPL_TAXONOMY_U1 ;
DROP INDEX FND_APPL_TAXONOMY_U2 ;
DROP INDEX FND_APPL_TAXONOMY_U3 ;
DROP INDEX FND_APPL_TAXONOMY_U4 ;
DROP INDEX FND_APPL_TAXONOMY_U5 ;
DROP INDEX FND_APPL_TAXONOMY_U6 ;
DROP INDEX FND_APPL_TAXONOMY_U8 ;
DROP INDEX FND_ISO_LANGUAGES_TL_U2 ;
DROP INDEX FND_SESSION_USERS_U1 ;
DROP INDEX FND_SESSION_USERS_U2 ;

 --- ('INDEX','FND_APPL_TAXONOMY_U1' ) ---

CREATE UNIQUE INDEX  "FND_APPL_TAXONOMY_U1" ON  "FND_APPL_TAXONOMY" (DECODE("MODULE_TYPE",'PRODUCT_LINE',TO_CHAR("PRODUCT_LINE")||'#'||TO_CHAR("ALTERNATIVE_ID")||'#'||TO_CHAR("ENTERPRISE_ID"),NULL),"ENTERPRISE_ID","MODULE_ID")
  PCTFREE 40 INITRANS 11 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 16384 NEXT 8192 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 4 FREELIST GROUPS 32 BUFFER_POOL DEFAULT) ;

 --- ('INDEX','FND_APPL_TAXONOMY_U2' ) ---

  CREATE UNIQUE INDEX  "FND_APPL_TAXONOMY_U2" ON  "FND_APPL_TAXONOMY" (DECODE("MODULE_TYPE",'FAMILY',TO_CHAR("PRODUCT_LINE")||'#'||TO_CHAR("ALTERNATIVE_ID")||'#'||TO_CHAR("ENTERPRISE_ID"),NULL),"ENTERPRISE_ID","MODULE_ID")
  PCTFREE 40 INITRANS 11 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 16384 NEXT 8192 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 4 FREELIST GROUPS 32 BUFFER_POOL DEFAULT) ;

 --- ('INDEX','FND_APPL_TAXONOMY_U3' ) ---

CREATE UNIQUE INDEX  "FND_APPL_TAXONOMY_U3" ON  "FND_APPL_TAXONOMY" (DECODE("MODULE_TYPE",'APPLICATION',TO_CHAR("PRODUCT_LINE")||'#'||TO_CHAR("ALTERNATIVE_ID")||'#'||TO_CHAR("ENTERPRISE_ID"),NULL),"ENTERPRISE_ID","MODULE_ID")
  PCTFREE 40 INITRANS 11 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 16384 NEXT 8192 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 4 FREELIST GROUPS 32 BUFFER_POOL DEFAULT) ;

 --- ('INDEX','FND_APPL_TAXONOMY_U4' ) ---

CREATE UNIQUE INDEX  "FND_APPL_TAXONOMY_U4" ON  "FND_APPL_TAXONOMY" (DECODE("MODULE_TYPE",'PRODUCT_LINE',TO_CHAR("PRODUCT_LINE")||'#'||"MODULE_KEY"||'#'||TO_CHAR("ENTERPRISE_ID"),NULL),"ENTERPRISE_ID","MODULE_ID")
  PCTFREE 40 INITRANS 11 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 16384 NEXT 8192 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 4 FREELIST GROUPS 32 BUFFER_POOL DEFAULT) ;

 --- ('INDEX','FND_APPL_TAXONOMY_U5' ) ---

CREATE UNIQUE INDEX  "FND_APPL_TAXONOMY_U5" ON  "FND_APPL_TAXONOMY" (DECODE("MODULE_TYPE",'FAMILY',TO_CHAR("PRODUCT_LINE")||'#'||"MODULE_KEY"||'#'||TO_CHAR("ENTERPRISE_ID"),NULL),"ENTERPRISE_ID","MODULE_ID")
  PCTFREE 40 INITRANS 11 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 16384 NEXT 8192 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 4 FREELIST GROUPS 32 BUFFER_POOL DEFAULT) ;

 --- ('INDEX','FND_APPL_TAXONOMY_U6' ) ---

CREATE UNIQUE INDEX  "FND_APPL_TAXONOMY_U6" ON  "FND_APPL_TAXONOMY" (DECODE("MODULE_TYPE",'APPLICATION',TO_CHAR("PRODUCT_LINE")||'#'||"MODULE_KEY"||'#'||TO_CHAR("ENTERPRISE_ID"),NULL), "MODULE_ID","ENTERPRISE_ID")
  PCTFREE 40 INITRANS 11 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 16384 NEXT 8192 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 4 FREELIST GROUPS 32 BUFFER_POOL DEFAULT) ;

--- ('INDEX','FND_APPL_TAXONOMY_U8' ) ---

CREATE BITMAP INDEX  "FND_APPL_TAXONOMY_U8" ON  "FND_APPL_TAXONOMY" (DECODE("MODULE_TYPE",'APPLICATION',TO_CHAR("PRODUCT_LINE")||'#'||"MODULE_NAME"||'#'||TO_CHAR("ENTERPRISE_ID"),NULL), "MODULE_ID","ENTERPRISE_ID")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 131072 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) ;

 --- ('INDEX','FND_NATURAL_LANGUAGES_TL_U2' ) ---


/* 
CREATE UNIQUE INDEX  "FND_ISO_LANGUAGES_TL_U2" ON  "FND_ISO_LANGUAGES_TL" ("NAME", "LANGUAGE","ENTERPRISE_ID")
  PCTFREE 40 INITRANS 11 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 16384 NEXT 8192 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 4 FREELIST GROUPS 32 BUFFER_POOL DEFAULT) ;
*/
--- ('INDEX','FND_SESSION_USERS_U2' ) ---


  CREATE UNIQUE INDEX  "FND_SESSION_USERS_U2" ON  "FND_SESSION_USERS" ("USER_NAME", "ENTERPRISE_ID")
  PCTFREE 40 INITRANS 11 MAXTRANS 255
  STORAGE(INITIAL 16384 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 4 FREELIST GROUPS 32 BUFFER_POOL DEFAULT) ;

 --- ('INDEX','FND_SESSION_USERS_U1' ) ---


  CREATE UNIQUE INDEX  "FND_SESSION_USERS_U1" ON  "FND_SESSION_USERS" ("USER_GUID", "ENTERPRISE_ID")
  PCTFREE 40 INITRANS 11 MAXTRANS 255
  STORAGE(INITIAL 16384 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 4 FREELIST GROUPS 32 BUFFER_POOL DEFAULT) ;

Unable to lock Central Inventory. OPatch will attempt to re-lock

Unable to lock Central Inventory. OPatch will attempt to re-lock


 OiiolLogger.addFileHandler:Error while adding file handler - C:\Program Files\Oracle\Inventory/logs\OPatch2015-06-18_12-24-59-PM.logjava.io.FileNotFoundException: C:\Program Files\Oracle\Inventory\logs\OPatch2015-06-18_12-24-59-PM.log (Access is denied)Unable to lock Central Inventory.  OPatch will attempt to re-lock.Do you want to proceed? [y|n]Y
(auto-answered by -silent)User Responded with: Y

OPatch will sleep for few seconds, before re-trying to get the lock...

It is an issue with privileges.


Solution:
-----------
 In Windows:  C:\Program Files\Oracle\Inventory\ContentsXML
 right click on directory=>properties=>Security=>select full control for your windows users.
In Unix or Linux:   /oracle/oraInventory/ContentsXML/

we need to grant  777  (read , write and execute privileges) to ContextsXML



Tuesday, June 9, 2015

BI Publisher error : Server not initialized. Please make sure the repository is ready

BI Publisher error : Server not initialized. Please make sure the repository is ready














Solution:
----------
 MW_HOME\user_projects\domains\bifoundation_domain\config\bipublisher

Edit  xmlp-server-config.xml file add below two lines and if already exists verify both repository path.


<?xml version="1.0" encoding="UTF-8" standalone="no"?><xmlpConfig xmlns="http://xmlns.oracle.com/oxp/xmlp">
  
   <resource>
      <file path="${xdo.server.config.dir}/repository"/>
   </resource>
   <config>
      <file path="${xdo.server.config.dir}/repository"/>
   </config>
</xmlpConfig>

Friday, June 5, 2015

ORA-31626: job does not exist ORA-06512: at "SYS.DBMS_DATAPUMP" error


ORA-31626: job does not exist
 ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
 ORA-06512: at "SYS.DBMS_DATAPUMP", line 1470
 ORA-06512: at "SYS.DBMS_DATAPUMP", line 5887
 ORA-06512: at line 1
 
 
Solution:
----------
there are different situation we will get this error.
 
1) If your database user dont have access like execute privileges for DBMS_DATAPUMP 
   package we will get this error
 
Sol: grant execute privileges to user.
      SQL> conn SYS as SYSDBA
      SQL> grant execute on DBMS_DATAPUMP to username;
 
2) IF previous jobs are not finished or idle state then we will get this error.
    using below query we can verify existing jobs.
 
SQL> SELECT owner_name, job_name, operation, job_mode, state, attached_sessions FROM dba_datapump_jobs;
  
OWNER_NAME JOB_NAME             OPERATION  JOB_MODE   STATE        ATTACHED_SESSIONS
---------- -------------------- ---------- ---------- ------------ -----------------
SYS        BIAPPSDBJOB          IMPORT     FULL       NOT RUNNING   0
 
 
drop table using
 
SQL>  DROP TABLE BIAPPDBJOB;
 
verify it is available in recyclebin using above query again, then  purge using like below.
 
SQL> PURGE TABLE BIAPPDBJOB; 
 
 
 
 
 
 
 

Thursday, May 21, 2015

OBIEE 11.1.1.9 Administrator Features

OBIEE 11.1.1.9 Administrator Features.
Mainly on presentation services diagnostics and dynamic log level  and filtering.

  1. Set dynamic log level per session from manage sessions
  2. Filter cursor cache based on specific user sessions
  3. Change sort order of cursor cache
  4. Show Presentation Services diagnostics per cursor





OBIEE 11G 11.1.1.9 View Types features TREEMAP

OBIEE 11G TREEMAP added in 11.1.1.9 version and it was there in BICS (BI Cloud Services) OR
11G 11.1.1.7.10 update as well.




Monday, May 18, 2015

Oracle Data Integrator 11G 11.1.1.9 Available for downloads with wallet and Asynchronous Web Services features

Oracle Data Integrator 11G 11.1.1.9 Available for downloads with wallet and Asynchronous Web Services features

Note:  Wallet feature already available in 12C.

http://www.oracle.com/technetwork/middleware/data-integrator/downloads/index.html

Oracle Data Integrator 11.1.1.9.0 New Features

password-protected wallet.: 

Studio Login Security Enhancements Oracle Data Integrator Studio login credentials can now be stored in a password-protected wallet. In order to login to the Oracle Data Integrator Studio, a login profile must be created where the connectivity to the ODI repository and the user credentials are specified. Oracle Data Integrator can now save these credentials in either an encrypted login XML file or a password-protected wallet. Depending on your security requirements you can now choose either of them.

 Oracle Data Integrator Asynchronous Web Services 

The Oracle Data Integrator agent provides web services for invoking Oracle Data Integrator sessions in both synchronous and asynchronous modes. In the synchronous mode, the web services invocation is blocked until the session execution finishes. However, in the asynchronous mode the web services call is not blocked and the caller provides details of the end-point where the response should be sent on session completion. Oracle Data Integrator can now send responses to such asynchronous invocation to endpoints that are secured with Oracle Web Service Manager (OWSM) policies


Source : Oracle
http://www.oracle.com/technetwork/middleware/data-integrator/downloads/index.html


Saturday, May 16, 2015

WebLogic Server instance as Windows Service and odi server as windows service

WebLogic Server instance as Windows Service and odi server as windows service

Create below .cmd files in C:\Oracle\Middleware\wlserver_10.3\server\bin.
*************************************************************************
Create two duplicate files from installSvc.cmd file and rename like below two files

1) installAdmSvc.cmd
2) installODISvc.cmd
and Add below line  in both files.

set JAVA_VM=-server














***************************
InstallAdminServer_Service.cmd
***************************

echo off
SETLOCAL
set JAVA_VENDOR=sun
set JAVA_HOME=C:\Java\jdk1.6.0_45
set DOMAIN_NAME=base_domain
set USERDOMAIN_HOME=C:\Oracle\Middleware\user_projects\domains\base_domain
set SERVER_NAME=AdminServer
set WLS_USER=weblogic
set WLS_PW=Admin123
set PRODUCTION_MODE=true
set MEM_ARGS=-Xms512m -Xmx1024m
cd %USERDOMAIN_HOME%
call %USERDOMAIN_HOME%\bin\setDomainEnv.cmd
call "C:\Oracle\Middleware\wlserver_10.3\server\bin\installAdmSvc.cmd"
ENDLOCAL

*************************
InstallODIServer_Service.cmd
*************************

echo off
SETLOCAL
set JAVA_VENDOR=sun
set JAVA_HOME=C:\Java\jdk1.6.0_45
set DOMAIN_NAME=base_domain
set USERDOMAIN_HOME=C:\Oracle\Middleware\user_projects\domains\base_domain
set SERVER_NAME=odi_server1
set WLS_USER=weblogic
set WLS_PW=Admin123
set PRODUCTION_MODE=true
set ADMIN_URL=http://localhost:7001
set MEM_ARGS=-Xms512m -Xmx1024m
cd %USERDOMAIN_HOME%
call %USERDOMAIN_HOME%\bin\setDomainEnv.cmd
call "C:\Oracle\Middleware\wlserver_10.3\server\bin\installODISvc.cmd"
ENDLOCAL


***********************************************************************
Installing AdminServer and odi_server1 using command line with Administrator user
***********************************************************************


















For debugging we can use below command.

cd C:\Oracle\Middleware\wlserver_10.3\server\bin
beasvc -debug "beasvc base_domain_AdminServer"
beasvc -debug "beasvc base_domain_odi_server1"





***********************************************************************
For Unstallation we can create new file for Adminserver and odi_server1
***********************************************************************

uninstallAdmSvc.cmd
*******************

echo off
SETLOCAL
set DOMAIN_NAME=base_domain
set SERVER_NAME=AdminServer
call "C:\Oracle\Middleware\wlserver_10.3\server\bin\uninstallSvc.cmd"
ENDLOCAL

uninstallAdmSvc.cmd
*******************

echo off
SETLOCAL
set DOMAIN_NAME=base_domain
set SERVER_NAME=odi_server1
call "C:\Oracle\Middleware\wlserver_10.3\server\bin\uninstallSvc.cmd"
ENDLOCAL

sc delete "beasvc base_domain_odi_server1"

 

***********************************************************************
Verify AdminServer and odi_server1 log files for errors
***********************************************************************

AdminServer.log file location
 C:\Oracle\Middleware\user_projects\domains\base_domain\servers\AdminServer\logs

odi_server1.log file location
C:\Oracle\Middleware\user_projects\domains\base_domain\servers\odi_server1\logs 


***********************************************************************
********************  COMMON ERRORS**********************
***********************************************************************


***********************************************************************
If we get error like  "java.lang.OutOfMemoryError: PermGen space"
***********************************************************************
 set MEM_ARGS=-Xms512m -Xmx1024m
and change in setDomainEnv.cmd
available in 
C:\Oracle\Middleware\user_projects\domains\base_domain\bin\setDomainEnv.cmd

***********************************************************************
If we get error like The object identified by: '31' could not be found
***********************************************************************
 remove admin url line or change port number as Adminserver host n port number like

set ADMIN_URL=http://localhost:7001


***********************************************************************
If we get error like Premature end of file encountered then copy below file
***********************************************************************

Error encountered when starting ADMIN Server. This is due to corrupted system-jazn-data.xml file.
Replace it in
Copy "system-jazn-data.xml" from
      ********************
SOURCE : C:\Oracle\Middleware\oracle_common\modules\oracle.jps_11.1.1\domain_config TO
DEST :  C:\Oracle\Middleware\user_projects\domains\base_domain\config\fmwconfig 

Saturday, March 28, 2015

ODI SCD TYPE 3 (Slowly Changing Dimension) Implementation in ODI.

ODI SCD TYPE 3 (Slowly Changing Dimension) Implementation in ODI.

Step1: Create duplicate on update existing rows in IKM Oracle Incremental update and change that code like below

step2: In interface prev_Sal column deselect INSERT and UPDATE and Select UD2 option in quick edit as well as select CUR_SAL UD1.




ODI SCD Type 2 Implementation step by step process, Oracle Data Integrator Interface example for implementing Slowly changing dimension Type 2.


ODI SCD Type 2 Implementation step by step process,
Oracle Data Integrator Interface example for implementing Slowly changing dimension Type 2.



Thursday, March 5, 2015

What is Multi-connection IKM ? And When its required?

What is Multi-connection IKM ? And When its required?

In interfaces if you are using staging area different from target like other databases or internal memory engine that time we need Multi -connection IKM, Oracle providing some of multi connection IKM's like 
1) IKM sql to sql control append
2) IKM sql to sql incremental update
3) IKM sql to file append .....

if we are not selecting multi connection ikm  we need to select Two LKM's
1) one LKM for source to staging 
2) 2nd LKM for staging to target staging (again it will create staging in target)








Wednesday, February 25, 2015

Creating ODI 12c Colocated Agent Nodemanager as Windows Service in Windows 8

Creating ODI 12c Colocated Agent as Windows Service in Windows 8.

First we need to Configure Domain and Start the Weblogic services.
Then Create agent in Topology as OracleDIAgent1 & Portno: 20910
Then call agent using command line for first time to apply all agent sdk jar files.

Then Follow the below steps to install nodemanager as windows service.
Step1:
Goto  C:\ODI_12c\OracleHome\user_projects\domains\odi_domain\bin directory there we can find the installNodeMgrSvc.cmd













Step2:
Open CMD as Administrator user and Change directory to  Domain\bin directory
CD C:\ODI_12c\OracleHome\user_projects\domains\odi_domain\bin
call installNodeMgrSvc.cmd and it will create Windows service.









Step3:  Open Serices.msc file and here we can see service is created.















step4: Create  startColocated_Agent.cmd  file in C:\ODI_12c\Oracle_Home\user_projects\domains\odi_domain\bin directory
    SETLOCAL                                                                                                                                                                              

set DOMAIN_HOME=C:\ODI_12c\Oracle_Home\user_projects\domains\odi_domain

call %DOMAIN_HOME%\bin\startComponent.cmd OracleDIAgent1

ENDLOCAL
step5:  call that startColocated_Agent.cmd file and it will ask password for Nodemanager
Password: Admin123























Test the agent in Topology















step6: Stopping agent command script creation.

create below commands as cmd file as stopColocated_Agent.cmd

 C:\ODI_12c\Oracle_Home\user_projects\domains\odi_domain\bin



SETLOCAL

set DOMAIN_HOME=C:\ODI_12c\Oracle_Home\user_projects\domains\odi_domain

call %DOMAIN_HOME%\bin\agentstop.cmd -NAME=OracleDIAgent1

ENDLOCAL









Monday, February 23, 2015

Creating Oracle Data Integrator 11g Standalone Agent as Windows Service in windows 8



Download YAJSW zip from below link.  We are using yajsw for odi agent as  service configuration

Yet Another Java Service Wrapper.

 find the steps in Oracle Blog.

step1)  download YAJSW
step2) unzip and copy into ODI Home location.










step3: start your standalone agent using cmd line


step4:  Goto Task manager and find PID for CMD line started agent as you can find java.exe

 

 

 

 

 

 

 

 

 

step5:  Open CMD and CD  C:\OBIA\Oracle_ODI1\yajsw-stable-11.11\bat  and call Genconfig.bat  PID (5500)

 

 Enter and It will be created    wrapper.conf  file in   

C:\OBIA\Oracle_ODI1\yajsw-stable-11.11\conf

  

 

 

 

 

 

 step6:  edit wrapper.conf file and change the below details in this faile.

SUPERVISOR and Master repository details will be fetched from odiparams.bat file only we need to change if password contains any , (comma) then add \  like  \,


 

 

 

 

 

 

 


step7: Save the file and and create agent in Topology.

step8: call  runConsole.bat file

 

 

 

 

 

 

step10:  service is created and goto services.msc file check service.

 

 

 

 

 

 

 

 

 

 

Goto Topology Agent and Click on test agent.


Sunday, February 22, 2015

ORA-01172: recovery of thread 1 stuck at block 224 of file 3 while starting database

While restarting my database it is able start database but it is failing for mounting and open database
file (dbfile).  I got below error while starting database.

ORA-01172: recovery of thread 1 stuck at block 224 of file 3
ORA-01151: use media recovery to recover block, restore backup if needed

I have did below fix for this issues. and after that its working fine.

SQL> Recover datafile 3;

 media recovery complete.
SQL> alter database open;
 database altered.










Tuesday, February 17, 2015

ODI Practice Database Source and target schemas creatioin

DROP USER SDEV CASCADE;
DROP USER STEST CASCADE;
DROP USER SPROD CASCADE;
DROP USER TDEV CASCADE;
DROP USER TTEST CASCADE;
DROP USER TPROD CASCADE;
CREATE USER SDEV IDENTIFIED by welcome;
CREATE USER STEST IDENTIFIED BY welcome;
CREATE USER SPROD IDENTIFIED BY welcome;
GRANT ALL PRIVILEGES TO SDEV,STEST,SPROD;
CREATE USER TDEV IDENTIFIED by welcome;
CREATE USER TTEST IDENTIFIED BY welcome;
CREATE USER TPROD IDENTIFIED BY welcome;
GRANT ALL PRIVILEGES TO TDEV,TTEST,TPROD;
CREATE TABLE SDEV.BONUS
        (ENAME VARCHAR2(10),
         JOB   VARCHAR2(9),
         SAL   NUMBER,
         COMM  NUMBER);
CREATE TABLE SDEV.EMP
       (EMPNO NUMBER(4) NOT NULL,
        ENAME VARCHAR2(10),
        JOB VARCHAR2(9),
        MGR NUMBER(4),
        HIREDATE DATE,
        SAL NUMBER(7, 2),
        COMM NUMBER(7, 2),
        DEPTNO NUMBER(2));
INSERT INTO SDEV.EMP VALUES
        (7369, 'SMITH',  'CLERK',     7902,
        TO_DATE('17-DEC-1980', 'DD-MON-YYYY'),  800, NULL, 20);
INSERT INTO SDEV.EMP VALUES
        (7499, 'ALLEN',  'SALESMAN',  7698,
        TO_DATE('20-FEB-1981', 'DD-MON-YYYY'), 1600,  300, 30);
INSERT INTO SDEV.EMP VALUES
        (7521, 'WARD',   'SALESMAN',  7698,
        TO_DATE('22-FEB-1981', 'DD-MON-YYYY'), 1250,  500, 30);
INSERT INTO SDEV.EMP VALUES
        (7566, 'JONES',  'MANAGER',   7839,
        TO_DATE('2-APR-1981', 'DD-MON-YYYY'),  2975, NULL, 20);
INSERT INTO SDEV.EMP VALUES
        (7654, 'MARTIN', 'SALESMAN',  7698,
        TO_DATE('28-SEP-1981', 'DD-MON-YYYY'), 1250, 1400, 30);
INSERT INTO SDEV.EMP VALUES
        (7698, 'BLAKE',  'MANAGER',   7839,
        TO_DATE('1-MAY-1981', 'DD-MON-YYYY'),  2850, NULL, 30);
INSERT INTO SDEV.EMP VALUES
        (7782, 'CLARK',  'MANAGER',   7839,
        TO_DATE('9-JUN-1981', 'DD-MON-YYYY'),  2450, NULL, 10);
INSERT INTO SDEV.EMP VALUES
        (7788, 'SCOTT',  'ANALYST',   7566,
        TO_DATE('09-DEC-1982', 'DD-MON-YYYY'), 3000, NULL, 20);
INSERT INTO SDEV.EMP VALUES
        (7839, 'KING',   'PRESIDENT', NULL,
        TO_DATE('17-NOV-1981', 'DD-MON-YYYY'), 5000, NULL, 10);
INSERT INTO SDEV.EMP VALUES
        (7844, 'TURNER', 'SALESMAN',  7698,
        TO_DATE('8-SEP-1981', 'DD-MON-YYYY'),  1500, NULL, 30);
INSERT INTO SDEV.EMP VALUES
        (7876, 'ADAMS',  'CLERK',     7788,
        TO_DATE('12-JAN-1983', 'DD-MON-YYYY'), 1100, NULL, 20);
INSERT INTO SDEV.EMP VALUES
        (7900, 'JAMES',  'CLERK',     7698,
        TO_DATE('3-DEC-1981', 'DD-MON-YYYY'),   950, NULL, 30);
INSERT INTO SDEV.EMP VALUES
        (7902, 'FORD',   'ANALYST',   7566,
        TO_DATE('3-DEC-1981', 'DD-MON-YYYY'),  3000, NULL, 20);
INSERT INTO SDEV.EMP VALUES
        (7934, 'MILLER', 'CLERK',     7782,
        TO_DATE('23-JAN-1982', 'DD-MON-YYYY'), 1300, NULL, 10);

CREATE TABLE SDEV.DEPT
       (DEPTNO NUMBER(2),
        DNAME VARCHAR2(14),
        LOC VARCHAR2(13) );
INSERT INTO SDEV.DEPT VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO SDEV.DEPT VALUES (20, 'RESEARCH',   'DALLAS');
INSERT INTO SDEV.DEPT VALUES (30, 'SALES',      'CHICAGO');
INSERT INTO SDEV.DEPT VALUES (40, 'OPERATIONS', 'BOSTON');
CREATE TABLE SDEV.SALGRADE
        (GRADE NUMBER,
         LOSAL NUMBER,
         HISAL NUMBER);
INSERT INTO SDEV.SALGRADE VALUES (1,  700, 1200);
INSERT INTO SDEV.SALGRADE VALUES (2, 1201, 1400);
INSERT INTO SDEV.SALGRADE VALUES (3, 1401, 2000);
INSERT INTO SDEV.SALGRADE VALUES (4, 2001, 3000);
INSERT INTO SDEV.SALGRADE VALUES (5, 3001, 9999);
COMMIT;
CREATE TABLE STEST.BONUS
        (ENAME VARCHAR2(10),
         JOB   VARCHAR2(9),
         SAL   NUMBER,
         COMM  NUMBER);
CREATE TABLE STEST.EMP
       (EMPNO NUMBER(4) NOT NULL,
        ENAME VARCHAR2(10),
        JOB VARCHAR2(9),
        MGR NUMBER(4),
        HIREDATE DATE,
        SAL NUMBER(7, 2),
        COMM NUMBER(7, 2),
        DEPTNO NUMBER(2));
INSERT INTO STEST.EMP VALUES
        (7369, 'SMITH',  'CLERK',     7902,
        TO_DATE('17-DEC-1980', 'DD-MON-YYYY'),  800, NULL, 20);
INSERT INTO STEST.EMP VALUES
        (7499, 'ALLEN',  'SALESMAN',  7698,
        TO_DATE('20-FEB-1981', 'DD-MON-YYYY'), 1600,  300, 30);
INSERT INTO STEST.EMP VALUES
        (7521, 'WARD',   'SALESMAN',  7698,
        TO_DATE('22-FEB-1981', 'DD-MON-YYYY'), 1250,  500, 30);
INSERT INTO STEST.EMP VALUES
        (7566, 'JONES',  'MANAGER',   7839,
        TO_DATE('2-APR-1981', 'DD-MON-YYYY'),  2975, NULL, 20);
INSERT INTO STEST.EMP VALUES
        (7654, 'MARTIN', 'SALESMAN',  7698,
        TO_DATE('28-SEP-1981', 'DD-MON-YYYY'), 1250, 1400, 30);
INSERT INTO STEST.EMP VALUES
        (7698, 'BLAKE',  'MANAGER',   7839,
        TO_DATE('1-MAY-1981', 'DD-MON-YYYY'),  2850, NULL, 30);
INSERT INTO STEST.EMP VALUES
        (7782, 'CLARK',  'MANAGER',   7839,
        TO_DATE('9-JUN-1981', 'DD-MON-YYYY'),  2450, NULL, 10);
INSERT INTO STEST.EMP VALUES
        (7788, 'SCOTT',  'ANALYST',   7566,
        TO_DATE('09-DEC-1982', 'DD-MON-YYYY'), 3000, NULL, 20);
INSERT INTO STEST.EMP VALUES
        (7839, 'KING',   'PRESIDENT', NULL,
        TO_DATE('17-NOV-1981', 'DD-MON-YYYY'), 5000, NULL, 10);
INSERT INTO STEST.EMP VALUES
        (7844, 'TURNER', 'SALESMAN',  7698,
        TO_DATE('8-SEP-1981', 'DD-MON-YYYY'),  1500, NULL, 30);
INSERT INTO STEST.EMP VALUES
        (7876, 'ADAMS',  'CLERK',     7788,
        TO_DATE('12-JAN-1983', 'DD-MON-YYYY'), 1100, NULL, 20);
INSERT INTO STEST.EMP VALUES
        (7900, 'JAMES',  'CLERK',     7698,
        TO_DATE('3-DEC-1981', 'DD-MON-YYYY'),   950, NULL, 30);
INSERT INTO STEST.EMP VALUES
        (7902, 'FORD',   'ANALYST',   7566,
        TO_DATE('3-DEC-1981', 'DD-MON-YYYY'),  3000, NULL, 20);
INSERT INTO STEST.EMP VALUES
        (7934, 'MILLER', 'CLERK',     7782,
        TO_DATE('23-JAN-1982', 'DD-MON-YYYY'), 1300, NULL, 10);

CREATE TABLE STEST.DEPT
       (DEPTNO NUMBER(2),
        DNAME VARCHAR2(14),
        LOC VARCHAR2(13) );
INSERT INTO STEST.DEPT VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO STEST.DEPT VALUES (20, 'RESEARCH',   'DALLAS');
INSERT INTO STEST.DEPT VALUES (30, 'SALES',      'CHICAGO');
INSERT INTO STEST.DEPT VALUES (40, 'OPERATIONS', 'BOSTON');
CREATE TABLE STEST.SALGRADE
        (GRADE NUMBER,
         LOSAL NUMBER,
         HISAL NUMBER);
INSERT INTO STEST.SALGRADE VALUES (1,  700, 1200);
INSERT INTO STEST.SALGRADE VALUES (2, 1201, 1400);
INSERT INTO STEST.SALGRADE VALUES (3, 1401, 2000);
INSERT INTO STEST.SALGRADE VALUES (4, 2001, 3000);
INSERT INTO STEST.SALGRADE VALUES (5, 3001, 9999);
COMMIT;
CREATE TABLE SPROD.BONUS
        (ENAME VARCHAR2(10),
         JOB   VARCHAR2(9),
         SAL   NUMBER,
         COMM  NUMBER);
CREATE TABLE SPROD.EMP
       (EMPNO NUMBER(4) NOT NULL,
        ENAME VARCHAR2(10),
        JOB VARCHAR2(9),
        MGR NUMBER(4),
        HIREDATE DATE,
        SAL NUMBER(7, 2),
        COMM NUMBER(7, 2),
        DEPTNO NUMBER(2));
INSERT INTO SPROD.EMP VALUES
        (7369, 'SMITH',  'CLERK',     7902,
        TO_DATE('17-DEC-1980', 'DD-MON-YYYY'),  800, NULL, 20);
INSERT INTO SPROD.EMP VALUES
        (7499, 'ALLEN',  'SALESMAN',  7698,
        TO_DATE('20-FEB-1981', 'DD-MON-YYYY'), 1600,  300, 30);
INSERT INTO SPROD.EMP VALUES
        (7521, 'WARD',   'SALESMAN',  7698,
        TO_DATE('22-FEB-1981', 'DD-MON-YYYY'), 1250,  500, 30);
INSERT INTO SPROD.EMP VALUES
        (7566, 'JONES',  'MANAGER',   7839,
        TO_DATE('2-APR-1981', 'DD-MON-YYYY'),  2975, NULL, 20);
INSERT INTO SPROD.EMP VALUES
        (7654, 'MARTIN', 'SALESMAN',  7698,
        TO_DATE('28-SEP-1981', 'DD-MON-YYYY'), 1250, 1400, 30);
INSERT INTO SPROD.EMP VALUES
        (7698, 'BLAKE',  'MANAGER',   7839,
        TO_DATE('1-MAY-1981', 'DD-MON-YYYY'),  2850, NULL, 30);
INSERT INTO SPROD.EMP VALUES
        (7782, 'CLARK',  'MANAGER',   7839,
        TO_DATE('9-JUN-1981', 'DD-MON-YYYY'),  2450, NULL, 10);
INSERT INTO SPROD.EMP VALUES
        (7788, 'SCOTT',  'ANALYST',   7566,
        TO_DATE('09-DEC-1982', 'DD-MON-YYYY'), 3000, NULL, 20);
INSERT INTO SPROD.EMP VALUES
        (7839, 'KING',   'PRESIDENT', NULL,
        TO_DATE('17-NOV-1981', 'DD-MON-YYYY'), 5000, NULL, 10);
INSERT INTO SPROD.EMP VALUES
        (7844, 'TURNER', 'SALESMAN',  7698,
        TO_DATE('8-SEP-1981', 'DD-MON-YYYY'),  1500, NULL, 30);
INSERT INTO SPROD.EMP VALUES
        (7876, 'ADAMS',  'CLERK',     7788,
        TO_DATE('12-JAN-1983', 'DD-MON-YYYY'), 1100, NULL, 20);
INSERT INTO SPROD.EMP VALUES
        (7900, 'JAMES',  'CLERK',     7698,
        TO_DATE('3-DEC-1981', 'DD-MON-YYYY'),   950, NULL, 30);
INSERT INTO SPROD.EMP VALUES
        (7902, 'FORD',   'ANALYST',   7566,
        TO_DATE('3-DEC-1981', 'DD-MON-YYYY'),  3000, NULL, 20);
INSERT INTO SPROD.EMP VALUES
        (7934, 'MILLER', 'CLERK',     7782,
        TO_DATE('23-JAN-1982', 'DD-MON-YYYY'), 1300, NULL, 10);

CREATE TABLE SPROD.DEPT
       (DEPTNO NUMBER(2),
        DNAME VARCHAR2(14),
        LOC VARCHAR2(13) );
INSERT INTO SPROD.DEPT VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO SPROD.DEPT VALUES (20, 'RESEARCH',   'DALLAS');
INSERT INTO SPROD.DEPT VALUES (30, 'SALES',      'CHICAGO');
INSERT INTO SPROD.DEPT VALUES (40, 'OPERATIONS', 'BOSTON');
CREATE TABLE SPROD.SALGRADE
        (GRADE NUMBER,
         LOSAL NUMBER,
         HISAL NUMBER);
INSERT INTO SPROD.SALGRADE VALUES (1,  700, 1200);
INSERT INTO SPROD.SALGRADE VALUES (2, 1201, 1400);
INSERT INTO SPROD.SALGRADE VALUES (3, 1401, 2000);
INSERT INTO SPROD.SALGRADE VALUES (4, 2001, 3000);
INSERT INTO SPROD.SALGRADE VALUES (5, 3001, 9999);
COMMIT;
CREATE TABLE TDEV.BONUS
        (ENAME VARCHAR2(10),
         JOB   VARCHAR2(9),
         SAL   NUMBER,
         COMM  NUMBER);
CREATE TABLE TDEV.EMP
       (EMPNO NUMBER(4) NOT NULL,
        ENAME VARCHAR2(10),
        JOB VARCHAR2(9),
        MGR NUMBER(4),
        HIREDATE DATE,
        SAL NUMBER(7, 2),
        COMM NUMBER(7, 2),
        DEPTNO NUMBER(2));

CREATE TABLE TDEV.DEPT
       (DEPTNO NUMBER(2),
        DNAME VARCHAR2(14),
        LOC VARCHAR2(13) );
CREATE TABLE TDEV.SALGRADE
        (GRADE NUMBER,
         LOSAL NUMBER,
         HISAL NUMBER);
COMMIT;
CREATE TABLE TTEST.BONUS
        (ENAME VARCHAR2(10),
         JOB   VARCHAR2(9),
         SAL   NUMBER,
         COMM  NUMBER);
CREATE TABLE TTEST.EMP
       (EMPNO NUMBER(4) NOT NULL,
        ENAME VARCHAR2(10),
        JOB VARCHAR2(9),
        MGR NUMBER(4),
        HIREDATE DATE,
        SAL NUMBER(7, 2),
        COMM NUMBER(7, 2),
        DEPTNO NUMBER(2));

CREATE TABLE TTEST.DEPT
       (DEPTNO NUMBER(2),
        DNAME VARCHAR2(14),
        LOC VARCHAR2(13) );
CREATE TABLE TTEST.SALGRADE
        (GRADE NUMBER,
         LOSAL NUMBER,
         HISAL NUMBER);
COMMIT;
CREATE TABLE TPROD.BONUS
        (ENAME VARCHAR2(10),
         JOB   VARCHAR2(9),
         SAL   NUMBER,
         COMM  NUMBER);
CREATE TABLE TPROD.EMP
       (EMPNO NUMBER(4) NOT NULL,
        ENAME VARCHAR2(10),
        JOB VARCHAR2(9),
        MGR NUMBER(4),
        HIREDATE DATE,
        SAL NUMBER(7, 2),
        COMM NUMBER(7, 2),
        DEPTNO NUMBER(2));

CREATE TABLE TPROD.DEPT
       (DEPTNO NUMBER(2),
        DNAME VARCHAR2(14),
        LOC VARCHAR2(13) );
CREATE TABLE TPROD.SALGRADE
        (GRADE NUMBER,
         LOSAL NUMBER,
         HISAL NUMBER);
COMMIT;