Script to take Daily Full Backups using RMAN In Oracle

Script to take Daily Full Backups using RMAN In Oracle

Need to create required directories:

mkdir -p /u03/scripts

mkdir -p /u03/rman_backup


Actual Script:

[oratest@mqmhrd1 rman_backup]$ cd scripts/

vi rman_backup.sh

. /home/oratest/MQMGEMTEST.env
today="$(date --date '0 days ago' '+%d_%b_%Y')";
BKUP_DIR=/u03/rman_backup/
BKUP_LOG=$BKUP_DIR/logs/"$ORACLE_SID"_Full_hot_backup_"$today".log
mkdir -p $BKUP_DIR/$today/hot
mkdir -p $BKUP_DIR/$today/cfile
mkdir -p $BKUP_DIR/$today/alogbkp
rman target / log=$BKUP_LOG<<EOF
run{
allocate channel d1 type disk;
allocate channel d2 type disk;
crosscheck backup;
crosscheck archivelog all;
delete noprompt obsolete;
delete noprompt expired backup;
delete noprompt expired backup of archivelog all;
sql 'alter system archive log current';
BACKUP AS COMPRESSED BACKUPSET INCREMENTAL LEVEL 0 format '$BKUP_DIR/$today/hot/%d-fullhot-%T-%t-%s-%p.bkp' filesperset 8 DATABASE plus archivelog format '$BKUP_DIR/$today/alogbkp/%d-alogs-fha-%T-%t-%s-%p.archbkp' filesperset 8;
delete noprompt obsolete;
backup current controlfile FORMAT='$BKUP_DIR/$today/cfile/%d-CTRL-%U.ctrl';
delete noprompt archivelog all completed before 'sysdate-4';
release channel d1;
release channel d2;
}
EOF

## Logfile deletion
find /u03/rman_backup/logs/*.log -mtime +2 -print -exec rm {} \;

## Empty Folder deletion
find /u03/rman_backup/ -type d -empty -delete
exit

Crontab:

[oratest@mqmhrd1 rman_backup]$ crontab -l
40 17 * * * /bin/sh /u03/rman_backup/scripts/rman_backup.sh
[oratest@mqmhrd1 rman_backup]$


RMAN> show all;

using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name MQMTEST are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 3;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1;
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/19.0.0/dbhome_1/dbs/snapcf_MQMTEST.f'; # default
RMAN>

Oracle Dataguard Reference

Oracle Dataguard Reference 

 Oracle Dataguard Reference

Oracle Dataguard Troubleshooting Steps

Oracle Dataguard Troubleshooting Steps

Dataguard Troubleshooting Commands

How to create startup & shutdown scripts form Oracle forms & reports ?

How to create startup & shutdown scripts form Oracle forms & reports ?

Startup Script:

cat start_services.sh

export ORACLE_BASE=/u01/oraclefrs
export MW_HOME=$ORACLE_BASE/middleware
export WLS_HOME=$MW_HOME/wlserver
export WL_HOME=$WLS_HOME
export DOMAIN_HOME=$ORACLE_BASE/middleware/user_projects/domains/basenew_domain
export OHS_INST=$DOMAIN_HOME/config/fmwconfig/components/OHS/instances/ohs1

# Start NodeManager
nohup $DOMAIN_HOME/bin/startNodeManager.sh > /dev/null 2>&1 &

sleep 15

# Start WebLogic Domain
nohup $DOMAIN_HOME/bin/startWebLogic.sh > /dev/null 2>&1 &

sleep 60

# Start the managed Servers

nohup $DOMAIN_HOME/bin/startManagedWebLogic.sh WLS_FORMS > /dev/null 2>&1 &
nohup $DOMAIN_HOME/bin/startManagedWebLogic.sh WLS_REPORTS > /dev/null 2>&1 &
sleep 15

# Start the web tier.

$DOMAIN_HOME/bin/startComponent.sh ohs1
echo " All services are up and Running.... "


Shutdown Script:

cat stop_services.sh

# Adjust paths and domain name to suit your installation.

export ORACLE_BASE=/u01/oraclefrs
export MW_HOME=$ORACLE_BASE/middleware
export WLS_HOME=$MW_HOME/wlserver
export WL_HOME=$WLS_HOME
export DOMAIN_HOME=$ORACLE_BASE/middleware/user_projects/domains/basenew_domain
export OHS_INST=$DOMAIN_HOME/config/fmwconfig/components/OHS/instances/ohs1

# Stop the web tier.

$DOMAIN_HOME/bin/stopComponent.sh ohs1

# Stop the managed Servers

$DOMAIN_HOME/bin/stopManagedWebLogic.sh WLS_FORMS
$DOMAIN_HOME/bin/stopManagedWebLogic.sh WLS_REPORTS

# Stop WebLogic Domain

$DOMAIN_HOME/bin/stopWebLogic.sh

# Stop NodeManager

$DOMAIN_HOME/bin/stopNodeManager.sh

Script to Track The RMAN Backup Status

Script to Track The RMAN Backup Status  

set lines 1000
set pages 1000
col cf for 9,999
col df for 9,999
col elapsed_seconds heading "ELAPSED|SECONDS"
col i0 for 9,999
col i1 for 9,999
col l for 9,999
col output_mbytes for 9,999,999 heading "OUTPUT MBYTES"
col session_recid for 999999 heading "SESSION|RECID"
col session_stamp for 99999999999 heading "SESSION|STAMP"
col status for a10 trunc
col time_taken_display for a10 heading "TIME|TAKEN"
col output_instance for 9999 heading "OUT|INST"
col "SESSION RECID" for a15
col "OUTPUT|MBYTES" for a10
col start_time for a20
col end_time for a20
col input_type for a10
col dow for a15
col "ELAPSED|SECONDS" for a10
col "TIME|TAKEN" for a10

select j.session_recid, j.session_stamp, to_char(j.start_time, 'yyyy-mm-dd hh24:mi:ss') start_time,to_char(j.end_time, 'yyyy-mm-dd hh24:mi:ss') end_time, (j.output_bytes/1024/1024) output_mbytes, j.status, j.input_type, decode(to_char(j.start_time, 'd'), 1, 'Sunday', 2, 'Monday',3, 'Tuesday', 4, 'Wednesday', 5, 'Thursday', 6, 'Friday',7, 'Saturday') dow, j.elapsed_seconds, j.time_taken_display, x.cf, x.df, x.i0, x.i1, x.l, ro.inst_id output_instance from V$RMAN_BACKUP_JOB_DETAILS j left outer join (select d.session_recid, d.session_stamp, sum(case when d.controlfile_included = 'YES' then d.pieces else 0 end) CF, sum(case when d.controlfile_included = 'NO' and d.backup_type||d.incremental_level = 'D' then d.pieces else 0 end) DF, sum(case when d.backup_type||d.incremental_level = 'D0' then d.pieces else 0 end) I0, sum(case when d.backup_type||d.incremental_level = 'I1' then d.pieces else 0 end) I1, sum(case when d.backup_type = 'L' then d.pieces else 0 end) L from V$BACKUP_SET_DETAILS d join V$BACKUP_SET s on s.set_stamp = d.set_stamp and s.set_count = d.set_count where s.input_file_scan_only = 'NO' group by d.session_recid, d.session_stamp) x on x.session_recid = j.session_recid and x.session_stamp = j.session_stamp left outer join (select o.session_recid, o.session_stamp, min(inst_id) inst_id from GV$RMAN_OUTPUT o group by o.session_recid, o.session_stamp) ro on ro.session_recid = j.session_recid and ro.session_stamp = j.session_stamp where j.start_time > trunc(sysdate)-&NUMBER_OF_DAYS order by j.start_time;

Different Ways To Check The Tablespaces Size

Different Ways To Check The Tablespace Size

 

Script 1.

set colsep |
set linesize 100 pages 100 trimspool on numwidth 14
col name format a25
col owner format a15
col "Used (GB)" format a15
col "Free (GB)" format a15
col "(Used) %" format a15
col "Size (M)" format a15

SELECT d.status "Status", d.tablespace_name "Name", TO_CHAR(NVL(a.bytes / 1024 / 1024 /1024, 0),'99,999,990.90') "Size (GB)", TO_CHAR(NVL(a.bytes - NVL(f.bytes, 0), 0)/1024/1024 /1024,'99999999.99') "Used (GB)", TO_CHAR(NVL(f.bytes / 1024 / 1024 /1024, 0),'99,999,990.90') "Free (GB)", TO_CHAR(NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0), '990.00') "(Used) %" FROM sys.dba_tablespaces d, (select tablespace_name, sum(bytes) bytes from dba_data_files group by tablespace_name) a, (select tablespace_name, sum(bytes) bytes from dba_free_space group by tablespace_name) f WHERE d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = f.tablespace_name(+) AND NOT (d.extent_management like 'LOCAL' AND d.contents like 'TEMPORARY') UNION ALL SELECT d.status "Status", d.tablespace_name "Name", TO_CHAR(NVL(a.bytes / 1024 / 1024 /1024, 0),'99,999,990.90') "Size (GB)", TO_CHAR(NVL(t.bytes,0)/1024/1024 /1024,'99999999.99') "Used (GB)", TO_CHAR(NVL((a.bytes -NVL(t.bytes, 0)) / 1024 / 1024 /1024, 0),'99,999,990.90') "Free (GB)",TO_CHAR(NVL(t.bytes / a.bytes * 100, 0), '990.00') "(Used) %" FROM sys.dba_tablespaces d, (select tablespace_name, sum(bytes) bytes from dba_temp_files group by tablespace_name) a, (select tablespace_name, sum(bytes_cached) bytes from v$temp_extent_pool group by tablespace_name) t WHERE d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = t.tablespace_name(+) AND d.extent_management like 'LOCAL' AND d.contents like 'TEMPORARY';

 

Script 2.

 

set lines 135
column TABLESPACE_NAME for a20
column FILE_NAME for a93
set lines 220 pages 220

 SELECT df.tablespace_name, df.size_mb, f.free_mb, df.max_size_mb, f.free_mb + (df.max_size_mb - df.size_mb) AS max_free_mb, df.file_name FROM   (SELECT file_id, file_name, tablespace_name, TRUNC(bytes/1024/1024) AS size_mb, TRUNC(GREATEST(bytes,maxbytes)/1024/1024) AS max_size_mb FROM   dba_data_files) df, (SELECT TRUNC(SUM(bytes)/1024/1024) AS free_mb, file_id FROM dba_free_space GROUP BY file_id) f WHERE  df.file_id = f.file_id (+) ORDER BY df.tablespace_name, df.file_name;

                                 

Script 3.                                 

                                 

select fs.tablespace_name "Tablespace", (df.totalspace - fs.freespace) "Used MB", fs.freespace "Free MB", df.totalspace "Total MB", round(100 * (fs.freespace / df.totalspace)) "Pct. Free" from (select tablespace_name, round(sum(bytes) / 1048576) TotalSpace from dba_data_files group by tablespace_name ) df, (select tablespace_name, round(sum(bytes) / 1048576) FreeSpace from dba_free_space group by tablespace_name ) fs where df.tablespace_name = fs.tablespace_name order by 1;

  

Script 4.  

 

set pages 80
set lin 120
set echo off
set feed off
column PCT format 999.99
column tbspce format A30
col container for a30

select substr(f.tablespace_name,1,30) tbspce, round(f.tsbytes/(1024*1024),0) "ALLOCATED(MB)", round(nvl(s.segbytes,0)/(1024*1024),0) "USED(MB)", round((nvl(s.segbytes,0)/f.tsbytes)*100,2) PCT, lower(vc.name) as container from (select con_id,tablespace_name,sum(bytes) tsbytes from cdb_data_files group by con_id,tablespace_name) f, (select con_id,tablespace_name,sum(bytes) segbytes from cdb_segments group by con_id,tablespace_name) s, v$containers vc where f.con_id=s.con_id(+) and f.tablespace_name=s.tablespace_name(+) and f.con_id=vc.con_id order by container, tbspce;

 

Script 5.

 

set pages 999
set lines 400

SELECT df.tablespace_name tablespace_name, max(df.autoextensible) auto_ext, round(df.maxbytes / (1024 * 1024), 2) max_ts_size,round((df.bytes - sum(fs.bytes)) / (df.maxbytes) * 100, 2) max_ts_pct_used,round(df.bytes / (1024 * 1024), 2) curr_ts_size,round((df.bytes - sum(fs.bytes)) / (1024 * 1024), 2) used_ts_size,round((df.bytes-sum(fs.bytes)) * 100 / df.bytes, 2) ts_pct_used,round(sum(fs.bytes) / (1024 * 1024), 2) free_ts_size, nvl(round(sum(fs.bytes) * 100 / df.bytes), 2) ts_pct_free FROM dba_free_space fs, (select tablespace_name, sum(bytes) bytes, sum(decode(maxbytes, 0, bytes, maxbytes)) maxbytes, max(autoextensible) autoextensible from dba_data_files group by tablespace_name) df WHERE fs.tablespace_name (+) = df.tablespace_name GROUP BY df.tablespace_name, df.bytes, df.maxbytes UNION ALL SELECT df.tablespace_name tablespace_name, max(df.autoextensible) auto_ext, round(df.maxbytes / (1024 * 1024), 2) max_ts_size, round((df.bytes - sum(fs.bytes)) / (df.maxbytes) * 100, 2) max_ts_pct_used, round(df.bytes / (1024 * 1024), 2) curr_ts_size, round((df.bytes - sum(fs.bytes)) / (1024 * 1024), 2) used_ts_size, round((df.bytes-sum(fs.bytes)) * 100 / df.bytes, 2) ts_pct_used, round(sum(fs.bytes) / (1024 * 1024), 2) free_ts_size, nvl(round(sum(fs.bytes) * 100 / df.bytes), 2) ts_pct_free FROM (select tablespace_name, bytes_used bytes from V$temp_space_header group by tablespace_name, bytes_free, bytes_used) fs, (select tablespace_name, sum(bytes) bytes, sum(decode(maxbytes, 0, bytes, maxbytes)) maxbytes, max(autoextensible) autoextensible from dba_temp_files group by tablespace_name) df WHERE fs.tablespace_name (+) = df.tablespace_name GROUP BY df.tablespace_name, df.bytes, df.maxbytes ORDER BY 4 DESC;

How To Generate AWR Report In Oracle

How To Generate AWR Report In Oracle

We can generate awr report for a particular time frame in the past using the script awrrpt.sql ( located under $ORACLE_HOME/rdbms/admin)

script – @$ORACLE_HOME/rdbms/admin/awrrpt.sql

For NON-SYSDBA USERS, BELOW GRANTS ARE REQUIRED TO GENERATE AWR REPORT:

SQL> grant connect,SELECT_CATALOG_ROLE to MQM;

SQL>  grant execute on dbms_workload_repository to MQM;

Note:

AWR report can be generating in RAC database using 2 scripts awrrpt.sql or awrrpti.sql

awrrpt.sql – > This will generate the one report for the database across all the nodes(i.e for all instances) for a partiular snapshot range.

awrrpti.sql – > This will genereate report for a particular instance, i.e for a 2 node RAC database , there will be two reports( one for each instance).

Following are the scripts that can be executed as sysdba in order to get the AWR, ASH and ADDM reports on Oracle RAC:

SQL script for getting AWR Report on RAC database:

SQL>@$ORACLE_HOME/rdbms/admin/awrgrpt.sql

SQL script for getting AWR Report for  single instance:

SQL>@$ORACLE_HOME/rdbms/admin/awrrpt.sql

SQL script for getting ASH Report on RAC database:

SQL>@$ORACLE_HOME/rdbms/admin/ashrpti.sql

SQL script for getting ASH Report for single Instance:

SQL>@$ORACLE_HOME/rdbms/admin/ashrpt.sql

SQL script for getting ADDM Report on RAC database:

SQL>@$ORACLE_HOME/rdbms/admin/addmrpti.sql

SQL script for getting ADDM Report for single instance:

SQL>@$ORACLE_HOME/rdbms/admin/addmrpt.sql

How to find table size?

How to find table size?

select segment_name,segment_type,bytes/1024/1024 MB from dba_segments where segment_type='TABLE' and segment_name='<yourtablename>';

Example:

select segment_name,segment_type,bytes/1024/1024 MB from dba_segments where segment_type='TABLE' and segment_name='QADER_T1';

SQL> select segment_name,segment_type,bytes/1024/1024 MB from dba_segments where segment_type='TABLE' and segment_name='QADER_T1';

SEGMENT_NAME                                                                                                                     SEGMENT_TYPE               MB

-------------------------------------------------------------------------------------------------------------------------------- ------------------ ----------

QADER_T1                                                                                                                         TABLE                    4776

How to check the database growth

How to check the database growth

SET LINESIZE 200
SET PAGESIZE 200
COL "Database Size" FORMAT a13
COL "Used Space" FORMAT a11
COL "Used in %" FORMAT a11
COL "Free in %" FORMAT a11
COL "Database Name" FORMAT a13
COL "Free Space" FORMAT a12
COL "Growth DAY" FORMAT a11
COL "Growth WEEK" FORMAT a12
COL "Growth DAY in %" FORMAT a16
COL "Growth WEEK in %" FORMAT a16
SELECT
(select min(creation_time) from v$datafile) "Create Time",
(select name from v$database) "Database Name",
ROUND((SUM(USED.BYTES) / 1024 / 1024 ),2) || ' MB' "Database Size",
ROUND((SUM(USED.BYTES) / 1024 / 1024 ) - ROUND(FREE.P / 1024 / 1024 ),2) || ' MB' "Used Space",
ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 )) / ROUND(SUM(USED.BYTES) / 1024 / 1024 ,2)*100,2) || '% MB' "Used in %",
ROUND((FREE.P / 1024 / 1024 ),2) || ' MB' "Free Space",
ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - ((SUM(USED.BYTES) / 1024 / 1024 ) - ROUND(FREE.P / 1024 / 1024 )))/ROUND(SUM(USED.BYTES) / 1024 / 1024,2 )*100,2) || '% MB' "Free in %",
ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 ))/(select sysdate-min(creation_time) from v$datafile),2) || ' MB' "Growth DAY",
ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 ))/(select sysdate-min(creation_time) from v$datafile)/ROUND((SUM(USED.BYTES) / 1024 / 1024 ),2)*100,3) || '% MB' "Growth DAY in %",
ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 ))/(select sysdate-min(creation_time) from v$datafile)*7,2) || ' MB' "Growth WEEK",
ROUND((((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 ))/(select sysdate-min(creation_time) from v$datafile)/ROUND((SUM(USED.BYTES) / 1024 / 1024 ),2)*100)*7,3) || '% MB' "Growth WEEK in %"
FROM    (SELECT BYTES FROM V$DATAFILE
UNION ALL
SELECT BYTES FROM V$TEMPFILE
UNION ALL
SELECT BYTES FROM V$LOG) USED,
(SELECT SUM(BYTES) AS P FROM DBA_FREE_SPACE) FREE
GROUP BY FREE.P;

How to check FRA location and it's utilization in Oracle

How to check FRA location and it's utilization in Oracle

set linesize 500
col NAME for a50
select name, ROUND(SPACE_LIMIT/1024/1024/1024,2) "Allocated Space(GB)",
round(SPACE_USED/1024/1024/1024,2) "Used Space(GB)",
round(SPACE_RECLAIMABLE/1024/1024/1024,2) "SPACE_RECLAIMABLE (GB)" ,
(select round(ESTIMATED_FLASHBACK_SIZE/1024/1024/1024,2)
from V$FLASHBACK_DATABASE_LOG) "Estimated Space (GB)"
from V$RECOVERY_FILE_DEST;

Check Long Running operations

Check Long Running operations:

select v$session_longops.sid || ' : ' || v$session_longops.serial# "session",
       V$session.username || ' : ' || V$session.osuser "DB / OS USER",
       v$process.program,
       V$session.machine || ' | ' || V$session.terminal Machine,
       v$session_longops.opname || ' | ' || v$session_longops.target OPNAME_TARGET,
       v$session_longops.elapsed_seconds || ' | ' ||
       v$session_longops.time_remaining Progress,
       v$process.spid,
       round(v$process.pga_used_mem / 1024 / 1024) || '  |  ' ||
       round(v$process.pga_alloc_mem / 1024 / 1024) "PGA USE | ALLOC",
       v$sql.sql_text,
       v$sql.sorts,
       v$sql.executions,
       v$sql.disk_reads,
       v$sql.parse_calls,
       v$sql.service,
       v$sql.module
  from v$session_longops, V$process, V$sql, V$session
 where v$session.sid = v$session_longops.sid
   and v$process.addr = v$session.paddr
   and v$session.sql_id = v$sql.sql_id
   and V$session_longops.time_remaining > 0;

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;

How to Monitor RMAN Backup through SQL Query

How to Monitor RMAN Backup through SQL Query

Script to check RMAN Configuration:

SELECT  name "PARAMETERS", value "RMAN CONFIGURATION"
FROM  v$rman_configuration
ORDER BY name;

Script to List RMAN Backup Piece:

SELECT bs.recid,
DECODE(   bp.status, 'A', 'Available', 'D', 'Deleted', 'X', 'Expired') status , bp.handle
handle, TO_CHAR(bp.start_time, 'dd/mm/yyyy HH24:MI:SS')start_time
, TO_CHAR(bp.completion_time, 'dd/mm/yyyy HH24:MI:SS') completion_time, bp.elapsed_seconds  "ELAPSED"
FROM
    v$backup_set bs JOIN v$backup_piece bp USING (set_stamp,set_count)
WHERE
    bp.status IN ('A', 'X') AND bp.completion_time > sysdate-1
ORDER BY bp.completion_time desc, bs.recid, bp.piece#;

Script to List RMAN Backup Set:

SELECT  bs.recid, DECODE(backup_type, 'L', 'Archived Logs', 'D', 'Datafile Full', 'I', 'Incremental') backup_type,
device_type "type", DECODE(bs.controlfile_included, 'NO', null, bs.controlfile_included) controlfile,
sp.spfile_included spfile, bs.incremental_level L,TO_CHAR(bs.start_time, 'dd/mm/yyyy HH24:MI:SS') start_time
  , TO_CHAR(bs.completion_time, 'dd/mm/yyyy HH24:MI:SS')  completion_time, bs.elapsed_seconds "ELAPSED", bp.tag, bs.block_size "BLOCK"
  FROM  v$backup_set  bs, (select distinct set_stamp, set_count, tag, device_type from v$backup_piece where status in ('A', 'X'))  bp,
   (select distinct set_stamp, set_count, 'YES' spfile_included from v$backup_spfile) sp
WHERE completion_time > sysdate -1
  AND bs.set_stamp = bp.set_stamp
  AND bs.set_count = bp.set_count
  AND bs.set_stamp = sp.set_stamp (+)
  AND bs.set_count = sp.set_count (+)
ORDER BY  completion_time desc, bs.recid;

Script to List RMAN Job done:

select SID, serial#, opname, START_TIME,TOTALWORK, sofar, (sofar/totalwork) * 100 done,
sysdate + TIME_REMAINING/3600/24 end_at, elapsed_seconds elapsed, username
from v$session_longops
Where totalwork = sofar
AND opname NOT LIKE '%aggregate%'
AND start_time > sysdate -1
AND opname like 'RMAN%';

Note: If you need to query running status of rman backup then you have to set totalwork > sofar in where condition.

Script to Monitor RMAN Job in Progress:

SELECT sid, serial#, sofar, totalwork, opname, round(sofar/totalwork*100,2) AS pct_complete
FROM v$session_longops
WHERE opname LIKE 'RMAN%'
AND opname NOT LIKE '%aggregate%' AND totalwork != 0
AND sofar <> totalwork;
Script to list RMAN spfile & Controlfile Backup:
SELECT bs.recid, sp.spfile_included spfile
, TO_CHAR(bs.completion_time, 'dd/mm/yyyy HH24:MI:SS') completion_time
, DECODE(status, 'A', 'Available', 'D', 'Deleted', 'X', 'Expired') status, handle
FROM v$backup_set  bs, v$backup_piece  bp, (select distinct set_stamp, set_count, 'YES' spfile_included
 from v$backup_spfile) sp
WHERE bs.set_stamp = bp.set_stamp
  AND bs.completion_time > sysdate -1
  AND bs.set_count = bp.set_count
  AND bp.status IN ('A', 'X')
  AND bs.set_stamp = sp.set_stamp
  AND bs.set_count = sp.set_count
ORDER BY  bs.completion_time desc, bs.recid, piece#;

Script to find size of Datafile and Archivelog Backup:

select trunc(completion_time) "BACKUP_DATE", sum(blocks*block_size)/1024/1024 "DBF_BACKUP",
(SELECT sum(blocks*block_size)/1024/1024  from v$backup_redolog
WHERE first_time > sysdate-1) "ARC_BACKUP"
from v$backup_datafile
WHERE completion_time > sysdate - 1
group by trunc(completion_time)
order by 1 DESC;

You can also use V$SESSION_LONGOPS to estimate when a job will complete for an RMAN backup or restore operation, because the total amount of work, the time the job began, and the amount of work left are known values.

SELECT sid, serial#, opname, ROUND(sofar/totalwork*100,2) AS pct_complete,
TO_CHAR(start_time,'dd-mon-yy hh24:mi') start_time, (sysdate-start_time)*24 hours_running,
((sysdate-start_time)*24*60)/(sofar/totalwork)-(sysdate-start_time)
*24*60 minutes_left, TO_CHAR((sysdate-start_time)/(sofar/totalwork)+start_time,'dd-mon-yy hh24:mi') est_comp_time
FROM v$session_longops
WHERE opname LIKE 'RMAN%'
AND opname NOT LIKE '%aggregate%'
AND totalwork != 0
AND sofar <> totalwork;                                             

Script to Report RMAN full, incremental and archivelog backups:

select SESSION_KEY, INPUT_TYPE, STATUS,
to_char(START_TIME,'mm/dd/yy hh24:mi') start_time,
to_char(END_TIME,'mm/dd/yy hh24:mi')   end_time,
elapsed_seconds/3600 hrs
from V$RMAN_BACKUP_JOB_DETAILS
order by session_key;

Script to Report RMAN full, incremental without archivelog backups:

col STATUS format a9
col hrs format 999.99
select
SESSION_KEY, INPUT_TYPE, STATUS,
to_char(START_TIME,'mm/dd/yy hh24:mi') start_time,
to_char(END_TIME,'mm/dd/yy hh24:mi')   end_time,
elapsed_seconds/3600                   hrs
from V$RMAN_BACKUP_JOB_DETAILS
where input_type='DB INCR'
order by session_key;

Tracking Alert Log file by x$dbgalertext

Tracking Alert Log file by x$dbgalertext

Instead of reviewing whole alert log file for any Particular information, We can search using that key word from X$DBGALERTEXT Table.

Grep The word “Starting Up” of Alert log file from Instance.

col ORIGINATING_TIMESTAMP for a40
col MESSAGE_TEXT for a80
set linesize 500
SELECT originating_timestamp, message_text FROM sys.x$dbgalertext WHERE message_text LIKE '%Starting up%';

Scripts To Monitor Tablespaces and datafiles

Scripts To Monitor Tablespaces and datafiles

To check the tablespace in detail.

set linesize 160
set pagesize 300
select distinct
a.tablespace_name,
SUM(a.bytes)/1024/1024 "Used Size MB",
SUM(decode(b.maxextend, null, A.BYTES/1024/1024, b.maxextend*8192/1024/1024)) "Max Size Mb",
(SUM(a.bytes)/1024/1024 - round(c."Free"/1024/1024)) "Total Used MB",
(SUM(decode(b.maxextend, null, A.BYTES/1024/1024, b.maxextend*8192/1024/1024)) - (SUM(a.bytes)/1024/1024 - round(c."Free"/1024/1024))) "Total Free MB",
round(100*(SUM(a.bytes)/1024/1024 - round(c."Free"/1024/1024))/(SUM(decode(b.maxextend, null, A.BYTES/1024/1024, b.maxextend*8192/1024/1024)))) "Used Percentage"
from
dba_data_files a,
sys.filext$ b,
(SELECT d.tablespace_name , sum(nvl(c.bytes,0)) "Free" FROM dba_tablespaces d,DBA_FREE_SPACE c where d.tablespace_name = c.tablespace_name(+) group by d.tablespace_name) c
where a.file_id = b.file#(+)
and a.tablespace_name = c.tablespace_name
GROUP by a.tablespace_name, c."Free"/1024
order by round(100*(SUM(a.bytes)/1024/1024 - round(c."Free"/1024/1024))/(SUM(decode(b.maxextend, null, A.BYTES/1024/1024, b.maxextend*8192/1024/1024)))) desc;

Note:
Tablespace usage, Free sizes will play a role when datafiles are enabled AUTOEXTENSIBLE.

Another script to check the tablespace in detail.

set pages 999
set lines 400
SELECT df.tablespace_name tablespace_name,
 max(df.autoextensible) auto_ext,
 round(df.maxbytes / (1024 * 1024), 2) max_ts_size,
 round((df.bytes - sum(fs.bytes)) / (df.maxbytes) * 100, 2) max_ts_pct_used,
 round(df.bytes / (1024 * 1024), 2) curr_ts_size,
 round((df.bytes - sum(fs.bytes)) / (1024 * 1024), 2) used_ts_size,
 round((df.bytes-sum(fs.bytes)) * 100 / df.bytes, 2) ts_pct_used,
 round(sum(fs.bytes) / (1024 * 1024), 2) free_ts_size,
 nvl(round(sum(fs.bytes) * 100 / df.bytes), 2) ts_pct_free
FROM dba_free_space fs,
 (select tablespace_name,
 sum(bytes) bytes,
 sum(decode(maxbytes, 0, bytes, maxbytes)) maxbytes,
 max(autoextensible) autoextensible
 from dba_data_files
 group by tablespace_name) df
WHERE fs.tablespace_name (+) = df.tablespace_name
GROUP BY df.tablespace_name, df.bytes, df.maxbytes
UNION ALL
SELECT df.tablespace_name tablespace_name,
 max(df.autoextensible) auto_ext,
 round(df.maxbytes / (1024 * 1024), 2) max_ts_size,
 round((df.bytes - sum(fs.bytes)) / (df.maxbytes) * 100, 2) max_ts_pct_used,
 round(df.bytes / (1024 * 1024), 2) curr_ts_size,
 round((df.bytes - sum(fs.bytes)) / (1024 * 1024), 2) used_ts_size,
 round((df.bytes-sum(fs.bytes)) * 100 / df.bytes, 2) ts_pct_used,
 round(sum(fs.bytes) / (1024 * 1024), 2) free_ts_size,
 nvl(round(sum(fs.bytes) * 100 / df.bytes), 2) ts_pct_free
FROM (select tablespace_name, bytes_used bytes
 from V$temp_space_header
 group by tablespace_name, bytes_free, bytes_used) fs,
 (select tablespace_name,
 sum(bytes) bytes,
 sum(decode(maxbytes, 0, bytes, maxbytes)) maxbytes,
 max(autoextensible) autoextensible
 from dba_temp_files
 group by tablespace_name) df
WHERE fs.tablespace_name (+) = df.tablespace_name
GROUP BY df.tablespace_name, df.bytes, df.maxbytes
ORDER BY 4 DESC;

To check the free and used space:

SELECT /* + RULE */  df.tablespace_name "Tablespace",
df.bytes / (1024 * 1024) "Size (MB)", SUM(fs.bytes) / (1024 * 1024) "Free (MB)",
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;

To check the tablespace used in GB's

set linesize 100
set pagesize 100
col file_name for a60

select
    a.tablespace_name,
    round(SUM(a.bytes)/(1024*1024)) CURRENT_MB,
    round(SUM(decode(a.AUTOEXTENSIBLE, 'NO', A.BYTES/(1024*1024), GREATEST (a.MAXBYTES/(1024*1024),A.BYTES/(1024*1024))))) MAX_MB,
    round((SUM(a.bytes)/(1024*1024) - c.Free/1024/1024)) USED_MB,
    round((SUM(decode(a.AUTOEXTENSIBLE, 'NO', A.BYTES/(1024*1024), GREATEST (a.MAXBYTES/(1024*1024),A.BYTES/(1024*1024)))) - (SUM(a.bytes)/(1024*1024) - round(c.Free/1024/1024))),2) FREE_MB,
    round(100*(SUM(a.bytes)/(1024*1024) - round(c.Free/1024/1024))/(SUM(decode(a.AUTOEXTENSIBLE, 'NO', A.BYTES/(1024*1024),GREATEST (a.MAXBYTES/(1024*1024),A.BYTES/(1024*1024)))))) USED_PCT
from
    dba_data_files a,
    (
        SELECT
            d.tablespace_name ,sum(nvl(c.bytes,0)) Free
        FROM
            dba_tablespaces d,
            DBA_FREE_SPACE c
        WHERE
            d.tablespace_name = c.tablespace_name(+)
            --AND d.contents='PERMANENT'
            --AND d.status='ONLINE'
            group by d.tablespace_name
    ) c
WHERE
    a.tablespace_name = c.tablespace_name
GROUP BY a.tablespace_name, c.Free/1024
order by 6;


To check Growth rate of Tablespaces

SELECT TO_CHAR (sp.begin_interval_time,'DD-MM-YYYY') days,
 ts.tsname , max(round((tsu.tablespace_size* dt.block_size )/(1024*1024),2) ) cur_size_MB,
 max(round((tsu.tablespace_usedsize* dt.block_size )/(1024*1024),2)) usedsize_MB
 FROM DBA_HIST_TBSPC_SPACE_USAGE tsu, DBA_HIST_TABLESPACE_STAT ts, DBA_HIST_SNAPSHOT sp,
 DBA_TABLESPACES dt
 WHERE tsu.tablespace_id= ts.ts# AND tsu.snap_id = sp.snap_id
 AND ts.tsname = dt.tablespace_name AND ts.tsname NOT IN ('SYSAUX','SYSTEM')
 GROUP BY TO_CHAR (sp.begin_interval_time,'DD-MM-YYYY'), ts.tsname
 ORDER BY ts.tsname, days;


Which schema are taking how much space:

Select obj.owner "Owner", obj_cnt "Objects", decode(seg_size, NULL, 0, seg_size) "size MB"
from (select owner, count(*) obj_cnt from dba_objects group by owner) obj,
 (select owner, ceil(sum(bytes)/1024/1024) seg_size  from dba_segments group by owner) seg
  where obj.owner  = seg.owner(+)
  order    by 3 desc ,2 desc, 1;


To Check Default Temporary Tablespace Name:

Select * from database_properties where PROPERTY_NAME like '%DEFAULT%';


To know default and Temporary Tablespace for particualr User:

Select username,temporary_tablespace,default_tablespace from dba_users where username='HRMS';


To know Default Tablespace for All User:

Select default_tablespace,temporary_tablespace,username from dba_users;
To Check Datafiles used and Free Space:
SELECT SUBSTR (df.NAME, 1, 40) file_name,dfs.tablespace_name, df.bytes / 1024 / 1024 allocated_mb,
((df.bytes / 1024 / 1024) -  NVL (SUM (dfs.bytes) / 1024 / 1024, 0)) used_mb,
NVL (SUM (dfs.bytes) / 1024 / 1024, 0) free_space_mb
FROM v$datafile df, dba_free_space dfs
WHERE df.file# = dfs.file_id(+)
GROUP BY dfs.file_id, df.NAME, df.file#, df.bytes,dfs.tablespace_name
ORDER BY file_name;


To check Used free space in Temporary Tablespace:

SELECT tablespace_name, SUM(bytes_used/1024/1024) USED, SUM(bytes_free/1024/1024) FREE
FROM   V$temp_space_header GROUP  BY tablespace_name;
SELECT   A.tablespace_name tablespace, D.mb_total,
         SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
         D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM     v$sort_segment A,
         ( SELECT   B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
         FROM     v$tablespace B, v$tempfile C
         WHERE    B.ts#= C.ts#
         GROUP BY B.name, C.block_size
         ) D
WHERE    A.tablespace_name = D.name
GROUP by A.tablespace_name, D.mb_total;

Who is using which UNDO or TEMP segment:

SELECT TO_CHAR(s.sid)||','||TO_CHAR(s.serial#) sid_serial,
NVL(s.username, 'None') orauser,s.program, r.name undoseg,
t.used_ublk * TO_NUMBER(x.value)/1024||'K' "Undo"
FROM sys.v_$rollname r, sys.v_$session s, sys.v_$transaction t, sys.v_$parameter   x
WHERE s.taddr = t.addr AND r.usn   = t.xidusn(+) AND x.name  = 'db_block_size';

Who is using the Temp Segment:

SELECT b.tablespace, ROUND(((b.blocks*p.value)/1024/1024),2)||'M' "SIZE",
a.sid||','||a.serial# SID_SERIAL, a.username, a.program
FROM sys.v_$session a,
sys.v_$sort_usage b, sys.v_$parameter p
WHERE p.name  = 'db_block_size' AND a.saddr = b.session_addr
ORDER BY b.tablespace, b.blocks;

Total Size and Free Size of Database:

Select round(sum(used.bytes) / 1024 / 1024/1024 ) || ' GB' "Database Size",
round(free.p / 1024 / 1024/1024) || ' GB' "Free space"
from (select bytes from v$datafile
      union all
      select bytes from v$tempfile
      union all
      select bytes from v$log) used,
(select sum(bytes) as p from dba_free_space) free
group by free.p;


To find used space of datafiles:

SELECT SUM(bytes)/1024/1024/1024 "GB" FROM dba_segments;
IO status of all of the datafiles in database:
WITH total_io AS
     (SELECT SUM (phyrds + phywrts) sum_io
        FROM v$filestat)
SELECT   NAME, phyrds, phywrts, ((phyrds + phywrts) / c.sum_io) * 100 PERCENT,
         phyblkrd, (phyblkrd / GREATEST (phyrds, 1)) ratio
    FROM SYS.v_$filestat a, SYS.v_$dbfile b, total_io c
   WHERE a.file# = b.file#
ORDER BY a.file#;
Displays Smallest size the datafiles can shrink to without a re-organize.
SELECT a.tablespace_name, a.file_name, a.bytes AS current_bytes, a.bytes - b.resize_to AS shrink_by_bytes, b.resize_to AS resize_to_bytes
FROM   dba_data_files a, (SELECT file_id, MAX((block_id+blocks-1)*&v_block_size) AS resize_to
        FROM   dba_extents
        GROUP by file_id) b
        WHERE  a.file_id = b.file_id
        ORDER BY a.tablespace_name, a.file_name;

Scripts to Find datafiles increment details:

Select  SUBSTR(fn.name,1,DECODE(INSTR(fn.name,'/',2),0,INSTR(fn.name,':',1),INSTR(fn.name,'/',2))) mount_point,tn.name   tabsp_name,fn.name   file_name,
ddf.bytes/1024/1024 cur_size, decode(fex.maxextend,
NULL,ddf.bytes/1024/1024,fex.maxextend*tn.blocksize/1024/1024) max_size,
nvl(fex.maxextend,0)*tn.blocksize/1024/1024 - decode(fex.maxextend,NULL,0,ddf.bytes/1024/1024)   unallocated,nvl(fex.inc,0)*tn.blocksize/1024/1024  inc_by
from  sys.v_$dbfile fn,    sys.ts$  tn,    sys.filext$ fex,    sys.file$  ft,    dba_data_files ddf
where    fn.file# = ft.file# and  fn.file# = ddf.file_id
and    tn.ts# = ft.ts# and    fn.file# = fex.file#(+)
order by 1;