Soban.dba

1) Create a Directory in database mkdir directory 2) Give permission to Directory chmod 775 directory/ Sqlplus / as sysdba create or replace directory exec_dir as '/home/oracle/checks/directory'; Create a Table in Database: CREATE TABLE df  ( "FILESYSTEM" VARCHAR2(100),    "BLOCKS" NUMBER, "USED" NUMBER,  "AVAILABLE" NUMBER, "CAPACITY" VARCHAR2(10),    "MOUNT" VARCHAR2(100) ) ORGANIZATION external (   TYPE oracle_loader DEFAULT DIRECTORY exec_dir ACCESS PARAMETERS    ( RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII preprocessor  exec_dir:'run_df.sh' READSIZE 1048576 SKIP 1 FIELDS TERMINATED BY WHITESPACE LDRTRIM      REJECT ROWS WITH ALL NULL FIELDS ( "FILESYSTEM" CHAR(255) TERMINATED BY WHITESPACE,       "BLOCKS" CHAR(255) TERMINATED BY WHITESPACE, "...

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...

RMAN> report schema; RMAN> catalog start with 'target datafiles path'; RMAN> list copy of database; RMAN> switch database to copy; RMAN> report schema; reference: RMAN-06094 or RMAN-06571 During Recovery or Switch to Copy at Standby Site (Doc ID 1339439.1)                            

What is the SQL Tuning Health-Check Script (SQLHC)? The SQL Tuning Health-Check Script is a tool developed by the Oracle Server Technologies Center of Expertise. The tool, also known as SQLHC, is used to check the environment in which a single SQL Statement runs, checking Cost-based Optimizer (CBO) statistics, schema object metadata, configuration parameters and other elements that may influence the performance of the one SQL being analyzed. INSTRUCTIONS Login to the database server and set the environment used by the Database Instance Download the "sqlhc.zip" archive file  and extract the contents to a suitable directory/folder Connect into SQL*Plus as SYS, a DBA account, or a user with access to Data Dictionary views and simply execute the "sqlhc.sql" script. It will request to enter two parameters: Oracle Pack License ( T uning,  D iagnostics or  N one) [T|D|N] (required) If site has both Tuning and Diagnostics licenses then specify  T  (Or...

Recently we ran into an error Oracle error which says Caused By: Error executing SQL ALTER TABLE ***_ALLOCATION ADD REGION_ID NUMBER(38):          Caused By: ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired The most common reason for this are either ' SELECT FOR UPDATE ' or some uncommitted INSERT statements. Combining the information I got from several Google searches, I ended up with this sql SELECT O.OBJECT_NAME, S.SID, S.SERIAL#, P.SPID, S.PROGRAM,SQ.SQL_FULLTEXT, S.LOGON_TIME FROM V$LOCKED_OBJECT L, DBA_OBJECTS O, V$SESSION S, V$PROCESS P, V$SQL SQ WHERE L.OBJECT_ID = O.OBJECT_ID AND L.SESSION_ID = S.SID AND S.PADDR = P.ADDR AND S.SQL_ADDRESS = SQ.ADDRESS; This gave me info about the sql and the table which it has locked and the logon time. OBJECT_NAME SID SERIAL# SPID PROGRAM LOGON_TIME SQL_FULLTEXT TABLE_USER 953 40807 9179 JDBC Thin Client 26-Jul-12 INSERT INTO T...

COMMAND alter tablespace <tablespace_name> add datafile ' <disk group name> ' size < size of a file > autoextend on next 50M maxsize unlimited; Example alter tablespace APPS_TS_TX_DATA add datafile '+DATAC1' size 2G AUTOEXTEND ON NEXT 50M MAXSIZE UNLIMITED;

col "Tablespace"       for a22 col "Used MB"          for 9,999,999.99 col "Free MB"          for 9,999,999.99 col "Total MB"         for 9,999,999.99 col extendable_free_space for 9,999,999.99 col "Pct. Free"        for 999,999.99 col " maxspace "         for 999,999.99 set linesize 112 select df . tablespace_name "Tablespace", totalusedspace "Used MB", ( df . totalspace - tu . totalusedspace ) "Free MB", df . totalspace "Total MB", round ( 100 * ( ( df . totalspace - tu . totalusedspace ) / df . totalspace ) , 2) " Pct . Free" from ( select tablespace_name, round ( sum ( bytes) / 1048576) TotalSpace from dba_data_files group by tablespace_name) df , ( select round ( sum ( bytes ) /(1024*1024)) totalusedspace , tablespac...