Way to success...

--"Running Away From Any PROBLEM Only Increases The DISTANCE From The SOLUTION"--.....--"Your Thoughts Create Your FUTURE"--.....--"EXCELLENCE is not ACT but a HABIT"--.....--"EXPECT nothing and APPRECIATE everything"--.....

Thursday, July 28, 2016

Query To Check Components Status Registered in an Oracle Database


Download(dbregistry.sql)

REM +======================================================================+
REM                    
REM File Name: dbregistry.sql
REM 
REM Description:
REM   Query To Check Components Status Registered in an Oracle Database
REM   
REM Notes:
REM   Usage: sqlplus "/ as sysdba" @dbregistry.sql 
REM   
REM +======================================================================+

clear columns
set lines 180
set pages 100
col COMP_ID for a10
col COMP_NAME for a50 WORD_WRAPPED
col VERSION for a15
col STATUS for a15
select COMP_ID,COMP_NAME,VERSION,STATUS from dba_registry;

Query To Get List of Database Directories


Download(dbdir.sql)

REM +======================================================================+
REM                    
REM File Name: dbdir.sql
REM 
REM Description:
REM   Query To Get List of Database Directories 
REM   
REM Notes:
REM   Usage: sqlplus "/ as sysdba" @dbdir.sql
REM   
REM +======================================================================+

clear columns
set lines 180
set pages 100
col OWNER for a12
col DIRECTORY_NAME for a40
col DIRECTORY_PATH for a80 WORD_WRAPPED

select * from dba_directories;

Query To Get List of Database Links


Download(dblinks.sql)

REM +======================================================================+
REM                    
REM File Name: dblinks.sql
REM 
REM Description:
REM   Query To Get List of Database Links 
REM   
REM Notes:
REM   Usage: sqlplus "/ as sysdba" @dblinks.sql
REM   
REM +======================================================================+

clear columns
set lines 180
col OWNER for a20
col DB_LINK for a50 WORD_WRAPPED
col USERNAME for a20
col HOST for a30 WORD_WRAPPED

select * from dba_db_links;

Query To Check Database Status and Properties

Below query will give the Oracle Database Version, startup time, whether database is RAC enabled, archivelog status, default temporary and permanent tablespace of an database etc.


Download(dbstatus.sql)

REM +======================================================================+
REM                    
REM File Name: dbstatus.sql
REM 
REM Description:
REM   Query To Check Database Status and Properties
REM   
REM Notes:
REM   Usage: sqlplus "/ as sysdba" @dbstatus.sql 
REM   
REM +======================================================================+

clear columns

PROMPT
PROMPT ...Database Status...
PROMPT

set echo off heading on underline on;
set lines 180
column inst_num  format 99999;
column inst_name format a12;
column db_name   format a12;
column dbid      format 9999999999 just c;
column OPEN_MODE format a12 WORD_WRAPPED
column VERSION format a12 WORD_WRAPPED

select i.instance_number inst_num
  , d.dbid            dbid
  , i.instance_name   inst_name
  , d.name            db_name
  , i.VERSION
  , d.OPEN_MODE       
  , TO_CHAR(d.CREATED, 'DD-MON-YYYY HH24:MI:SS') "CREATED"
  , TO_CHAR(i.STARTUP_TIME, 'DD-MON-YYYY HH24:MI:SS') "STARTUP_TIME"
  , d.LOG_MODE
  from gv$database d,
    gv$instance i;

PROMPT
PROMPT ...Database Properties...
PROMPT

col PROPERTY_VALUE for a50
SELECT property_name, property_value
  FROM database_properties
 WHERE property_name IN ('DEFAULT_TBS_TYPE', 'DEFAULT_PERMANENT_TABLESPACE', 'DEFAULT_TEMP_TABLESPACE', 'NLS_LANGUAGE', 'NLS_CHARACTERSET');
      


Query To Get List Of Tablespaces Exist in an Oracle Database


Download(tslist.sql)

REM +======================================================================+
REM                    
REM File Name: tslist.sql
REM 
REM Description:
REM   Query To Get List Of Tablespaces Exist in an Oracle Database
REM   
REM Notes:
REM   Usage: sqlplus "/ as sysdba" @tslist.sql
REM   
REM +======================================================================+

clear columns
set lines 180
 set pages 100
 col BIGFILE for a10
 select TABLESPACE_NAME,STATUS,CONTENTS,BIGFILE from dba_tablespaces
 order by CONTENTS desc;

Query To Check Oracle Database Size


Download(dbsize.sql)

REM +======================================================================+
REM                    
REM File Name: dbsize.sql
REM 
REM Description:
REM   Query To Check Oracle Database Size
REM   
REM Notes:
REM   Usage: sqlplus "/ as sysdba" @dbsize.sql
REM   
REM +======================================================================+

clear columns
set lines 180
SELECT "DATABASE_SIZE(GB)", "DATABASE_SIZE(GB)" - "FREE_SPACE(GB)" "USED_SPACE(GB)", "FREE_SPACE(GB)"
  FROM (SELECT (SELECT SUM (BYTES / (1014 * 1024 * 1024))
                  FROM dba_data_files) "DATABASE_SIZE(GB)", (SELECT SUM (BYTES / (1024 * 1024 * 1024))
                                                               FROM dba_free_space) "FREE_SPACE(GB)"
          FROM DUAL);

Query To Monitor Longops Sessions


Download(longops.sql)

REM +======================================================================+
REM                    
REM File Name: longops.sql
REM 
REM Description:
REM   Query To Monitor Longops Sessions
REM   
REM Notes:
REM   Usage: sqlplus "/ as sysdba" @longops.sql 
REM   
REM +======================================================================+

Clear columns
SET pages 100
 SET Lines 280
 COL MESSAGE for a40 WORD_WRAPPED
 COL START_TIME for a22
 COL INST_ID for 99999
 SELECT inst_id,SID, serial#, TO_CHAR (start_time, 'dd-MON-yyyy hh24:mi:ss') "START_TIME", ROUND (time_remaining / 60, 2) "TimeRemaining(M)", ROUND (elapsed_seconds / 60, 2) "ElapsedTime(M)"
    ,ROUND ((time_remaining + elapsed_seconds) / 60, 2) "TotalTime(M)", ROUND (sofar / totalwork * 100, 2) "%Complete", MESSAGE
   FROM gv$session_longops
  WHERE time_remaining > 0;