oracle 12c

— Schema_names, tables & Size MB
select owner, table_name, round((num_rows*avg_row_len)/(1024*1024)) MB
from all_tables
where owner not like ‘SYS%’ — Exclude system tables.
and num_rows > 0 — Ignore empty Tables.
order by MB desc — Biggest first.

–Schema_names, tables & Rows
select owner, table_name, num_rows
from all_tables
where owner not like ‘SYS%’ — Exclude system tables.
and num_rows > 0 — Ignore empty Tables.
order by num_rows desc — Biggest first.

–Lock statistics on those newly created tables so when stats run, these tables will be –skipped
–Also, you can lock stats on tables you want to avoid statistics to be gathered on
–when stats is not locked the value of stattype_locked is NULL
–shows when stats is locked the value of stattype_locked is ALL

–Syntax to lock statistics
SELECT stattype_locked FROM dba_tab_statistics WHERE table_name = ‘<table_name>’ and owner = ‘<SCHEMA_NAME>’;
exec dbms_stats.lock_table_stats(‘<SCHEMA_NAME>’,'<SCHEMA_NAME.TABLENAME>’);

##space issue

du -h .

sudo su – grid (for ASM)

asmcd lsdg

##View Free space from temporary tablespace

select substr(a.tablespace_name,1,20) “TS_name”, substr(a.file_name,1,35) “File_Name”,round(sum(a.bytes)/1024/1024,2) “Size_MB”,sum(NVL(b.bytes,0)) “FreeSpace” from dba_TEMP_files a, (select substr(tablespace_name,1,20) tablespace_name,file_id, round(sum(bytes_used)/1024/1024,2) used_sp, round(sum(blocks_free * 8192)/1024/1024,2) bytes from v$temp_space_header group by substr(tablespace_name,1,20),file_id ) b where a.tablespace_name=b.tablespace_name(+) and a.file_id=b.file_id(+) group by substr(a.tablespace_name,1,20), substr(a.file_name,1,35)


##syntax to get DB_SIZE

vi DB_SIZE.sql

COLUMN “Total Mb” FORMAT 999,999,999.0

COLUMN “Redo Mb” FORMAT 999,999,999.0

COLUMN “Temp Mb” FORMAT 999,999,999.0

COLUMN “Data Mb” FORMAT 999,999,999.0



Prompt “Database Size”


select (select sum(bytes/1048576) from dba_data_files) “Data Mb”,

(select NVL(sum(bytes/1048576),0) from dba_temp_files) “Temp Mb”,

(select sum(bytes/1048576)*max(members) from v$log) “Redo Mb”,

(select sum(bytes/1048576) from dba_data_files) +

(select NVL(sum(bytes/1048576),0) from dba_temp_files) +

(select sum(bytes/1048576)*max(members) from v$log) “Total Mb”

from dual;


select “Reserved_Space(MB)”, “Reserved_Space(MB)” – “Free_Space(MB)” “Used_Space(MB)”,”Free_Space(MB)” from (select (select sum(bytes/(1014*1024)) from dba_data_files) “Reserved_Space(MB)”, (select sum(bytes/(1024*1024)) from dba_free_space) “Free_Space(MB)”

from dual);


###syntax to get all tablespaces, datafiles, and autoextend mode from DB


select TABLESPACE_NAME “Tablspace”, FILE_NAME “Filename”, BYTES/1024/1024 “Size MB”, MAXBYTES/1024/1024 “Maximum Size MB”, AUTOEXTENSIBLE “Autoextensible” from SYS.DBA_DATA_FILES;



tempstring  varchar2(300);

task_id   varchar2(200);


tempstring := ‘select USERNAME, STATUS, MACHINE, SID from v$session where username = ”abesong”’;

task_id := dbms_sqltune.create_tuning_task(sql_text => tempstring, task_name=>’SQLTUNE2′);




SQL> set long 5000

SQL> select dbms_sqltune.report_tuning_task(‘SQLTUNE1’) from dual;

##Get DDL command from SQL query

select dbms_metadata.get_ddl(‘TABLE’,’table name’) FROM DUAL;

select dbms_metadata.get_ddl(‘TABLE’,’ORDR’) FROM DUA


##Monitor user session

set linesize 220

col first_load_time format a20

col client_info format a20

col username format a20

col last_load_time format a20

col sql_text format a30

select a.sid,a.serial#,a.client_info,a.username,a.logon_time,a.status,b.sql_text,a.last_call_et,b.first_load_time,b.last_load_time

from v$session a ,v$sql b

where a.sql_id=b.sql_id(+)

and a.sid=’&sid’


NB: You’ll be prompted to enter certan values like SID…

##Temporary tablespace usage for each user

COLUMN tablespace FORMAT A20

COLUMN temp_size FORMAT A20

COLUMN sid_serial FORMAT A20

COLUMN username FORMAT A20



SELECT b.tablespace,

ROUND(((b.blocks*p.value)/1024/1024),2)||’M’ AS temp_size,

a.sid||’,’||a.serial# AS sid_serial,

NVL(a.username, ‘(oracle)’) AS username,


FROM v$session a,

v$sort_usage b,

v$parameter p

WHERE p.name = ‘db_block_size’

AND a.saddr = b.session_addr

ORDER BY b.tablespace, b.blocks;



COLUMN file_name FORMAT A60


SELECT a.file_name,

ROUND(a.bytes/1024/1024) AS size_mb,

ROUND(a.maxbytes/1024/1024) AS maxsize_mb,

ROUND(b.free_bytes/1024/1024) AS free_mb,

ROUND((a.maxbytes-a.bytes)/1024/1024) AS growth_mb,

100 – ROUND(((b.free_bytes+a.growth)/a.maxbytes) * 100) AS pct_used

FROM   (SELECT file_name,



GREATEST(bytes,maxbytes) AS maxbytes,

GREATEST(bytes,maxbytes)-bytes AS growth

FROM   dba_data_files) a,

(SELeCT file_id,

SUM(bytes) AS free_bytes

FROM   dba_free_space

GROUP BY file_id) b

WHERE  a.file_id = b.file_id

ORDER BY file_name;


##Script to identify poor performant sql queries in Database


(SELECT sql_fulltext,  sql_id, elapsed_time, child_number, disk_reads, executions, first_load_time, last_load_time FROM    v$sql ORDER BY elapsed_time DESC)WHERE ROWNUM < 10


–This finds the top SQL statements that are currently stored in the SQL cache ordered by elapsed time. Statements will disappear from the cache over time, so it might be no good trying to diagnose last night’s batch job when you roll into work at midday.

You can also try ordering by disk_reads and executions. Executions is useful because some poor applications send the same SQL statement way too many times. This SQL assumes you use bind variables correctly.

Then, you can take the sql_id and child_number of a statement and feed them into this baby:-

SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR(‘&sql_id’, &child));

This shows the actual plan from the SQL cache and the full text of the SQL.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s