Thursday 27 June 2013

ORA-00600: internal error code, arguments: [ktrget2:kcbz_objdchk]

Error:==
ORA-00600:  [ktrget2:kcbz_objdchk]

select TIMESLOT_emp,TIMESLOT2US_imp from emp ,emp a, b,timeslot
where usedby2emp=a.empid
and uses2emp=b.empid
and a.emp2emptype=0000010
and b.emp2emptype=0000009
and TIMESLOT_emp=a.empid
and TIMESLOT_imp=b.empid
group by TIMESLOT_emp,TIMESLOT_imp
having count(*)>1;

*
ERROR at line 1:
ORA-00600: internal error code, arguments: [ktrget2:kcbz_objdchk], [12], [0],[31], [], [], [], [], [], [], [], []






Solution :===

flush the shared pool 3 times :-
alter system flush shared_pool

Please run following query to check which objects the block belongs to:
SQL> SELECT tablespace_name, segment_type, owner, segment_name
FROM dba_extents
WHERE relative_fno = 26
AND 58378 between block_id AND block_id + blocks - 1;


TABLESPACE_NAME SEGMENT_TYPE OWNER SEGMENT_NAME
------------------------------ ------------------ ------------------------------ ---------------------------------------------------------------------------------
USR INDEX CMD EMP_CC_FK_I


SELECT object_id, data_object_id, status FROM dba_objects WHERE owner = 'CMD AND object_name = 'EMP_CC_FK_I'
AND object_type = 'INDEX';


OBJECT_ID DATA_OBJECT_ID STATUS
---------- -------------- -------
244663 1156881 VALID


Please rebuild the index:
SQL> ALTER INDEX cmd.emp_cc_fk_i REBUILD ONLINE;

Than analyze the index again to check it ORA-8103 are solved:

SQL> ANALYZE INDEX cmd.emp_cc_fk_i VALIDATE STRUCTURE;

after both the (Rebuild & Analyze) complete please try again same SQL QUERY.

Friday 21 June 2013

Create/Drop database link from another schema

Create DB Link from another schema:-

SQL> CREATE or replace PROCEDURE DATALOAD.create_db_link AS
 BEGIN
 EXECUTE IMMEDIATE 'create database link VSNLDEV1 connect to DATALOAD identified by xxxxx using ''CRAMER''';
 END create_db_link;
 /

Procedure created.

SQL> show user
 USER is "SYS"



SQL> exec DATALOAD.create_db_link
PL/SQL procedure successfully completed.

SQL> select * from dba_db_links where OWNER='DATALOAD';
OWNER                          DB_LINK              USERNAME             HOST                 CREATED
 ------------------------------ -------------------- -------------------- -------------------- --------
 DATALOAD                       VSNLDEV1             CRAMER               VSNLDEV1             13-06-08



SQL> drop database link DATALOAD.VSNLDEV1;
 drop database link DATALOAD.VSNLDEV1
 *
 ERROR at line 1:
 ORA-02024: database link not found



Drop DB Link from another schema:-

SQL> CREATE PROCEDURE DATALOAD.drop_db_link AS
 BEGIN
 EXECUTE IMMEDIATE 'drop database link VSNLDEV1';
 END drop_db_link;
 /

Procedure created.

SQL> exec DATALOAD.drop_db_link
PL/SQL procedure successfully completed.

SQL> select * from dba_db_links where OWNER='DATALOAD';
no rows selected

SQL>

For dropping multiple database links:-

 

ERROR 11.2.0\client_1\bin/Emctl Common.pm line 598.

Get below error for start/status emctl in oracle

Error:-

C:\Users\hyporacle>emctl status dbconsole
Can't locate CompEMdbconsole.pm in @INC (@INC contains: %s_javapOracleHome%/sysman/admin/scripts %s_javapOracleHome%/bin D:\win64_11gR2_client\product
\11.2.0\client_1\\perl\bin D:\win64_11gR2_client\product\11.2.0\client_1\perl\lib D:\win64_11gR2_client\product\11.2.0\client_1\perl\lib
0.0 D:\win64_11gR2_client\product\11.2.0\client_1\perl\lib\site_perl D:\win64_11gR2_client\product\11.2.0\client_1\perl\site\lib D:\win64_11gR2_client
\product\11.2.0\client_1\sysman\admin\scripts D:\win64_11gR2_client\product\11.2.0\client_1\sysman\admin\scripts\Net-DNS-0.48\lib D:\win64_11gR2_clien
t\product\11.2.0\client_1\bin D:/win64_11gR2_client/product/11.2.0/client_1/perl/lib D:/win64_11gR2_client/product/11.2.0/client_1/perl/site/lib .) at
 D:\win64_11gR2_client\product\11.2.0\client_1\bin/EmctlCommon.pm line 598.

\site_perl/5.1

Solution:-

Please set ORACLE_SID and ORACLE_HOME then re-run emctl status dbconsole from <ORACLE_HOME\BIN> location


Tuesday 11 June 2013

Oracle Environment variable -->> ORACLE_HOME


What is ORACLE_HOME used for?

* The ORACLE_HOME is an environment variable which is used to set and define the path of Oracle Home (server) Directory.
* The ORACLE_HOME directory will have the sub directories, binaries, executables, programs, scripts, etc. for the Oracle Database.
* This directory can be used by any user who wants to use the particular database.
* If the ORACLE_HOME variable is defined as an environment variable, then during the installation process, the Oracle Home Path will be set to the directory defined as default. If the variable is not defined, then the Oracle will take its own default location. i.e. The ORACLE_HOME variable does not have to be preset as an environment variable, it can be set during the installation process.
* Basically The ORACLE_HOME variable is in the following ORACLE_BASE directory:
ORACLE_HOME=$ORACLE_BASE/product/10.2.0.


What is ORACLE_BASE used for?

* The ORACLE_BASE is also an environment variable to define the base/root level directory where you will have the Oracle Database directory tree - ORACLE_HOME defined under the ORACLE_BASE directory.
* Basically, The ORACLE_BASE directory is a higher-level directory, than ORACLE_HOME, that you can use to install the various Oracle Software Products and the same Oracle base directory can be used for more than one installation.


Note: If you did not set the ORACLE_BASE environment variable before starting OUI, the Oracle home directory is created in an app/username/directory on the first existing and writable directory from /u01 through /u09 for UNIX and Linux systems, or on the disk drive with the most available space for Windows systems. If /u01 through /u09 does not exist on the UNIX or Linux system, then the default location is

user_home_directory/app/username.

How to check if ORACLE_HOME is set already?

On Unix/Linux Systems:
Basically, before or after the Oracle Database is installed, the oracle user profile, the environment variable file, is prepared where all the required environment variables for Oracle are set. i.e. ORACLE_BASE, ORACLE_HOME, ORACLE_SID,PATH, LD_LIBRARY_PATH, NLS_LANG, etc.
The user profile file can be
.bash_profile - Bash Shell
.profile - Bourne Shell or Korn shell
.login ­- C Shell


Note: This user profile file will be under user’s home directory i.e. $HOME/.bash_profile
To check specific environment variable set:

$ echo $ORACLE_HOME
To check all the environment variables set:
$ env
On Windows Systems:
To check specific environment variable set:
C:\> set ORACLE_HOME
OR
C:\echo %ORACLE_HOME%
To check all the environment variables set:
C:\> set
Or
C:\> env

Other way, to check the ORACLE_HOME, is as follows.

Start -> Run -> Regedit (enter) -> HKEY_LOCAL_MACHINE -> SOFTWARE –> ORACLE
i.e. My Computer\HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE

How to check using sqlplus command:

To find the ORACLE_HOME path in Oracle Database
How to set the ORACLE_HOME environment variable?

On Unix/Linux Systems:

Define the ORACLE_HOME value in the user profile file i.e. .bash_profile or .profile
ORACLE_HOME=$ORACLE_BASE/product/10.2.0
export ORACLE_HOME

Source the user profile as follows:
Bash shell:
$ . ./.bash_profile
Bourne shell or Korn shell:
$ . ./.profile
C shell:
% source ./.login
If no profile file is set with environment variables, then physically also be set as follows:
Bourne, Bash, or Korn shell:
$ ORACLE_BASE=/oracle/app
$ export ORACLE_BASE
$ ORACLE_HOME=$ORACLE_BASE/product/10.2.0
$ export ORACLE_HOME

C Shell:
% setenv ORACLE_BASE /oracle/app
% setenv ORACLE_HOME /oracle/app/product/10.2.0


On Windows Systems:

My Computer -> Properties -> Advanced -> Environment Variables -> System Variables -> New/Edit/Delete (to set the variables)

After setting the environment variables as above, open a fresh CMD tool and check whether they set properly or not. Do not try on already opened CMD tool to make sure the variables set or not.
Another way to physically set the variables as follow at the DOS prompt:
C:\> set ORACLE_HOME=C:\oracle\app\product\10.2.0
C:\> echo %ORACLE_HOME%


Note: I would suggest to refer the Oracle Documentation on Installation where these environment variables are discussed and explained with the enough information.