Oracle DBA Support

CHECK PGA INFORMATION AND CONSUMPTION OF MEMORY IN ORACLE SQL QUERY: >>>  SET LINESIZE 145  SET PAGESIZE 9999  COLUMN sid FORM...

CHECK WAIT CLASS IN ORACLE DATABASE : >> select event,p1,p2,wait_class from v$session_wait where sid=29; EXAMPLE : SQL> sele...

INFORMATION ABOUT SGA : >> select * from v$sgainfo;

CHECK IMPORTANT PARAMETERS IN A DATABASE: >> show parameter db >> show parameter _db >> show parameter database

HOW TO SELECT ALL COLUMNS IN AN ORACLE DATABASE : select OWNER, TABLE_NAME, COLUMN_NAME from dba_tab_columns;

SGA_MAX_SIZE, SGA_TARGET, MEMORY_TARGET AND MEMORY_MAX_TARGET PLAYS A MAJOR ROLE IN ENHNACING THE PEFORMANCE OF THE DATABASE : >> I...

HOW TO ENABLE AUTOMATIC MEMORY MANAGEMENT IN ORACLE - PARAMETERS AND CONDITIONS TO FULFILL FOR AMM AND ASMM : >> SGA_TARGET SHOULD ...

SETTING VALUES FOR PARAMETERS SGA_MAX_SIZE, MEMORY_TARGET AND MEMORY_MAX_TARGET : SGA_MAX_SIZE is dependent on MEMORY_MAX_TARGET and cann...

CHECK TABLESPACE SIZE,PATH AND TABLESPACE_NAME IN ORACLE: >> select file_name,status,tablespace_name,bytes/1024/1024 from dba_data...

HOW TO CHECK SGA_MAX_SIZE , MEMORY_TARGET, MEMORY_MAX_TARGET IN ORACLE DATABASE : >> show parameter sga_max_size >> show pa...

HOW TO CHECK FULL SIZE OF AN ORACLE DATABASE (APPROXIMATE) : select (select sum(bytes)/1024/1024 from dba_data_files)+ (select sum(bytes)...

STEP BY STEP PROCESS TO DUPLICATE AN ORACLE DATABASE & SIMPLE STEPS, EASY TO UNDERSTAND WITH SQL QUERIES : 1. connect to TEST database ...

HOW TO CHECK ALL TABLES FOR A PARTICULAR SCHEMA : SELECT DISTINCT OWNER, OBJECT_NAME   FROM DBA_OBJECTS  WHERE OBJECT_TYPE = 'TABLE...

FIND ALL INDEXES ON A SPECIFIC TABLE : select table_name,index_name from dba_indexes where table_name = 'PROJECT_JOB';

CREATE TEST TABLE OF 100000 RECORDS : >> CREATE TABLE test_table AS SELECT LEVEL id, SYSDATE+DBMS_RANDOM.VALUE(-1000, 1000) date_v...

RENAMING AN ORACLE DATABASE STEP BY STEP PROCEDURE : If the database instance runs on same node when nid.exe runs you don't need to u...

CHECK DATABASE CHARACTER SETS : SQL> SHOW PARAMETER NLS_LANGUAGE; SQL> SELECT name, value$ from SYS.PROPS$ WHERE name='NLS_CHAR...

ALTERNATING ANALYZE QUERIES FOR PERFORMANCE ENHANCEMENT : >> EXEC DBMS_UTILITY.analyze_schema('SCHEMA_NAME,'COMPUTE')...

GATHER STATISTICS FOR A SPECIFIC TABLE  AND SPECIFIC USER TO ENHANCE PERFORMANCE OF RUNNING SQL QUERIES : >>  EXECUTE DBMS_STATS.G...

BEST QUERY TO CHECK TABLESPACE SIZE IN ORACLE AND % FREE : select df.tablespace_name "Tablespace", totalusedspace "Used MB...

CHECK ALL INVALID OBJECTS IN A SCHEMA : COLUMN object_name FORMAT A30 SELECT owner,        object_type,        object_name,        status...

BEST COMMANDS FOR GATHER STATISTICS IN ORACLE AND PERFORMANCE TUNING OF THE DATABASE STEP BY STEP PROCEDURE: - To enhance the performance...

RESTORE A PARTICULAR TABLESPACE USING RMAN : C:\Documents and Settings\USERPRO>set oracle_sid=orcl C:\Documents and Settings\USERPRO>...

RMAN STEP BY STEP RESTORE AND RECOVER DATABASE IN ORACLE : --Take a backup of database from RMAN before proceeding : ===================...

FIND DATUM,  PROJECTION  IN ORACLE : select DATUM_NAME, INFORMATION_SOURCE from sdo_datums; select DATUM_NAME, INFORMATION_SOURCE fro...