Daily Use Query/Command
For single tablesapce with max size
select df.tablespace_name "Tablespace",
totalusedspace "Used MB",
(df.totalspace - tu.totalusedspace) "Free MB",
df.totalspace "Total MB",
round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace)) "Pct. Free"
from
(select tablespace_name,
SUM (DECODE (Autoextensible,'YES', maxbytes/ 1048576,'NO', BYTES/ 1048576)) TotalSpace
from dba_data_files
group by tablespace_name) df,
(select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_name
from dba_segments
group by tablespace_name) tu
where df.tablespace_name = tu.tablespace_name
and tu.tablespace_name='&tablespace_name';
Autoextensible file
col file_name for a65
select file_name,autoextensible,bytes/1024/1024 as "File Size",maxbytes/1024/1024 as "File Maxsize" from
dba_data_files where tablespace_name='&tbs_name';
Utilization of ASM diskgroup
select NAME,STATE,TOTAL_MB,FREE_MB, round ((FREE_MB/TOTAL_MB)*100,2) PCT_FREE from v$asm_diskgroup ;
select name,
round((total_mb-free_mb)/1024) USED_GB,
round(free_mb/1024) FREE_GB,
round(total_mb/1024) TOTAL_GB,
round(free_mb/total_mb*100) "FREE_%"
from v$asm_diskgroup where name like '%&name%' order by name;
for resize Tablespace
column value new_val blksize
select value
from v$parameter
where name = 'db_block_size'
/
select /*+ rule */ 'alter database datafile ''' ||
file_name || ''' resize ' ||
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 )
|| 'm;' cmd
from dba_data_files a,
( select file_id,
max(block_id+blocks-1) hwm
from dba_extents
group by file_id ) b
where a.tablespace_name='&TBSPACE' and a.file_id = b.file_id(+)
and
ceil(blocks*&&blksize/1024/1024)-
ceil((nvl(hwm,1)*
&&blksize)/1024/1024 ) > 0
/
All tablespace details including Temp Tablespace
set pages 999
col tablespace_name format a40
col "size MB" format 999,999,999
col "free MB" format 99,999,999
col "% Used" format 999
select tsu.tablespace_name, ceil(tsu.used_mb) "size MB"
, decode(ceil(tsf.free_mb), NULL,0,ceil(tsf.free_mb)) "free MB"
, decode(100 - ceil(tsf.free_mb/tsu.used_mb*100), NULL, 100,
100 - ceil(tsf.free_mb/tsu.used_mb*100)) "% used"
from (select tablespace_name, sum(bytes)/1024/1024 used_mb
from dba_data_files group by tablespace_name union all
select tablespace_name || ' **TEMP**'
, sum(bytes)/1024/1024 used_mb
from dba_temp_files group by tablespace_name) tsu
, (select tablespace_name, sum(bytes)/1024/1024 free_mb
from dba_free_space group by tablespace_name) tsf
where tsu.tablespace_name = tsf.tablespace_name (+)
order by 4
/
All tablespace details including Temp Tablespace
set pages 999
col tablespace_name format a40
col "size MB" format 999,999,999
col "free MB" format 99,999,999
col "% Used" format 999
select tsu.tablespace_name, ceil(tsu.used_mb) "size MB"
, decode(ceil(tsf.free_mb), NULL,0,ceil(tsf.free_mb)) "free MB"
, decode(100 - ceil(tsf.free_mb/tsu.used_mb*100), NULL, 100,
100 - ceil(tsf.free_mb/tsu.used_mb*100)) "% used"
from (select tablespace_name, sum(bytes)/1024/1024 used_mb
from dba_data_files group by tablespace_name union all
select tablespace_name || ' **TEMP**'
, sum(bytes)/1024/1024 used_mb
from dba_temp_files group by tablespace_name) tsu
, (select tablespace_name, sum(bytes)/1024/1024 free_mb
from dba_free_space group by tablespace_name) tsf
where tsu.tablespace_name = tsf.tablespace_name (+)
order by 4
/
Tablespaces that are >=80% full, and how much to add to make them 80% again
set pages 999 lines 100
col "Tablespace" for a50
col "Size MB" for 999999999
col "%Used" for 999
col "Add (80%)" for 999999
select tsu.tablespace_name "Tablespace"
, ceil(tsu.used_mb) "Size MB"
, 100 - floor(tsf.free_mb/tsu.used_mb*100) "%Used"
, ceil((tsu.used_mb - tsf.free_mb) / .8) - tsu.used_mb "Add (80%)"
from (select tablespace_name, sum(bytes)/1024/1024 used_mb
from dba_data_files group by tablespace_name) tsu
, (select ts.tablespace_name
, nvl(sum(bytes)/1024/1024, 0) free_mb
from dba_tablespaces ts, dba_free_space fs
where ts.tablespace_name = fs.tablespace_name (+)
group by ts.tablespace_name) tsf
where tsu.tablespace_name = tsf.tablespace_name (+)
and 100 - floor(tsf.free_mb/tsu.used_mb*100) >= 80
order by 3,4
/
TEMP tablespace related query
select (s.tot_used_blocks/f.total_blocks)*100 as "percent used"
from (select sum(used_blocks) tot_used_blocks from v$sort_segment where tablespace_name='TEMP') s,
(select sum(blocks) total_blocks from dba_temp_files where tablespace_name='TEMP') f;
select file_name,autoextensible,bytes/1024/1024 as "File Size",maxbytes/1024/1024 as "File Maxsize" from
dba_temp_files where tablespace_name='&tbs_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
/
Database start time etc
set line 500
col OPEN_MODE format a15
col LOG_MODE format a20
col CREATED format a30
col host_name format a15
col INSTANCE_NAME format a15
col DATABASE_ROLE format a18
col DB_NAME format a15
col startup_time format a20
col LOGINS format a15
select d.dbid,i.instance_name,d.name "DB_NAME",d.open_mode,d.log_mode,d.database_role,i.LOGINS,to_char(i.startup_time,'DD-MM-YY HH24:MI:SS') "STARTUP_TIME",host_name from v$database d, v$instance i;
set line 500
select d.dbid,i.instance_name,d.name "DB_NAME",d.open_mode,i.LOGINS,to_char(i.startup_time,'DD-MM-YY HH24:MI:SS') "STARTUP_TIME",host_name from v$database d, v$instance i;
srvctl status database -d Instance_name1
srvctl status database -d Instance name
select INSTANCE_NAME,HOST_NAME,to_char(STARTUP_TIME,'dd-mon-yy hh24:mi:ss') created,STATUS,LOGINS from v$instance;
select name,open_mode,LOG_MODE,to_char(created,'dd-mon-yy hh24:mi:ss') created from v$database;
Daily Use Important Command
Count Number of Dtabafile at filesystem
find oradata* -name '*.dbf' |wc -l
Crontab Entry
crontab -l
# * * * * * command to be executed
# | | | | |
# | | | | +--- day of week (0 - 6) (Sunday = 0)
# | | | +-------- month (1 - 12)
# | | +------------- day of month (1 - 31)
# | +------------------ hour (0 - 23)
# +----------------------- min (0 - 59)
Database Size
col "Database Size" format a20
col "Free space" format a20
col "Used space" format a20
select round(sum(used.bytes) / 1024 / 1024 / 1024 ) || ' GB' "Database Size"
, round(sum(used.bytes) / 1024 / 1024 / 1024 ) -
round(free.p / 1024 / 1024 / 1024) || ' GB' "Used space"
, 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
/
Find Number of database running on linux server -
ls -l $ORACLE_HOME/dbs/orapw*
Flashback related
SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
NO
SQL> alter database flashback on;
Database altered.
SQL> SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
YES
Query check fregmented size of schemas::
select owner,sum(round((blocks*16)/1024,2))"Fragmented size MB",sum(round((num_rows*avg_row_len/1024/1024),2))"Actual size MB",sum(round
2 (round((blocks*16)/1024,2)-round((num_rows*avg_row_len/1024/1024),2),2)) "Diff" from dba_tables where owner in('EPIMETA','EPIOP','EPIMART')
3 group by owner;
to check fregmented size of all schmas in user :
SQL> select owner,sum(round((blocks*16)/1024,2))"Fragmented size MB",sum(round((num_rows*avg_row_len/1024/1024),2))"Actual size MB",sum(round(round((blocks*16)/1024,2)-round((num_rows*avg_row_len/1024/1024),2),2)) "Diff" from dba_tables group by owner;
DBA grant to normal user
SQL> select * from v$pwfile_users;
USERNAME SYSDB SYSOP SYSAS
------------------------------ ----- ----- -----
SYS TRUE TRUE FALSE
RMAN TRUE FALSE FALSE
SQL> select name from v$database;
NAME
---------
XXXX
SQL> grant sysdba to USERNAME;
Grant succeeded.
SQL> select * from v$pwfile_users;
USERNAME SYSDB SYSOP SYSAS
------------------------------ ----- ----- -----
SYS TRUE TRUE FALSE
RMAN TRUE FALSE FALSE
USERNAME TRUE FALSE FALSE
Know the Growth trend of database
select to_char(CREATION_TIME,'RRRR') year, to_char(CREATION_TIME,'MM') month, round(sum(bytes)/1024/1024/1024) GB
from v$datafile
group by to_char(CREATION_TIME,'RRRR'), to_char(CREATION_TIME,'MM')
order by 1, 2;
High water mark related query
set lines 1000
col file_name for a65
col tablespace for a15
col actual for a20
select tablespace_name tablespace
, file_name
, decode(actual_size,-1,'n/a (offline)',actual_size) actual
, decode(shrink_to,-1,'n/a',null,'can''t be shrinked',actual_size,'can''t be shrinked', shrink_to) shrink_to
from (
select -- locally managed files that can be resized (last used block < last free block)
d.tablespace_name
, d.file_id
, d.file_name
, d.bytes/1024 actual_size --MB
, nvl((m.maxblock-1)*t.blocksize/1024, (64*1024+t.bitmapped*t.blocksize)/1024) shrink_to
from dba_data_files d
, sys.ts$ t
, (select u.fileid file_id, max(u.block+u.length) maxblock --last used block +1
from dba_lmt_used_extents u group by fileid ) m
where m.file_id(+)=d.file_id
and d.tablespace_name =t.name
and t.bitmapped > 0
and d.bytes is not null --online
union -- Offline LMT (Offline DMT info is still available in datadict.)
select d.tablespace_name, d.file_id, d.file_name, -1, -1 /* -1 => n/a, null => cannot be resized */
from dba_data_files d
where bytes is null -- Offline LMT: Size is unknown
union -- dictionary managed (online + offline)
select ts.name, a.file#, df.name, ts.blocksize*f.blocks/1024
, case when nvl(min(a.bl-1),f.blocks) > 2 --nvl: no free space
then nvl(min(a.bl-1),f.blocks)*(ts.blocksize)/1024
else 2*(ts.blocksize)/1024 end
from (select f.ts#, f.file#, block# bl
from sys.fet$ f
start with block#=(select d.blocks-f.length+1 m from sys.file$ d where f.file#=d.relfile#)
--end of free space area = end of datafile
connect by prior block#=length+block# --if area before is also free space
) a
, v$dbfile df
, sys.file$ f
, sys.ts$ ts
where f.relfile# = a.file# (+) -- a no rows -> no free space
and f.ts# = ts.ts#
and f.file# = df.file#
and ts.bitmapped=0 --0=dictionary managed, else=locally managed
group by ts.name, a.file#, df.name, f.blocks , ts.blocksize
)
order by tablespace_name, file_name
/
Index rebuilding dynamic query
SQL> select 'alter index '||owner||'.'||SEGMENT_NAME||' rebuild tablespace XXXX;' from dba_segments where SEGMENT_TYPE='INDEX' and owner='XX1' and bytes/1024/1024 > 1;
'ALTERINDEX'||OWNER||'.'||SEGMENT_NAME||'REBUILDTABLESPACEREDWOOD;'
--------------------------------------------------------------------------------------------------------------------------------------------------------
alter index SYSJCS.RWS_CCOL$_PK rebuild tablespace XXXX;
alter index SYSJCS.RWS_JOB$_PK rebuild tablespace XXXX;
alter index SYSJCS.RWS_JOB$_FK_FMT$_IDX rebuild tablespace XXXX;
alter index SYSJCS.RWS_JOB$_FK_OBJ#_IDX rebuild tablespace XXXX;
Archive Generation Details
This is used to check archive log generated in MB for database in last few days -
set pages 100
set pages 1000
select trunc(COMPLETION_TIME,'DD') Day, thread#, round(sum(BLOCKS*BLOCK_SIZE)/1048576) MB,count(*) Archives_Generated
from v$archived_log
group by trunc(COMPLETION_TIME,'DD'),thread# order by 1;
Longops related Query
col username format a15
set linesize 32767
col OPNAME format a40
col SOFAR format 999999999
col START_TIME format a20
select SID, username,OPNAME,SOFAR,TOTALWORK,SOFAR/TOTALWORK*100 "% of work done",to_char(START_TIME,'DD-MON-YY hh24:mi:ss') "START_TIME",
TIME_REMAINING,ELAPSED_SECONDS from v$session_longops where sofar!=TOTALWORK and TOTALWORK!=0
order by OPNAME desc;
Analyze how fast import is working
select substr(sql_text,instr(sql_text,'INTO "'),30) table_name,
rows_processed,
round((sysdate-to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60,1) minutes,
trunc(rows_processed/((sysdate-to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60)) rows_per_min
from sys.v_$sqlarea
where sql_text like 'INSERT %INTO "%'
and command_type = 2
and open_versions > 0;
To find out Expected Time of Completion for an Oracle Query
SELECT
opname,
target,
ROUND((sofar/totalwork),4)*100 Percentage_Complete,
start_time,
CEIL(time_remaining/60) Max_Time_Remaining_In_Min,
FLOOR(elapsed_seconds/60) Time_Spent_In_Min
FROM v$session_longops
WHERE sofar != totalwork;
SELECT SID, SERIAL#, opname, SOFAR, TOTALWORK,
ROUND(SOFAR/TOTALWORK*100,2) COMPLETE
FROM V$SESSION_LONGOPS
WHERE
TOTALWORK != 0
AND SOFAR != TOTALWORK
order by 1;
RMAN Backup related query
Set lines 300 pages 300
col START_TIME for a15
col COMPLETION_TIME for a15
col MESSAGE for a50
col STATUS for a10
col BACKUP_TYPE for a30
col BACKUP_METHOD for a30
select START_TIME,COMPLETION_TIME,MESSAGE,STATUS,BACKUP_TYPE,BACKUP_METHOD from RMAN.RMAN_BACKUP order by START_TIME asc;
Logfine Related
set linesize 300
col MEMBER format a90
col GROUP# format 99
select member,group#,status,type from v$logfile;
select GROUP#,members,status,bytes/1024/1024 "size-MB" from v$log order by group#;
Session Related
SQL> select * from user_resource_limits a where a.resource_name in ('IDLE_TIME','CONNECT_TIME');
RESOURCE_NAME LIMIT
-------------------------------- ----------------------------------------
IDLE_TIME UNLIMITED
CONNECT_TIME UNLIMITED
SQL> SELECT COUNT(*) FROM v$session;
COUNT(*)
----------
51
SQL> SELECT name, value FROM v$parameter WHERE name = 'sessions';
NAME
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
sessions
555
SQL> show parameter sessions
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
java_max_sessionspace_size integer 0
java_soft_sessionspace_limit integer 0
license_max_sessions integer 0
license_sessions_warning integer 0
sessions integer 555
shared_server_sessions integer
For single tablesapce with max size
select df.tablespace_name "Tablespace",
totalusedspace "Used MB",
(df.totalspace - tu.totalusedspace) "Free MB",
df.totalspace "Total MB",
round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace)) "Pct. Free"
from
(select tablespace_name,
SUM (DECODE (Autoextensible,'YES', maxbytes/ 1048576,'NO', BYTES/ 1048576)) TotalSpace
from dba_data_files
group by tablespace_name) df,
(select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_name
from dba_segments
group by tablespace_name) tu
where df.tablespace_name = tu.tablespace_name
and tu.tablespace_name='&tablespace_name';
Autoextensible file
col file_name for a65
select file_name,autoextensible,bytes/1024/1024 as "File Size",maxbytes/1024/1024 as "File Maxsize" from
dba_data_files where tablespace_name='&tbs_name';
Utilization of ASM diskgroup
select NAME,STATE,TOTAL_MB,FREE_MB, round ((FREE_MB/TOTAL_MB)*100,2) PCT_FREE from v$asm_diskgroup ;
select name,
round((total_mb-free_mb)/1024) USED_GB,
round(free_mb/1024) FREE_GB,
round(total_mb/1024) TOTAL_GB,
round(free_mb/total_mb*100) "FREE_%"
from v$asm_diskgroup where name like '%&name%' order by name;
for resize Tablespace
column value new_val blksize
select value
from v$parameter
where name = 'db_block_size'
/
select /*+ rule */ 'alter database datafile ''' ||
file_name || ''' resize ' ||
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 )
|| 'm;' cmd
from dba_data_files a,
( select file_id,
max(block_id+blocks-1) hwm
from dba_extents
group by file_id ) b
where a.tablespace_name='&TBSPACE' and a.file_id = b.file_id(+)
and
ceil(blocks*&&blksize/1024/1024)-
ceil((nvl(hwm,1)*
&&blksize)/1024/1024 ) > 0
/
All tablespace details including Temp Tablespace
set pages 999
col tablespace_name format a40
col "size MB" format 999,999,999
col "free MB" format 99,999,999
col "% Used" format 999
select tsu.tablespace_name, ceil(tsu.used_mb) "size MB"
, decode(ceil(tsf.free_mb), NULL,0,ceil(tsf.free_mb)) "free MB"
, decode(100 - ceil(tsf.free_mb/tsu.used_mb*100), NULL, 100,
100 - ceil(tsf.free_mb/tsu.used_mb*100)) "% used"
from (select tablespace_name, sum(bytes)/1024/1024 used_mb
from dba_data_files group by tablespace_name union all
select tablespace_name || ' **TEMP**'
, sum(bytes)/1024/1024 used_mb
from dba_temp_files group by tablespace_name) tsu
, (select tablespace_name, sum(bytes)/1024/1024 free_mb
from dba_free_space group by tablespace_name) tsf
where tsu.tablespace_name = tsf.tablespace_name (+)
order by 4
/
All tablespace details including Temp Tablespace
set pages 999
col tablespace_name format a40
col "size MB" format 999,999,999
col "free MB" format 99,999,999
col "% Used" format 999
select tsu.tablespace_name, ceil(tsu.used_mb) "size MB"
, decode(ceil(tsf.free_mb), NULL,0,ceil(tsf.free_mb)) "free MB"
, decode(100 - ceil(tsf.free_mb/tsu.used_mb*100), NULL, 100,
100 - ceil(tsf.free_mb/tsu.used_mb*100)) "% used"
from (select tablespace_name, sum(bytes)/1024/1024 used_mb
from dba_data_files group by tablespace_name union all
select tablespace_name || ' **TEMP**'
, sum(bytes)/1024/1024 used_mb
from dba_temp_files group by tablespace_name) tsu
, (select tablespace_name, sum(bytes)/1024/1024 free_mb
from dba_free_space group by tablespace_name) tsf
where tsu.tablespace_name = tsf.tablespace_name (+)
order by 4
/
Tablespaces that are >=80% full, and how much to add to make them 80% again
set pages 999 lines 100
col "Tablespace" for a50
col "Size MB" for 999999999
col "%Used" for 999
col "Add (80%)" for 999999
select tsu.tablespace_name "Tablespace"
, ceil(tsu.used_mb) "Size MB"
, 100 - floor(tsf.free_mb/tsu.used_mb*100) "%Used"
, ceil((tsu.used_mb - tsf.free_mb) / .8) - tsu.used_mb "Add (80%)"
from (select tablespace_name, sum(bytes)/1024/1024 used_mb
from dba_data_files group by tablespace_name) tsu
, (select ts.tablespace_name
, nvl(sum(bytes)/1024/1024, 0) free_mb
from dba_tablespaces ts, dba_free_space fs
where ts.tablespace_name = fs.tablespace_name (+)
group by ts.tablespace_name) tsf
where tsu.tablespace_name = tsf.tablespace_name (+)
and 100 - floor(tsf.free_mb/tsu.used_mb*100) >= 80
order by 3,4
/
TEMP tablespace related query
select (s.tot_used_blocks/f.total_blocks)*100 as "percent used"
from (select sum(used_blocks) tot_used_blocks from v$sort_segment where tablespace_name='TEMP') s,
(select sum(blocks) total_blocks from dba_temp_files where tablespace_name='TEMP') f;
select file_name,autoextensible,bytes/1024/1024 as "File Size",maxbytes/1024/1024 as "File Maxsize" from
dba_temp_files where tablespace_name='&tbs_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
/
Database start time etc
set line 500
col OPEN_MODE format a15
col LOG_MODE format a20
col CREATED format a30
col host_name format a15
col INSTANCE_NAME format a15
col DATABASE_ROLE format a18
col DB_NAME format a15
col startup_time format a20
col LOGINS format a15
select d.dbid,i.instance_name,d.name "DB_NAME",d.open_mode,d.log_mode,d.database_role,i.LOGINS,to_char(i.startup_time,'DD-MM-YY HH24:MI:SS') "STARTUP_TIME",host_name from v$database d, v$instance i;
set line 500
select d.dbid,i.instance_name,d.name "DB_NAME",d.open_mode,i.LOGINS,to_char(i.startup_time,'DD-MM-YY HH24:MI:SS') "STARTUP_TIME",host_name from v$database d, v$instance i;
srvctl status database -d Instance_name1
srvctl status database -d Instance name
select INSTANCE_NAME,HOST_NAME,to_char(STARTUP_TIME,'dd-mon-yy hh24:mi:ss') created,STATUS,LOGINS from v$instance;
select name,open_mode,LOG_MODE,to_char(created,'dd-mon-yy hh24:mi:ss') created from v$database;
Daily Use Important Command
Count Number of Dtabafile at filesystem
find oradata* -name '*.dbf' |wc -l
Crontab Entry
crontab -l
# * * * * * command to be executed
# | | | | |
# | | | | +--- day of week (0 - 6) (Sunday = 0)
# | | | +-------- month (1 - 12)
# | | +------------- day of month (1 - 31)
# | +------------------ hour (0 - 23)
# +----------------------- min (0 - 59)
Database Size
col "Database Size" format a20
col "Free space" format a20
col "Used space" format a20
select round(sum(used.bytes) / 1024 / 1024 / 1024 ) || ' GB' "Database Size"
, round(sum(used.bytes) / 1024 / 1024 / 1024 ) -
round(free.p / 1024 / 1024 / 1024) || ' GB' "Used space"
, 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
/
Find Number of database running on linux server -
ls -l $ORACLE_HOME/dbs/orapw*
Flashback related
SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
NO
SQL> alter database flashback on;
Database altered.
SQL> SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
YES
Query check fregmented size of schemas::
select owner,sum(round((blocks*16)/1024,2))"Fragmented size MB",sum(round((num_rows*avg_row_len/1024/1024),2))"Actual size MB",sum(round
2 (round((blocks*16)/1024,2)-round((num_rows*avg_row_len/1024/1024),2),2)) "Diff" from dba_tables where owner in('EPIMETA','EPIOP','EPIMART')
3 group by owner;
to check fregmented size of all schmas in user :
SQL> select owner,sum(round((blocks*16)/1024,2))"Fragmented size MB",sum(round((num_rows*avg_row_len/1024/1024),2))"Actual size MB",sum(round(round((blocks*16)/1024,2)-round((num_rows*avg_row_len/1024/1024),2),2)) "Diff" from dba_tables group by owner;
DBA grant to normal user
SQL> select * from v$pwfile_users;
USERNAME SYSDB SYSOP SYSAS
------------------------------ ----- ----- -----
SYS TRUE TRUE FALSE
RMAN TRUE FALSE FALSE
SQL> select name from v$database;
NAME
---------
XXXX
SQL> grant sysdba to USERNAME;
Grant succeeded.
SQL> select * from v$pwfile_users;
USERNAME SYSDB SYSOP SYSAS
------------------------------ ----- ----- -----
SYS TRUE TRUE FALSE
RMAN TRUE FALSE FALSE
USERNAME TRUE FALSE FALSE
Know the Growth trend of database
select to_char(CREATION_TIME,'RRRR') year, to_char(CREATION_TIME,'MM') month, round(sum(bytes)/1024/1024/1024) GB
from v$datafile
group by to_char(CREATION_TIME,'RRRR'), to_char(CREATION_TIME,'MM')
order by 1, 2;
High water mark related query
set lines 1000
col file_name for a65
col tablespace for a15
col actual for a20
select tablespace_name tablespace
, file_name
, decode(actual_size,-1,'n/a (offline)',actual_size) actual
, decode(shrink_to,-1,'n/a',null,'can''t be shrinked',actual_size,'can''t be shrinked', shrink_to) shrink_to
from (
select -- locally managed files that can be resized (last used block < last free block)
d.tablespace_name
, d.file_id
, d.file_name
, d.bytes/1024 actual_size --MB
, nvl((m.maxblock-1)*t.blocksize/1024, (64*1024+t.bitmapped*t.blocksize)/1024) shrink_to
from dba_data_files d
, sys.ts$ t
, (select u.fileid file_id, max(u.block+u.length) maxblock --last used block +1
from dba_lmt_used_extents u group by fileid ) m
where m.file_id(+)=d.file_id
and d.tablespace_name =t.name
and t.bitmapped > 0
and d.bytes is not null --online
union -- Offline LMT (Offline DMT info is still available in datadict.)
select d.tablespace_name, d.file_id, d.file_name, -1, -1 /* -1 => n/a, null => cannot be resized */
from dba_data_files d
where bytes is null -- Offline LMT: Size is unknown
union -- dictionary managed (online + offline)
select ts.name, a.file#, df.name, ts.blocksize*f.blocks/1024
, case when nvl(min(a.bl-1),f.blocks) > 2 --nvl: no free space
then nvl(min(a.bl-1),f.blocks)*(ts.blocksize)/1024
else 2*(ts.blocksize)/1024 end
from (select f.ts#, f.file#, block# bl
from sys.fet$ f
start with block#=(select d.blocks-f.length+1 m from sys.file$ d where f.file#=d.relfile#)
--end of free space area = end of datafile
connect by prior block#=length+block# --if area before is also free space
) a
, v$dbfile df
, sys.file$ f
, sys.ts$ ts
where f.relfile# = a.file# (+) -- a no rows -> no free space
and f.ts# = ts.ts#
and f.file# = df.file#
and ts.bitmapped=0 --0=dictionary managed, else=locally managed
group by ts.name, a.file#, df.name, f.blocks , ts.blocksize
)
order by tablespace_name, file_name
/
Index rebuilding dynamic query
SQL> select 'alter index '||owner||'.'||SEGMENT_NAME||' rebuild tablespace XXXX;' from dba_segments where SEGMENT_TYPE='INDEX' and owner='XX1' and bytes/1024/1024 > 1;
'ALTERINDEX'||OWNER||'.'||SEGMENT_NAME||'REBUILDTABLESPACEREDWOOD;'
--------------------------------------------------------------------------------------------------------------------------------------------------------
alter index SYSJCS.RWS_CCOL$_PK rebuild tablespace XXXX;
alter index SYSJCS.RWS_JOB$_PK rebuild tablespace XXXX;
alter index SYSJCS.RWS_JOB$_FK_FMT$_IDX rebuild tablespace XXXX;
alter index SYSJCS.RWS_JOB$_FK_OBJ#_IDX rebuild tablespace XXXX;
Archive Generation Details
This is used to check archive log generated in MB for database in last few days -
set pages 100
set pages 1000
select trunc(COMPLETION_TIME,'DD') Day, thread#, round(sum(BLOCKS*BLOCK_SIZE)/1048576) MB,count(*) Archives_Generated
from v$archived_log
group by trunc(COMPLETION_TIME,'DD'),thread# order by 1;
Longops related Query
col username format a15
set linesize 32767
col OPNAME format a40
col SOFAR format 999999999
col START_TIME format a20
select SID, username,OPNAME,SOFAR,TOTALWORK,SOFAR/TOTALWORK*100 "% of work done",to_char(START_TIME,'DD-MON-YY hh24:mi:ss') "START_TIME",
TIME_REMAINING,ELAPSED_SECONDS from v$session_longops where sofar!=TOTALWORK and TOTALWORK!=0
order by OPNAME desc;
Analyze how fast import is working
select substr(sql_text,instr(sql_text,'INTO "'),30) table_name,
rows_processed,
round((sysdate-to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60,1) minutes,
trunc(rows_processed/((sysdate-to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60)) rows_per_min
from sys.v_$sqlarea
where sql_text like 'INSERT %INTO "%'
and command_type = 2
and open_versions > 0;
To find out Expected Time of Completion for an Oracle Query
SELECT
opname,
target,
ROUND((sofar/totalwork),4)*100 Percentage_Complete,
start_time,
CEIL(time_remaining/60) Max_Time_Remaining_In_Min,
FLOOR(elapsed_seconds/60) Time_Spent_In_Min
FROM v$session_longops
WHERE sofar != totalwork;
SELECT SID, SERIAL#, opname, SOFAR, TOTALWORK,
ROUND(SOFAR/TOTALWORK*100,2) COMPLETE
FROM V$SESSION_LONGOPS
WHERE
TOTALWORK != 0
AND SOFAR != TOTALWORK
order by 1;
RMAN Backup related query
Set lines 300 pages 300
col START_TIME for a15
col COMPLETION_TIME for a15
col MESSAGE for a50
col STATUS for a10
col BACKUP_TYPE for a30
col BACKUP_METHOD for a30
select START_TIME,COMPLETION_TIME,MESSAGE,STATUS,BACKUP_TYPE,BACKUP_METHOD from RMAN.RMAN_BACKUP order by START_TIME asc;
FRA Utilization
select sum(PERCENT_SPACE_USED) from v$FLASH_RECOVERY_AREA_USAGE;
set lines 200
select * from v$FLASH_RECOVERY_AREA_USAGE;
select * from v$FLASH_RECOVERY_AREA_USAGE;
show parameter DB_RECOVERY_FILE_DEST
Utilization
Database Component related
set lines 300 pages 300
col COMP_NAME for a50
archive log list
select * from NLS_DATABASE_PARAMETERS;
sho parameter db_block_size
select COMP_NAME,VERSION,STATUS from dba_registry order by 1;
col COMP_NAME for a50
archive log list
select * from NLS_DATABASE_PARAMETERS;
sho parameter db_block_size
select COMP_NAME,VERSION,STATUS from dba_registry order by 1;
Logfine Related
set linesize 300
col MEMBER format a90
col GROUP# format 99
select member,group#,status,type from v$logfile;
select GROUP#,members,status,bytes/1024/1024 "size-MB" from v$log order by group#;
Session Related
SQL> select * from user_resource_limits a where a.resource_name in ('IDLE_TIME','CONNECT_TIME');
RESOURCE_NAME LIMIT
-------------------------------- ----------------------------------------
IDLE_TIME UNLIMITED
CONNECT_TIME UNLIMITED
SQL> SELECT COUNT(*) FROM v$session;
COUNT(*)
----------
51
SQL> SELECT name, value FROM v$parameter WHERE name = 'sessions';
NAME
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
sessions
555
SQL> show parameter sessions
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
java_max_sessionspace_size integer 0
java_soft_sessionspace_limit integer 0
license_max_sessions integer 0
license_sessions_warning integer 0
sessions integer 555
shared_server_sessions integer
