Pubudu: Data and Decisions

SQL Navigator 3.1e5 version will not work with Oracle 10g and above windows client.  To make it work find a dll called ociw32.dll in the local machine where Oracle client is installed inside bin folder, take a copy and rename it to ora73.dll  and copy back to the same bin folder, and navigator will work.

Error : ORA-01017: invalid username/password; logon denied - Database Link SQL> select * from global_name@my_link; select * from global_name@my_link * ERROR at line 1: ORA-01017: invalid username/password; logon denied ORA-02063: preceding line from MY_LINK SQL> Above error may sometimes occur when accessing the target DB with newly created database link. If so,try connecting to the target database via SQLPlus using the tnsnames.ora entry: sqlplus pub@tnsname If the target db is 11g then password case sensitivity may be the issue.  Solution: Place the password in double quotes when you create the database link and retry.

You can use below statements to drop an existing user and recreate it with appropriate privileges.. DROP USER MY_USER CASCADE / CREATE USER MY_USER     IDENTIFIED BY MY_USER     DEFAULT TABLESPACE MYTBS     TEMPORARY TABLESPACE temp / GRANT DBA TO MY_USER / GRANT SELECT ANY TABLE TO MY_USER / GRANT INSERT ANY TABLE TO MY_USER / GRANT UPDATE ANY TABLE TO MY_USER / GRANT DELETE ANY TABLE TO MY_USER / GRANT ALTER ANY TABLE TO MY_USER / GRANT EXECUTE ANY PROCEDURE TO MY_USER / GRANT SELECT ANY SEQUENCE to MY_USER /

You may get below error in your Oracle Alert logs.. Errors in file /u01/app/oracle/admin/s123/udump/s123_ora_27563.trc: ORA-07445: exception encountered: core dump [qmxiManifestVArray()+4314] [SIGSEGV] [unknown code] [0x000000000] [] [] In general this kind of error is due to an OS or Hardware trouble or bug.  Usually ORA-7445 messages should be reported to Oracle. There is most likely a patchset or a work-around that adresses the problem. This error probably generated a trace file in your udump or bdump directory.  And also this might be related to a previous SHUTDOWN ABORT that caused block corruption.

Transaction Control  (TCL) statements are used to manage the changes made by DML statements(INSERT,UPDATE etc). TCL Statements available in Oracle are: COMMIT  Make changes done in  transaction permanent. ROLLBACK Rollbacks the state of database to the last commit point. SAVEPOINT   Use to specify a point in transaction to which later you can rollback. COMMIT To make the changes done in a transaction permanent issue the COMMIT statement. The syntax of COMMIT Statement is COMMIT  [WORK]  [COMMENT ‘your comment’]; WORK is optional. COMMENT is also optional, specify this if you want to identify this transaction in data dictionary DBA_2PC_PENDING. Example : insert into emp (empno,ename,sal) values (001,’Dayan’,25000); commit; ROLLBACK To rollback the changes done in a transaction give rollback statement. Rollback restore the state of the database to the last commit point. Example : delete from emp_details; rollback;        ...

Different versions of the import utility are upwards compatible. This means that one can take an export file created from an old export version, and import it using a later version of the import utility. Below grid is just a outline and there may be many considerations between YES and NO Exp done with version: 7 8i 9i 10g 11g Is compatible with Imp 7 YES NO NO NO NO 8i YES YES NO NO NO 9i YES YES YES NO NO 10g YES YES YES YES NO 11g YES YES YES YES YES

Permanent tablespace create tablespace ts_something logging datafile '/dbf1/ts_sth.dbf' size 32m autoextend on next 32m maxsize 2048m extent management local; create tablespace data datafile ' /u01/app/oracle/oradata/my/data.dbf' size   10M autoextend on maxsize      200M extent management local uniform size  64K; Temporary tablespace create temporary tablespace temp_mtr   tempfile '/dbf1/mtr_temp01.dbf'   size 32m   autoextend on   next 32m maxsize 2048m   extent management local; Undo tablespace create undo tablespace ts_undo datafile '/dbf/undo.dbf' size 100M; Also more than one  datafile  can be created with a single create tablespace command: create tablespace mytbs   datafile ' /u01/app/oracle/oradata/my/mytbs _01.dbf' size 4M autoextend off,               ' /u01/app/oracle/oradata/my/mytbs _02.dbf' size 4M autoextend off,...

http://www.linuxjo.com/oracle-10g-installation-on-centos-5/ http://www.oracle-base.com/articles/10g/OracleDB10gR2InstallationOnRHEL5.php

Find out available Timezones in Oracle SQL>select distinct tzname from v$timezone_names Check current DB Timezone SQL>select dbtimezone from dual Check current session Timezone SQL>select sessiontimezone from dual Change DB Timezone Log in as SYS.. SQL>alter database set time_zone = 'Asia/Culcutta'

Below is the Oracle functionality relating to the identification of host for an Oracle Instance. V$INSTANCE The   HOST_NAME   column of the   V$INSTANCE   view contains the host name of the server running the instance. SQL> SELECT host_name FROM v$instance; HOST_NAME ------------------------------------------------ mydb123 1 row selected. SQL>

01. Shutdown database normal/immediate but not abort. >shutdown immediate; 02. Copy the online redo log files to the new location. Linux/Unix use mv command Windows move command 03. Startup MOUNT database logging in as sysdba (do not open the database) >startup mount pfile=<initialization parameter file with path> 04. Issue the following statement Ex You are changing the file from c:\oracle\oradata\redologs to c:\oracle\oradata\whs\redologs and like wise on d:\ drive. ALTER DATABASE RENAME FILE 'c:\oracle\oradata\redologs\redo_01_01.log', 'd:\oracle\oradata\redologs\redo_01_02.log' TO 'c:\oracle\oradata\whs\redologs\redo_01_01.log', 'd:\oracle\oradata\whs\redologs\redo_01_02.log' / 05. Open the database >alter database open;

emctl start agent                     Starts the Management Agent emctl stop agent                    Stops the Management Agent   emctl status agent                                                                                                If the Management Agent is running, this command displays status information about the Management Agent, including the Agent Home, the process ID, and the time and date of the last successful upload to the Management Repository ().

In oracle 11g, alert file is saved in 2 location, one is in alert directory ( in XML format) and other in old style alert file in trace directory . Both contains the same information. These logs are used when you are using the ADRCI . ( Automatic Diagnostic Repository Command Interface ) Beginning with Release 11g, the alert log file is written as XML formatted and as a text file (like in previous releases). The default location of both these files is the new ADR home (Automatic Diagnostic Respository, yet another new dump dest in 11g). The ADR is set by using the DIAGNOSTIC_DEST initialization parameter. If this parameter is omitted, then, the default location of ADR is, 'u01/oracle/product/ora11g/log' (depends on ORACLE_HOME settings). The location of an ADR home is given by the following path, which starts at the ADR base directory: ADR_BASE/diag/product_type/product_id/instance_id If environment variable ORACLE_BASE is not set, DIAGNOSTIC_DEST is set to ORACLE_HO...

Objects need to recompile are: VIEW, SYNONYM, PUBLIC SYNONYM, PROCEDURE, FUNCTION, PACKAGE, PACKAGE BODY, TRIGGER, UNDEFINED (MATERIALIZED VIEW), JAVA CLASS, TYPE, TYPE BODY There are five ways to recompile invalid objects in schemas. 1. DBMS_DDL 2. DBMS_UTILITY 3. UTL_RECOMP 4. UTLRP.SQL 5. Manually Recompile 1. DBMS_DDL This procedure is equivalent to the following SQL statement: ALTER PROCEDUREFUNCTIONPACKAGE [ .]  COMPILE [BODY] Exec dbms_ddl.alter_compile ( type , schema, name); SQL> exec dbms_ddl.alter_compile ('PROCEDURE','SCOTT','TEST'); 2. DBMS_UTILITY This procedure compiles all procedures, functions, packages, and triggers in the specified schema. Exec dbms_utility.compile_schema ( schema,compile all) SQL> exec dbms_utility.compile_schema('SCOTT'); 3. UTL_RECOMP This script is particularly useful after a major-version upgrade that typically invalidates all PL/SQL and Java objects. Exec UTL_RECOMP....

Error :  ORA-04021 timeout occurred while waiting to lock object Solutions: 1. Look in V$SESSION_EVENT and V$SESSION_WAIT to see what the session is waiting on. 2. Use the V$LOCKED_OBJECT view to find out if anyone else is locking the object. 3. Check  DB links which aren't working correctly. Kill the relevant sessions if needed.

SQL> ALTER DATABASE open * ERROR at line 1: ORA-16014: log 1 sequence# 10484 not archived, no available destinations ORA-00312: online log 1 thread 1: 'C:\ORACLE\PRODUCT\10.2.0\STAR\REDO01.LOG' You are getting this message because Oracle can not find enough space on your archive destination to write the archive.Create some space in your archive destination. Also check if the destination you specified EXISTS.

Memory Notification: Library Cache Object loaded into SGA Heap size 2183K exceeds notification threshold (2048K) These are warning messages only which should not cause any problems.  These warning messages appear due to new event messaging mechanism and memory manager in 10g Release 2. In 10g R2 we have a new undocumented parameter that sets the KGL heap size warning threshold. Warnings are written if heap size exceeds this threshold.   Default Threshold in 10.2.0.1 is 2 MB only. But Default Threshold value in 10.2.0.2 is 50 MB which is reasonable and recommended value.  Solution:   Set _kgl_large_heap_warning_threshold to a reasonable high value to prevent these warning messages. SQL> alter system set "_kgl_large_heap_warning_threshold"= 52428800 scope=spfile; SQL> shutdown immediate;   SQL> startup;

Following error may thrown during recovery process if incarnation of live and backup databases are different.. An incarnation is a seperate ‘life path’ of an Oracle Database, which is created at the time when a RESETLOGS is issued. ORA-00283: recovery session canceled due to errors ORA-19909: datafile 1 belongs to an orphan incarnation ORA-01110: data file 1: First check the incarnation of both the databases using rman. RMAN> list incarnation Check the current incarnation of the LIVE database and apply the same number to backup database. RMAN> RESET DATABASE TO INCARNATION 4 ; Then try to recover again..

When your import file path was wrong you may get a prompt like below and you may wonder how to exit from it.. IMP-00002: failed to open /quit.dmp for read Import file: expdat.dmp > Use " Ctrl D " to exit from the prompt  or you may also kill the session from separate OS window/terminal.