Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Tuesday, 14 October 2014

Useful Query for DBA

Database default information:
Select username,profile,default_tablespace,temporary_tablespace from dba_users;
Database Structure information:
SELECT /*+ ordered */ d.tablespace_name tablespace, d.file_name filename
, d.bytes filesize, d.autoextensible autoextensible, d.increment_by * e.value increment_by, d.maxbytes maxbytes
FROM sys.dba_data_files d, v$datafile v
, (SELECT value FROM v$parameter WHERE name = 'db_block_size') e
WHERE (d.file_name =
SELECT d.tablespace_name tablespace, d.file_name filename, d.bytes filesize, d.autoextensible autoextensible
, d.increment_by * e.value increment_by, d.maxbytes maxbytes
FROM sys.dba_temp_files d, (SELECT value
FROM v$parameter
WHERE name = 'db_block_size') e
SELECT '[ ONLINE REDO LOG ]', a.member, b.bytes, null, TO_NUMBER(null), TO_NUMBER(null)
FROM v$logfile a, v$log b
SELECT '[ CONTROL FILE ]',, TO_NUMBER(null), null, TO_NUMBER(null), TO_NUMBER(null)
FROM v$controlfile a
Database Character Set Informations:
Select * from nls_database_parameters;
Database Segment Managment  Informations:
Database Object Information:
Select owner,object_type,count(*) from dba_objects Where owner not IN ('SYS','MDSYS','CTXSYS','HR','ORDSYS','OE','ODM_MTR','WMSYS','XDB','QS_WS',
Group by owner,object_type order by owner;
Find the last record from a table?
select * from employees where rowid in(select max(rowid) from employees);
select * from employees minus select * from employees where rownum < (select count(*) from employees);
Last SQL fired by the User on Database:
Select S.USERNAME||'('||s.sid||')-'||s.osuser UNAME  ,s.program||'-'||s.terminal||'('||s.machine||')' PROG ,s.sid||'/'||s.serial# sid,s.status "Status",p.spid,sql_text sqltext
from v$sqltext_with_newlines t,V$SESSION s , v$process p
where t.address =s.sql_address and p.addr=s.paddr(+) and t.hash_value = s.sql_hash_value
order by s.sid,t.piece;
Find Oracle timestamp from current SCN
Select to_char(CURRENT_SCN) from v$database;  -- oracle Ver. 10g or above
Select current_scn, dbms_flashback.get_system_change_number from v$database;  --standby case
SQL> select scn_to_timestamp(8843525) from dual;
Find UNDO information Table:
select to_char(begin_time,'hh24:mi:ss'),to_char(end_time,'hh24:mi:ss')
, maxquerylen,ssolderrcnt,nospaceerrcnt,undoblks,txncount from v$undostat
order by undoblks ;
Shared Pool Information:
select to_number(value) shared_pool_size, sum_obj_size, sum_sql_size, sum_user_size,
(sum_obj_size + sum_sql_size+sum_user_size)* 1.3 min_shared_pool
  from (select sum(sharable_mem) sum_obj_size
  from v$db_object_cache where type <> 'CURSOR'),
 (select sum(sharable_mem) sum_sql_size from v$sqlarea),
 (select sum(250 * users_opening) sum_user_size from v$sqlarea), v$parameter
 where name = 'shared_pool_size';
How to determine whether the datafiles are synchronized or not?
select status, checkpoint_change#, to_char(checkpoint_time, 'DD-MON-YYYY HH24:MI:SS') as checkpoint_time, count(*) from v$datafile_header
group by status, checkpoint_change#, checkpoint_time
order by status, checkpoint_change#, checkpoint_time;
Long Query Progress in database:
SELECT a.sid, a.serial#, b.username , opname OPERATION, target OBJECT,
TRUNC(elapsed_seconds, 5) "ET (s)", TO_CHAR(start_time, 'HH24:MI:SS') start_time,
ROUND((sofar/totalwork)*100, 2) "COMPLETE (%)"
FROM v$session_longops a, v$session b
WHERE a.sid = b.sid AND b.username not IN ('SYS', 'SYSTEM') AND totalwork > 0
ORDER BY elapsed_seconds;
How can we see the oldest flashback available?
You can use the following query to see the flashback data available.
SELECT to_char(sysdate,'YYYY-MM-DD HH24:MI') current_time, to_char(f.oldest_flashback_time, 'YYYY-MM-DD HH24:MI')OLDEST_FLASHBACK_TIME, (sysdate - f.oldest_flashback_time)*24*60 HIST_MIN FROM v$database d, V$FLASHBACK_DATABASE_LOG f;
How to get current session id, process id, client process id?
select b.sid, b.serial#, a.spid processid, b.process clientpid from v$process a, v$session b
where a.addr = b.paddr and b.audsid = userenv('sessionid');
V$SESSION.SID and V$SESSION.SERIAL# are database process id
V$PROCESS.SPID – Shadow process id on the database server
V$SESSION.PROCESS – Client process id, on windows it is “:” separated the first # is the process id on the client and 2nd one is the thread id.
How to find running jobs in oracle database
select sid, job,instance from dba_jobs_running;
select sid, serial#,machine, status, osuser,username from v$session where username!='NULL'; --all active users
select owner, job_name from DBA_SCHEDULER_RUNNING_JOBS; --for oracle 10g
How to find long running jobs in oracle database
select username,to_char(start_time, 'hh24:mi:ss dd/mm/yy') started, time_remaining remaining, message from v$session_longops 
where time_remaining = 0 order by time_remaining desc
Report Longest Rman Backup Job
Select username, to_char(start_time, 'hh24:mi:ss dd/mm/yy') started, TOTALWORK, SOFAR COMPLETED, time_remaining remaining, ELAPSED_SECONDS, message from v$session_longops where time_remaining = 0 and message like 'RMAN%' order by  ELAPSED_SECONDS DESC;
Last SQL Fired from particular Schema or Table:
Select CREATED, TIMESTAMP, last_ddl_time from all_objects
Display Log on Information of database:
Select SYSDATE-logon_time "Days", (SYSDATE-logon_time)*24 "Hours"
from  sys.v_$session where  sid=1;
Note: The above query will display since how many days and time your database is up. That means you can estimate the last login days and time. Here Sid=1 is the PMON
How do you find whether the instance was started with pfile or spfile
SELECT name, value FROM v$parameter WHERE name = 'spfile';
This query will return NULL if you are using PFILE
SELECT COUNT(*) FROM v$spparameter WHERE value IS NOT NULL;
If the count is non-zero then the instance is using a spfile, and if the count is zero then it is using a pfile:
SELECT DECODE(value, NULL, 'PFILE', 'SPFILE') "Init File Type" FROM sys.v_$parameter WHERE name = 'spfile';
How can you check which user has which Role:
Sql>Select * from DBA_ROLE_PRIVS order by grantee;
How to detect Migrated and chained row in a Table
You must execute script UTLCHAIN.SQL from before doing actual query
SQL> SELECT * FROM chained_rows;
You can also detect migrated and chained rows by checking the ‘table fetch continued row’ statistic in the v$sysstat view.
SQL> SELECT name, value FROM v$sysstat WHERE name = ‘table fetch continued row’;
Find Top 10 SQL from Database:
SELECT * FROM (SELECT rownum Substr(a.sql_text 1 200) sql_text Trunc(a.disk_reads/Decode(a.executions 0 1 a.executions)) reads_per_execution a.buffer_gets a.disk_reads a.executions a.sorts a.address FROM v$sqlarea a ORDER BY 3 DESC)WHERE rownum < 10; 
How to Get Database Version:
SELECT * from v$version;
SELECT VALUE  FROM v$system_parameter  WHERE name = 'compatible';
Find the Size of Schema:
SELECT SUM (bytes / 1024 / 1024) "size"
FROM dba_segments WHERE owner = '&owner';
Oracle SQL query over the view that shows actual Oracle Connections.
SELECT osuser, username, machine, program
FROM v$session ORDER BY osuser;  
SELECT program application, COUNT (program) Numero_Sesiones
FROM v$session GROUP BY program
ORDER BY Numero_Sesiones DESC;
Showing the Table Structure:
Getting Current Schema:
SELECT SYS_CONTEXT ('userenv', 'current_schema') FROM DUAL;
How to find the last time a session performed any activity?
select username, floor(last_call_et / 60) "Minutes", status from v$session
where username is not null order by last_call_et;
How to find parameters that will take into effect for new sessions?
SELECT name FROM v$parameter WHERE issys_modifiable = 'DEFERRED';
How to find tables that have a specific column name?
SELECT owner, table_name, column_name
FROM dba_tab_columns WHERE column_name like 'AMOUNT' ORDER by table_name;
Display database Recovery status:
SELECT * FROM   v$backup;
SELECT * FROM   v$recovery_status;
SELECT * FROM   v$recover_file;
SELECT * FROM   v$recovery_file_status;

SELECT * FROM   v$recovery_log;


  1. (query for Long Query Progress in database)

    above mentioned query retrieve information that are in progress/completed
    i am using following(link) query to check only current long running operations with more information,

    1. Thanks
      Adnan bhai,

      It seems to work on oracle 10g onward i think. In oracle 9i giving missing v$sql.sql_id

  2. There is multiple table.But one user have to give access to single table.What have to do ??

  3. Use the below command to give particular table access.
    For more information follow the below link:

  4. Dear sir,

    i am MCA 2015 batch passed fresher, i want to make my career in ORACLE DBA i have OCA 11g certification and hv training from and institute in noida.
    sir can you please guide me a right path to become a DBA that i follow.

    Heera Singh Bhandari