How to generate AWR report in oracle

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 […]

How to flush a sql statement from shared pool

                                  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. […]

How to get the bind values of a sql query

                   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, 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 […]

How to move lob segment to another tablespace

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 […]

ORA-01940: cannot drop a user that is currently connected

 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 […]

How to change the oracle database name using nid utility

  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 […]

How to find when a table was last modified in oracle

    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 […]

How to Enable Trace for a listener

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 – Production Start Date 14-DEC-2015 16:28:54 Uptime 0 days 0 […]

How to deinstall oracle client

              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 -rw-r–r–. 1 oracle oinstall 409 Aug 18 2010 readme.txt -rw-r–r–. 1 oracle oinstall 3466 […]

How to enable block change tracking in oracle

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: […]