Scripts For Database Details

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;