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

Suppose you wish to take a expdp backup of a big table, but you don’t sufficient space in a single mount point to keep the dump. In this case we take expdp dump to multiple directory. DEMO: Create 2 directories: SQL> create directory DIR1 as ‘/home/oracle/DIR1’; Directory created. SQL> create directory DIR2 as ‘/home/oracle/DIR2’; Directory […]

A character set determines what languages can be represented in the database. Oracle recommends Unicode AL32UTF8 as the database character set. Unicode is the universal character set that supports most of the currently spoken languages of the world. select * from nls_database_parameters where parameter=’NLS_CHARACTERSET’; PARAMETER VALUE ——————— ———— NLS_CHARACTERSET AL32UTF8 SQL> ; 1* SELECT * […]

If your AUD$ table is in SYSTEM and SYTEM tablespace, Then it is advised to move the AUD$ to a dedicated tablespace. Use below steps to move AUD$. select owner,segment_name,segment_type,tablespace_name,bytes/1024/1024 from dba_segments where segment_name=’AUD$’ OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME BYTES/1024/1024 ——- ————- —————— —————————— ————— SYS AUD$ TABLE SYSTEM 16 Use the dbms_audit_mgmt to move the […]

From 11g onward you can make a table read only SQL> select owner,table_name,STATUS,READ_ONLY from dba_tables where table_name=’DBACLASS’; OWNER TABLE_NAME STATUS READ_ONLY —————————— —————————— ——– ——— TEST DBACLASS VALID NO SQL> alter table test.dbaclass read only; Table altered. SQL> SQL> select owner,table_name,STATUS,READ_ONLY from dba_tables where table_name=’DBACLASS’; OWNER TABLE_NAME STATUS READ_ONLY —————————— —————————— ——– ——– TEST DBACLASS […]

If you want to recreate your temp tablespace, then follow below steps. For changing the default tablespace also, below steps can be used. Find the existing temp tablespace details SQL> ; 1* select tablespace_name,file_name from dba_temp_files SQL> / TABLESPACE_NAME FILE_NAME —————————— ——————————————————– TEMP /home/oracle/app/oracle/oradata/cdb1/orcl/orcl_temp01201 4-07-30_04-39-23-PM.dbf Create another Temporary Tablespace TEMP1 CREATE TEMPORARY TABLESPACE TEMP1 TEMPFILE […]

Use below script to find the long running queries in your database. select sid,inst_id,opname,totalwork,sofar,start_time,time_remaining from gv$session_longops where totalwork<>sofar / SEE – COMPLETE COLLECTION OF DATABASE SCRIPTS

Use below query to get the sql text of a particular sid. When the below script asks for input, enter the SID. col sql_text form a80 set lines 120 select sql_text from gv$sqltext where hash_value= (select sql_hash_value from gv$session where sid=&1) order by piece / SQL> SQL> 2 3 4 Enter value for 1: 285 […]