Oracle_Helpful_Sqls.sql

/************************************************
Tablespace Information                             
*************************************************/

--Show tablespace usage information 
col "Tablespace" for a40
col "Used MB" for 99,999,999
col "Free MB" for 99,999,999
col "Total MB" for 99,999,999
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,
          round(sum(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 ;

--Show temporary tablespace usage information
SELECT  tablespace_name,tablespace_size/1024/1024/1024, 
        allocated_space/1024/1024/1024, 
        free_space/1024/1024/1024
FROM    dba_temp_free_space;

--Add a data file to a temporary tablespace (change size etc if required)
alter tablespace TEMP_TABLESPACE_NAME add tempfile size 10G autoextend on maxsize unlimited;

--Add a data file to a normal tablespace
ALTER TABLESPACE "PRPRD_EDQ_RES" ADD DATAFILE '+DATAC2' SIZE 30720M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED

/************************************************
Monitoring                              
*************************************************/
-- Show the SQL for the given sql_id hash value:
select  * 
from    gv$sqltext 
where   sql_id = 'xxx' 
--where sql_hash = xxx
order by inst_id, piece;

--Show the SQL in one big line
select  * 
from    gv$sqlarea 
where   sql_id = 'xxx';

--Show session information for SQL
select  * 
from    gv$session where sql_id = 'xxx';

--Displays the history of the contents of the in-memory active session history of recent system activity
select  * 
from    dba_hist_active_sess_history;

--Displays historical information about SQL statistics
select * from dba_hist_sqlstat
/************************************************
Object Information                          
*************************************************/
--Show all tables in DB
select  * 
from    dba_tables;

--Show all table partitions
select  * 
from    dba_tab_partitions;

--Show all indexes
select  * 
from    dba_indexes;

--Show all index partitions
select  * 
from    dba_ind_partitions;

--Show the currently locked objects on the DB
select  *
from    gv$locked_object

/************************************************
Performance                         
*************************************************/
--Restore statistics to a point in time for a table (Doc ID 452011.1)
execute dbms_stats.restore_table_stats ('OWNER','TABLE_NAME','02-MAY-17 02.39.17.086519000 PM +10:00');