Skip to main content

General Database Queries



How to find Session details for RMAN backups:

select b.sid, b.serial#, a.spid, b.client_info
from
 gv$process a, gv$session b
where
 a.addr=b.paddr and client_info
like
 'rman%';



How to find REDO log generation hour by hour in a database :

set lines 120;
set pages 999;
SELECT
to_char(first_time,'YYYY-MON-DD') day,
to_char(sum(decode(to_char(first_time,'HH24'),'00',1,0)),'99') "00",
to_char(sum(decode(to_char(first_time,'HH24'),'01',1,0)),'99') "01",
to_char(sum(decode(to_char(first_time,'HH24'),'02',1,0)),'99') "02",
to_char(sum(decode(to_char(first_time,'HH24'),'03',1,0)),'99') "03",
to_char(sum(decode(to_char(first_time,'HH24'),'04',1,0)),'99') "04",
to_char(sum(decode(to_char(first_time,'HH24'),'05',1,0)),'99') "05",
to_char(sum(decode(to_char(first_time,'HH24'),'06',1,0)),'99') "06",
to_char(sum(decode(to_char(first_time,'HH24'),'07',1,0)),'99') "07",
to_char(sum(decode(to_char(first_time,'HH24'),'08',1,0)),'99') "0",
to_char(sum(decode(to_char(first_time,'HH24'),'09',1,0)),'99') "09",
to_char(sum(decode(to_char(first_time,'HH24'),'10',1,0)),'99') "10",
to_char(sum(decode(to_char(first_time,'HH24'),'11',1,0)),'99') "11",
to_char(sum(decode(to_char(first_time,'HH24'),'12',1,0)),'99') "12",
to_char(sum(decode(to_char(first_time,'HH24'),'13',1,0)),'99') "13",
to_char(sum(decode(to_char(first_time,'HH24'),'14',1,0)),'99') "14",
to_char(sum(decode(to_char(first_time,'HH24'),'15',1,0)),'99') "15",
to_char(sum(decode(to_char(first_time,'HH24'),'16',1,0)),'99') "16",
to_char(sum(decode(to_char(first_time,'HH24'),'17',1,0)),'99') "17",
to_char(sum(decode(to_char(first_time,'HH24'),'18',1,0)),'99') "18",
to_char(sum(decode(to_char(first_time,'HH24'),'19',1,0)),'99') "19",
to_char(sum(decode(to_char(first_time,'HH24'),'20',1,0)),'99') "20",
to_char(sum(decode(to_char(first_time,'HH24'),'21',1,0)),'99') "21",
to_char(sum(decode(to_char(first_time,'HH24'),'22',1,0)),'99') "22",
to_char(sum(decode(to_char(first_time,'HH24'),'23',1,0)),'99') "23"
from
v$log_history
GROUP by
to_char(first_time,'YYYY-MON-DD') order by 1;


How to build Java VM in a databasae:

SQL> @?/javavm/install/update_javavm_db.sql

Reference Doc ID 2189241.1

How to find User lock information by using Audit session: If user is locked due to invalid password attempts then use below query to find the session details like who accessed the user with wrong passwords.


select os_username,username,userhost,terminal,to_char(TIMESTAMP,'DD-MON-YYYY,HH24:MI:SS'),action_name,returncode,to_char(EXTENDED_TIMESTAMP,'DD-MON-YYYY,HH24:MI:SS') from dba_audit_session where returncode not like 0  and username='<user_name>' order by 5


How to find RMAN restore progress (Standby/RMAN Cloning):


TTITLE LEFT '% Completed. Aggregate is the overall progress:'
SET LINE 132
SELECT opname, round(sofar/totalwork*100) "% Complete"
  FROM gv$session_longops
 WHERE opname LIKE 'RMAN%'
   AND totalwork != 0
   AND sofar <> totalwork
 ORDER BY 1;


Tablespace current space status:

SELECT /* + RULE */  df.tablespace_name "Tablespace",
    df.bytes / (1024 * 1024 * 1024) "Size (Gb)",
         SUM(fs.bytes) / (1024 * 1024* 1024) "Free (Gb)",
         Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free",
         Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used"
    FROM dba_free_space fs,
         (SELECT tablespace_name,SUM(bytes) bytes
            FROM dba_data_files
           GROUP BY tablespace_name) df
  WHERE fs.tablespace_name (+)  = df.tablespace_name
  GROUP BY df.tablespace_name,df.bytes;


Along with Temp Tablespaces:

SELECT /* + RULE */  df.tablespace_name "Tablespace",
    df.bytes / (1024 * 1024 * 1024) "Size (Gb)",
         SUM(fs.bytes) / (1024 * 1024* 1024) "Free (Gb)",
         Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free",
         Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used"
    FROM dba_free_space fs,
         (SELECT tablespace_name,SUM(bytes) bytes
            FROM dba_data_files
           GROUP BY tablespace_name) df
  WHERE fs.tablespace_name (+)  = df.tablespace_name
  GROUP BY df.tablespace_name,df.bytes
   UNION ALL
 SELECT /* + RULE */ df.tablespace_name tspace,
        fs.bytes / (1024 * 1024),
        SUM(df.bytes_free) / (1024 * 1024),
        Nvl(Round((SUM(fs.bytes) - df.bytes_used) * 100 / fs.bytes), 1),
        Round((SUM(fs.bytes) - df.bytes_free) * 100 / fs.bytes)
   FROM dba_temp_files fs,
        (SELECT tablespace_name,bytes_free,bytes_used
           FROM v$temp_space_header
          GROUP BY tablespace_name,bytes_free,bytes_used) df
  WHERE fs.tablespace_name (+)  = df.tablespace_name
  GROUP BY df.tablespace_name,fs.bytes,df.bytes_free,df.bytes_used
  ORDER BY 4 DESC;

Top 20 Objects from Database:

with segment_rollup as (
  select owner, table_name, owner segment_owner, table_name segment_name from dba_tables
 union all
 select table_owner, table_name, owner segment_owner, index_name segment_name from dba_indexes
    union all
  select owner, table_name, owner segment_owner, segment_name from dba_lobs
    union all
  select owner, table_name, owner segment_owner, index_name segment_name from dba_lobs
), ranked_tables as (
  select rank() over (order by sum(blocks) desc) rank, sum(blocks) blocks, r.owner, r.table_name
  from segment_rollup r, dba_segments s
  where s.owner=r.segment_owner and s.segment_name=r.segment_name
  group by r.owner, r.table_name
)
select rank, round(blocks*8/1024/1024) GB, table_name, owner
from ranked_tables
where rank<=20

/

With Specific Schema:

with segment_rollup as (
  select owner, table_name, owner segment_owner, table_name segment_name from dba_tables
 union all
 select table_owner, table_name, owner segment_owner, index_name segment_name from dba_indexes
    union all
  select owner, table_name, owner segment_owner, segment_name from dba_lobs
    union all
  select owner, table_name, owner segment_owner, index_name segment_name from dba_lobs
), ranked_tables as (
  select rank() over (order by sum(blocks) desc) rank, sum(blocks) blocks, r.owner, r.table_name
  from segment_rollup r, dba_segments s
  where s.owner=r.segment_owner and s.segment_name=r.segment_name
 and r.owner=upper('&schema_name')
  group by r.owner, r.table_name
)
select rank, round(blocks*8/1024/1024) GB, table_name, owner
from ranked_tables
where rank<=20
/


How to get Table Growth based on interval 

column owner format a16
column object_name format a36
column start_day format a11
column block_increase format 9999999999

select   obj.owner, obj.object_name,
         to_char(sn.BEGIN_INTERVAL_TIME,'RRRR-MON-DD') start_day,
         sum(a.SPACE_USED_DELTA) block_increase_bytes
from     dba_hist_seg_stat a,
         dba_hist_snapshot sn,
         dba_objects obj
where    sn.snap_id = a.snap_id
and      obj.object_id = a.obj#
and      obj.owner not in ('SYS','SYSTEM')
and      end_interval_time between to_timestamp('01-JAN-2012','DD-MON-RRRR')
         and to_timestamp('02-FEB-2012','DD-MON-RRRR')
group by obj.owner, obj.object_name,
         to_char(sn.BEGIN_INTERVAL_TIME,'RRRR-MON-DD')
order by obj.owner, obj.object_name
/


How to find and disable/enable DBMS scheduler jobs:

SELECT owner, job_name, comments FROM dba_scheduler_jobs;

select job_name, enabled from DBA_SCHEDULER_JOBS WHERE job_name = '<Job_name>';


execute dbms_scheduler.disable('<owner>.<Job_name>');

How to find and generate disable/enable Referential constraints:

select 'alter table '||a.owner||'.'||a.table_name||' enable/disable constraint '||a.constraint_name||';'
    from dba_constraints a, all_constraints b
    where a.constraint_type = 'R'
    and a.r_constraint_name = b.constraint_name
    and a.r_owner  = '<schema_name>'

    and b.table_name = '<table_name>';

How to unregister database from catalog after dropping database:

Connect to rman catalog via RMAN

rman catalog <catalog_user/password>@<catalogtns>;

rman> list incaranation;

cross verify the dbid from present database (select dbid from v$database;)

rman> run { 
set dbid <olddbid>;
unregister database <dbname> noprompt;
}

If above method doesn't work then unregister using sqlplus

sqlplus <catalog_user/password>@<catalogtns>

select db_key, dbid, name FROM rc_database WHERE name = '<dbname>'; 

EXECUTE dbms_rcvcat.unregisterdatabase(<old dbkey>,<old_dbid>);

cross verfiy from rman prompt

rman > list incarnation;



Comments

Popular posts from this blog

How to unmount and mount ACFS FILESYSTEM

How to unmount and mount ACFS FileSystem in Linux Unmounting ACFS File Systems ACFS file systems can be used by Oracle RAC for hosting software files for the database. It can also be used as a general purpose file system for non-database files. The ACFS file system is managed and administered by the Oracle Grid Infrastructure, So ACFS file systems will be impacted when shutting down the GI stack for patching GI homes. Shut down the processes (including non-Oracle processes) that use the files on ACFS, then unmount the ACFS file system. If the ACFS file system is used by Oracle database software, then perform Steps 1 and 2. 1.      Execute the following command to find the names of the CRS managed ACFS file system resource.  # crsctl stat res -w "TYPE = ora.acfs.type" -p | grep VOLUME 2.      Execute the following command to stop the CRS managed ACFS file system resource with the resource name found from Step 1. ...

How to perform GRID/RDBMS patching of DB systems deployed in OCI using dbcli command line utility

This procedure covers OS, Grid and RDBMS home patching for Oracle Cloud Infrastructure (OCI) DB systems using the dbcli utility. Run every step as root . Advantages of dbcli patching Simple command line, with no manual patch download or copy to the nodes. Patches are downloaded automatically if the service gateway can reach Oracle Object Storage and the YUM repos. Applied in a rolling fashion, so no downtime is needed. Disadvantages You can't choose the patch version; the latest available in OCI is applied. Rollback isn't possible from the console, and dbcli support is limited (see the note at the end). Steps 1. Backup Make sure a recent full backup exists before patching. 2. Blackout Create a blackout for both nodes (console or emctl ) to avoid unnecessary alerts. 3. Check patch availability # dbcli describe-component 4. Pre-checks Grid home: # dbcli update-server -p # dbcli describe-job -i <jobId> RDBMS home: # dbcli list-dbhomes...

To fix huge lag between primary and DR using 12c feature: 12c Recover from service

Scope If transport or apply lag is huge difference, then we can fix using 12c new feature “Recover from Service”. Prerequisites : Oracle Net connectivity is established between the physical standby database and the primary database. You can do this by adding an entry corresponding to the primary database in the tnsnames.ora file of the physical standby database. The password files on the primary database and the physical standby database are the same. The COMPATIBLE parameter in the initialization parameter file of the primary database and physical standby database is set to 12.0 or higher. Start RMAN and connect as target to the physical standby database. Detailed Steps: Step 1: Stop the MRP process in standby database. If dg broker: Edit database <dbname> set state=’APPLY-OFF’; Step 2:   Shut down the standby database and mount the same. Shut immediate;   (srvctl stop database –d <dbname>)  ...