Scripts For Database Details
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 = v.name)
UNION
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
UNION
SELECT '[ ONLINE REDO LOG ]', a.member, b.bytes, null, TO_NUMBER(null), TO_NUMBER(null)
FROM v$logfile a, v$log b
WHERE a.group# = b.group#
UNION
SELECT '[ CONTROL FILE ]', a.name, TO_NUMBER(null), null, TO_NUMBER(null), TO_NUMBER(null)
FROM v$controlfile a
ORDER BY 1,2;
Database Character Set Informations:
Select * from nls_database_parameters;
Database Segment Managment Informations:
Select TABLESPACE_NAME, BLOCK_SIZE, EXTENT_MANAGEMENT, SEGMENT_SPACE_MANAGEMENT from dba_tablespaces;
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',
'RMAN','SCOTT','QS_ADM','QS_CBADM', 'ORDSYS', 'OUTLN', 'PM', 'QS_OS', 'QS_ES', 'ODM', 'OLAPSYS','WKSYS','SH','SYSTEM','ORDPLUGINS','QS','QS_CS')
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
WHERE OWNER='HRMS' AND OBJECT_TYPE='TABLE' AND OBJECT_NAME='PAYROLL_MAIN_FILE';
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:
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
ANALYZE TABLE scott.emp LIST CHAINED ROWS;
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’;
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;
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;
