The Automatic Workload Repository (AWR) collects and maintains statistics of the database. We can generate awr report for a particular time frame in the past using the script awrrpt.sql ( located under $ORACLE_HOME/rdbms/admin) script – @$ORACLE_HOME/rdbms/admin/awrrpt.sql conn / as sysdba SQL> @$ORACLE_HOME/rdbms/admin/awrrpt.sql Specify the Report Type ~~~~~~~~~~~~~~~~~~~~~~~ AWR reports can be generated in the following […]

                                  If you flush the shared pool, all the statements in cursor will be flushed. So if you want a sql query to do hard parsing, then you can flush the particular sql statement from shared pool. STEPS: 1. […]

                   If you have the sql_id of the sql query , then you can get the bind values of the bind variables, from v$sql_bind_capture. Script: SELECT sql_id, b. LAST_CAPTURED, t.sql_text sql_text, b.HASH_VALUE, b.name bind_name, b.value_string bind_value FROM gv$sql t JOIN gv$sql_bind_capture b using (sql_id) WHERE b.value_string is […]

Follow below steps from moving lob segment from one tablespace to another. SQL> ;   1*  select table_name,COLUMN_NAME,SEGMENT_NAME,TABLESPACE_NAME from dba_lobs where OWNER=’DBACLASS’ SQL> / TABLE_NAME     COLUMN_NAME  SEGMENT_NAME                       TABLESPACE_NAME ————– ———— ———————————- —————————— FD_BREL_AUDIT PAYLOAD      SYS_LOB0000100201C00011$$          WEBMDATA   SQL> alter table DBACLASS.FD_BREL_AUDIT move lob (PAYLOAD) store as SYS_LOB0000100201C00011$$ ( tablespace USERS); Table altered.   SQL>  select […]

 While dropping a user in oracle database , you may face below error. ORA-01940 Problem: SQL> drop user SCOTT cascade 2 / drop user SCOTT cascade * ERROR at line 1: ORA-01940: cannot drop a user that is currently connected Solution: 1. Find the sessions running from this userid: SQL> SELECT SID,SERIAL#,STATUS from v$session where […]

  Follow below steps for changing the oracle database name using nid utility. We will change the database name from P2PRMD2 to P1D2ST. 1. Mount the database SQL> STARTUP MOUNT ORACLE instance started. Total System Global Area 8754618368 bytes Fixed Size 4646288 bytes Variable Size 3556776560 bytes Database Buffers 5033164800 bytes Redo Buffers 160030720 bytes […]

    If you want to find, when a table was last modified like insert,update ,delete, then use the dictionary table dba_tab_modifications. SCENARIO: 1. Insert into test data: SQL[SCOTT@TDB01]SQL>>]insert into TEST values (10); 1 row created. SQL[SCOTT@TDB01]SQL>>]commit; Commit complete. 2. Check dba_tab_modification: SQL[SYS@TCRMDB01]SQL>>]select INSERTS,UPDATES,DELETES,TRUNCATED,TIMESTAMP from dba_tab_modifications where TABLE_NAME=’TEST’ and TABLE_OWNER=’SCOTT’; no rows selected   As […]

If you want to enable trace for a listener, Then follow below steps. Set current listener:   LSNRCTL> set cur LISTENER_TEST Current Listener is LISTENER_TEST Check status LSNRCTL> status Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_TEST))) STATUS of the LISTENER ———————— Alias LISTENER_TEST Version TNSLSNR for HPUX: Version 12.1.0.2.0 – Production Start Date 14-DEC-2015 16:28:54 Uptime 0 days 0 […]

              We can deinstall oracle client by runnning the deinstall tool provided by oracle client software. [oracle@bt-Prov-devst1 client_1]$ cd deinstall/ [oracle@bt-Prov-devst1 deinstall]$ ls -ltr total 92 -rwxr-xr-x. 1 oracle oinstall 32343 Dec 16 2009 sshUserSetup.sh -rw-r–r–. 1 oracle oinstall 409 Aug 18 2010 readme.txt -rw-r–r–. 1 oracle oinstall 3466 […]

Block change tracking file keeps track of all changes to blocks of datafile since the last incremental backup. So while taking incremental backup, RMAN will read this tracking file, instead of going through all the blocks to find the changed blocks. It hugely reduced the incremental backup time.   How to enable block change tracking: […]