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

Use below script to find active sessions in oracle database. set echo off set linesize 95 set head on set feedback on col sid head “Sid” form 9999 trunc col serial# form 99999 trunc head “Ser#” col username form a8 trunc col osuser form a7 trunc col machine form a20 trunc head “Client|Machine” col program […]

You can rename a tablespace with alter tablespace  command. See the below example.     set pagesize 200 set lines 200 col file_name for a57 SQL> select file_id,file_name,tablespace_name from dba_data_files where file_id=37; FILE_ID FILE_NAME TABLESPACE_NAME ———- ——————————————————– —————————— 37 /home/oracle/app/oracle/oradata/cdb1/testin1.dbf TESTING — Rename the tablespace_name from TESTING to PRODUCING; SQL> alter tablespace TESTING rename to […]

Below is the commands to drop a tablespace. 1. Drop a tablespace without removing the physical database files.   SQL> select file_name from dba_data_files where tablespace_name=’TESTING’; FILE_NAME ——————————————————————————– /home/oracle/app/oracle/oradata/cdb1/testin1.dbf SQL> drop tablespace TESTING; Tablespace dropped. SQL> select file_name from dba_data_files where tablespace_name=’TESTING’; no rows selected SQL> SQL> !ls -ltr /home/oracle/app/oracle/oradata/cdb1/testin1.dbf -rw-rw—-. 1 oracle oracle 104865792 […]