Soban.dba

Enabling auditing in db sqlplus / as sysdba ALTER SYSTEM SET audit_trail=db,extended SCOPE=SPFILE; shutdown immediate startup table for audit trails DBA_AUDIT_TRAIL view the size of audit select round(bytes/1024/1024/1024) from dba_segments where segment_name ='AUD$'; see the current tablespace of audit table select tablespace_name from dba_tables where table_name='AUD$'; Audit notes Master Note For Oracle Database Auditing (Doc ID 1299033.1) To audit a specific schema AUDIT ALL STATEMENTS BY <username> ; Moving $AUD table from SYSTEM TS to a dedicated Table Space If your AUD$ table is in SYSTEM and SYSTEM tablespace, Then it is advised to move the AUD$to a dedicated tablespace. Use 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 ------- ------------- ------------ --------

On Running a huge database backup we often need to know the status of backup. 1) How much backup is taken. 2) How much is remain. 3) Estimate time to complete the backup. After lot of search i find out this amazing query below. col dbsize_mbytes for 99,999,990.00 justify right head "DBSIZE_MB" col input_mbytes for 99,999,990.00 justify right head "READ_MB" col output_mbytes for 99,999,990.00 justify right head "WRITTEN_MB" col output_device_type for a10 justify left head "DEVICE" col complete for 990.00 justify right head "COMPLETE %" col compression for 990.00 justify right head "COMPRESS|% ORIG" col est_complete for a20 head "ESTIMATED COMPLETION" col recid for 9999999 head "ID" select recid , output_device_type , dbsize_mbytes , input_bytes/1024/1024 input_mbytes , output_bytes/1024/1