Pages

Thursday, March 24, 2016

Plan Hash Values for a Given SQLID Over Given Period & Historical Plans from the AWR

Plan Hash Values for a Given SQLID Over Given Period 
SET PAGESIZE 60
SET LINESIZE 300
SELECT DISTINCT sql_id, plan_hash_value
FROM dba_hist_sqlstat q,
    (
    SELECT /*+ NO_MERGE */ MIN(snap_id) min_snap, MAX(snap_id) max_snap
    FROM dba_hist_snapshot ss
    WHERE ss.begin_interval_time BETWEEN (SYSDATE - &No_Days) AND SYSDATE
    ) s
WHERE q.snap_id BETWEEN s.min_snap AND s.max_snap
  AND q.sql_id IN ( '&SQLID')
/

Get Historical Plans from the AWR Using SQLID
SET PAUSE ON
SET PAUSE 'Press Return to Continue'
SET PAGESIZE 60
SET LINESIZE 300
SELECT * FROM TABLE(dbms_xplan.display_awr('&SQL_ID'))
/

---Nikhil Tatineni—

Wednesday, March 23, 2016

ERROR OGG-00730 No minimum supplemental logging is enabled


 
Scenario where, changed primary keys for table / dropped supplemental logging & re-enabled supplemental logging with new keycolumns on table. Extract Abended with "ERROR   OGG-00730 "& report as follows 

2016-03-21 08:52:37  INFO    OGG-01517  Position of first record processed Sequence 15315, RBA 187571728, SCN 3378.225080410, Mar 21, 2016 6:37:42 AM.
Source Context :
  SourceModule            : [er.redo.ora.out]
  SourceID                : [/scratch/aime1/adestore/views/aime1_staxj04/oggcore/OpenSys/src/app/er/re
do/oracle/redooraout.c]
  SourceFunction          : [get_next_complete_record(mempool_t *, memtran_t *, ora_xid_t *, int32_t,
log_context_t *, chkpt_context_t *, BOOL, BOOL *, tran_item_hdr_t *, file_def **, row_info_t *, row_in
fo_t *, row_info_t *, BOOL *, BOOL *, BOOL *, char *)]
  SourceLine              : [2916]
2016-03-21 08:54:21  ERROR   OGG-00730  No minimum supplemental logging is enabled. This may cause extract process to handle key update incorrectly if key column is not in first row piece.

RootCause: 
checked for Minimal supplemental logging for database. But in my scenario, where Minimal Supplemental Logging is enabled for database.If it is NO, please Enable Minimal supplemental logging for database & start extract with Begin now :). Sync table after ..

SQL> SELECT SUPPLEMENTAL_LOG_DATA_MIN from v$database;
SUPPLEMENTAL_LOG_DATA_MIN
YES

From METALINK I FOUND FOLLOWING INFORMATION ( Doc ID 1571665.1 )

This is due to a regression bug( handled via Bug 16857778)
The ogg v11.2.1.0 4 and lower could process the same archive log without any issues.
This bug is fixed after v11.2.1.0.9, currently I am using OGG Version v11.2.1.0.6 

Use the undocumented parameter "TRANLOGOPTIONS DISABLESUPPLOGCHECK". Once it bypass the problematic archive log file remove the same.
Note:
We have seen the extract hanging in one case when using DISABLESUPPLOGCHECK and so please use it with caution.

Resolution: 
Depend on your scenario, work around 
1) alter extract with begin now 
                  (or) 
2) Add parameter ""TRANLOGOPTIONS DISABLESUPPLOGCHECK" to extract, restart the Extract :) 

--Nikhil Tatineni--
--GoldenGate 12c--

Tuesday, March 22, 2016

GoldenGate ERROR OGG-00665 OCI Error describe for query (status = 4031-ORA-04031


Problem Summary & Problem Description 
Developers started to  perform DML operation on Huge Volume tables, 
Oracle Goldengate Extract Abended with Error OGG-00665. 

Errors in Report File as follows
2016-03-21 09:44:31 ERROR OGG-00665 OCI Error describe for query (status = 4031-ORA-04031: unable to allocate 4000 bytes of shared memory ("shared pool","SELECT banner FROM v$version","sga heap(1,0)
","kglsim heap")), SQL<Not available>.
2016-03-21 09:44:31 ERROR OGG-01668 PROCESS ABENDING.

Resolution:
we increased memory structure "SGA" for database, issue is resolved

--Nikhil Tatineni--
--Oracle GoldenGate--

Procedure to flush SQL_ID from Shared Pool : Oracle 12c


In order to change the plan we have flush the current cursor in the library cache. 
We can find existing cursor information with the help of SQL_ID :) 

Steps as follows 
Find out sql_id of the existing sql in memory using v$sqlarea; 
Using v$sqlarea we can find out "sql_id" as follows 
select sql_id, force_matching_signature, sql_text from v$sqlarea where SQL_TEXT like '%Select max%';

using DBMS_XPLAN.DISPLAY_CURSOR we can find "sql_id" stored in memory 
SQL>select * from TABLE(dbms_xplan.display_cursor('SQL_ID'));

Flush the cache from memory and using dbms_shared_pool.purge 
SQL>exec dbms_shared_pool.purge('address','old_hash_value','C');

--Nikhil Tatineni--
--Oracle Database 12c --
12c: Trigger to open all pdbs after cdb reboot
http://oracle-in-memory.blogspot.com/2016/02/12c-trigger-to-open-all-pdbs-after-cdb.html
12c: Clone pluggable database using existing Pluggable database

http://oracle-in-memory.blogspot.com/2016/02/12c-clone-pluggable-databases-using.html
12c: open & close Pluggable databases

http://oracle-in-memory.blogspot.com/2016/02/12c-open-close-pluggable-databases.html

12c: Rename PDB’S from CDB Root 
http://oracle-in-memory.blogspot.com/2016/02/12c-rename-pdbs-from-cdb-root.html
12c: Creating Service on Pluggable Database using DBMS_SERVICE
http://oracle-in-memory.blogspot.com/2016/02/12c-creating-service-on-pdb.html

Monday, March 21, 2016

ORA-00119 & ORA-00132

Scenario: Bouncing of database failed with following errors 

[admind@tnc63 goldengate]$ . oraenv
ORACLE_SID = [admin] ? admin
The Oracle base remains unchanged with value /u01/app/admind
[admind@tnc63 goldengate]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Mon Mar 21 21:33:01 2016
Copyright (c) 1982, 2014, Oracle.  All rights reserved.
Connected to an idle instance.
SQL> startup;
ORA-00119: invalid specification for system parameter LOCAL_LISTENER
ORA-00132: syntax error or unresolved network name 'tnc63.sfdc.sbc.com:1521'
SQL> !

In order to start Database & to fix this issue 
1) commented out local_listener parameter in pfile i.e ORACLE_HOME/dbs location
2) rename server parameter file (spfile) 
3) start database using pfile from step 1

[admind@tnc63 dbs]$ vi initadmin.ora 
[admind@tnc63 dbs]$ cat initadmin.ora 
admin.__data_transfer_cache_size=0
admin.__db_cache_size=134217728
admin.__java_pool_size=16777216
admin.__large_pool_size=33554432
admin.__oracle_base='/u01/app/admind'#ORACLE_BASE set from environment
admin.__pga_aggregate_target=117440512
admin.__sga_target=989855744
admin.__shared_io_pool_size=0
admin.__shared_pool_size=251658240
admin.__streams_pool_size=0
*.audit_file_dest='/u01/app/admind/admin/admin/adump'
*.audit_trail='db'
*.compatible='12.1.0.2.0'
*.control_files='/u01/app/admind/oradata/ADMIN/controlfile/o1_mf_ccso8s0y_.ctl','/u01/app/admind/fast_recovery_area/ADMIN/controlfile/o1_mf_ccso8s4h_.ctl'
*.db_block_size=8192
*.db_create_file_dest='/u01/app/admind/oradata'
*.db_domain=''
*.db_name='admin'
*.db_recovery_file_dest='/u01/app/admind/fast_recovery_area'
*.db_recovery_file_dest_size=4560m
*.diagnostic_dest='/u01/app/admind'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=adminXDB)'
*.enable_pluggable_database=true
*.inmemory_size=524288000
###*.local_listener='tnc63.sfdc.sbc.com:1521'
*.memory_target=1048m
*.open_cursors=300
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
[admind@tnc63 dbs]$ 

listener file & tnsnames entry as follows

[admind@tnc63 admin]$ cat listener.ora 
# listener.ora Network Configuration File: /u01/app/admind/product/12.1.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

ADMIN =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = tnc63.sfdc.sbc.com)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )
SID_LIST_ADMIN =
  (SID_LIST =
    (SID_DESC =
      (ORACLE_HOME = /u01/app/admind/product/12.1.0/dbhome_1)
      (SID_NAME = admin)
    )
    (SID_DESC =
      (ORACLE_HOME = /u01/app/admind/product/12.1.0/dbhome_1)
      (SID_NAME = nspdba)
    )
  )

tnsnames.ora 

ADMIN =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = tnc63.sfdc.sbc.com)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = admin)
    )

  )

[admind@tnc63 admin]$ lsnrctl status
LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 21-MAR-2016 22:37:37
Copyright (c) 1991, 2014, Oracle.  All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                21-MAR-2016 21:30:07
Uptime                    0 days 1 hr. 7 min. 30 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/admind/product/12.1.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/admind/diag/tnslsnr/tnc63/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=tnc63.sfdc.sbc.com)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=tnc63.sfdc.sbc.com)(PORT=5500))(Security=(my_wallet_directory=/u01/app/admind/admin/admin/xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "admin" has 1 instance(s).
  Instance "admin", status READY, has 1 handler(s) for this service...
Service "adminXDB" has 1 instance(s).
  Instance "admin", status READY, has 1 handler(s) for this service...
Service "nspdba" has 1 instance(s).
  Instance "admin", status READY, has 1 handler(s) for this service...
The command completed successfully

#######

SQL> startup;
ORACLE instance started.
Total System Global Area 1107296256 bytes
Fixed Size     2923488 bytes
Variable Size   503317536 bytes
Database Buffers   50331648 bytes
Redo Buffers   13852672 bytes
In-Memory Area   536870912 bytes
Database mounted.
Database opened.


SQL> show con_name;
CON_NAME
------------------------------
CDB$ROOT

SQL> show parameter local_listener
NAME     TYPE VALUE
------------------------------------ ----------- ------------------------------
local_listener     string
SQL> alter system set local_listener='ADMIN' scope=both;

SQL> create pfile from spfile;
File created.

SQL> show parameter local_listener
NAME     TYPE VALUE
------------------------------------ ----------- ------------------------------
local_listener     string ADMIN

SQL> show parameter pfile;
NAME     TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile     string /u01/app/admind/product/12.1.0
/dbhome_1/dbs/spfileadmin.ora
SQL> 
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

setting local_listener parameter completed for CDB$ROOT database :) 

SQL> startup;
ORACLE instance started.
Total System Global Area 1107296256 bytes
Fixed Size     2923488 bytes
Variable Size   503317536 bytes
Database Buffers   50331648 bytes
Redo Buffers   13852672 bytes
In-Memory Area   536870912 bytes
Database mounted.
Database opened.
SQL> show parameter local_listener
NAME     TYPE VALUE
------------------------------------ ----------- ------------------------------
local_listener     string ADMIN


[admind@tnc63 admin]$ lsnrctl status
LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 21-MAR-2016 22:58:51
Copyright (c) 1991, 2014, Oracle.  All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                21-MAR-2016 21:30:07
Uptime                    0 days 1 hr. 28 min. 43 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/admind/product/12.1.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/admind/diag/tnslsnr/tnc63/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=tnc63.sfdc.sbc.com)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=tnc63.sfdc.sbc.com)(PORT=5500))(Security=(my_wallet_directory=/u01/app/admind/admin/admin/xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "admin" has 1 instance(s).
  Instance "admin", status READY, has 1 handler(s) for this service...
Service "adminXDB" has 1 instance(s).
  Instance "admin", status READY, has 1 handler(s) for this service...
Service "nspdba" has 1 instance(s).
  Instance "admin", status READY, has 1 handler(s) for this service...
The command completed successfully

--Nikhil Tatineni--
--Database Error--

Saturday, March 19, 2016

Oracle GoldenGate ERROR OGG-01668


Replicat Abended With following Error

2016-02-17 08:09:55  INFO    OGG-03035  Operating system character set identified as ISO-8859-1. Locale: en_US, LC_ALL:.

2016-02-17 08:09:55  INFO    OGG-02696  NON-ANSI SQL parameter syntax is used for parameter parsing.
replicat rotr131
sourcedefs dirdef/nsp-rwpk.def
Source Context :
  SourceModule            : [er.init]
  SourceID                : [/scratch/aime1/adestore/views/aime1_staxj04/oggcore/OpenSys/src/app/er/init.cpp]
  SourceFunction          : [get_infile_params(time_elt_def *, time_elt_def *, char **)]
  SourceLine              : [1596]
2016-02-17 08:09:55  ERROR   OGG-00303  Problem at line 103.  Bad column definition: PREV_VDSL_UP_RATE
  134     11      684  3  0 1 0      8      8ED            192     19       26  0  0 1 0     19     19     19 0 5
0 0 1    0 0 0.
2016-02-17 08:09:55  ERROR   OGG-01668  PROCESS ABENDING.

Root Cause: Definition file  which is used by replicat is corrupted 

Fix: Rename the existing definition file in ogghome/dirdef directory and re-create New defintion file from Source database using defgen utility & place it in GoldenGateHome/dirdef directory 

Regards,
Nikhil Tatineni


Knowledge On GoldenGate

Friday, March 18, 2016

Monitor and Manage Oracle Temporary Tablespace


#creating New Temporary Tablespace
SQL> create temporary tablespace temptbs tempfile '+NSP' size 200m;
Tablespace created

Assign Newly Created Temporary Tablespace to Database as follows
SQL> alter database default temporary tablespace temptbs;

Drop Old tablespace Temporary tablespace on database #
Use following Queries to monitor the size of temporary tablespace 

Adding additional space to temporary tablespace on database # 
SQL> alter tablespace temptbs add tempfile '+NSP' size 100m;

Checking usage of temporary tablespace  
 select  b.Total_MB,  
   b.Total_MB - round(a.used_blocks*8/1024) Current_Free_MB,  
   round(used_blocks*8/1024)        Current_Used_MB,  
   round(max_used_blocks*8/1024)       Max_used_MB  
 from  v$sort_segment a,  
   (select round(sum(bytes)/1024/1024) Total_MB from dba_temp_files )b;

Top Sessions with largest Temp Usage-
select * from (
select s.sid,
s.status,
s.sql_hash_value sesshash,
u.SQLHASH sorthash,
s.username,
u.tablespace,
sum(u.blocks*p.value/1024/1024) mbused ,
sum(u.extents) noexts,
nvl(s.module,s.program) proginfo,
floor(last_call_et/3600)||':'||
floor(mod(last_call_et,3600)/60)||':'||
mod(mod(last_call_et,3600),60) lastcallet
from v$sort_usage u,
v$session s,
v$parameter p
where u.session_addr = s.saddr
and p.name = 'db_block_size'
group by s.sid,s.status,s.sql_hash_value,u.sqlhash,s.username,u.tablespace,
nvl(s.module,s.program),
floor(last_call_et/3600)||':'||
floor(mod(last_call_et,3600)/60)||':'||
mod(mod(last_call_et,3600),60)
order by 7 desc,3)
where rownum < 11;

 Users / SIDs / which are performing sort Operations  
 SELECT  b.tablespace, b.segfile#, b.segblk#, b.blocks, a.sid, a.serial#,   
      a.username, a.osuser, a.status  
 FROM   v$session a,v$sort_usage b  
 WHERE  a.saddr = b.session_addr  
 ORDER BY b.tablespace, b.segfile#, b.segblk#, b.blocks;  

User/ Sql  Using Temp Segments  
 SELECT a.username, a.sid, a.serial#, a.osuser, b.tablespace, b.blocks, c.sql_text  
 FROM v$session a, v$tempseg_usage b, v$sqlarea c  
 WHERE a.saddr = b.session_addr  
 AND c.address= a.sql_address  
 AND c.hash_value = a.sql_hash_value  
 ORDER BY b.tablespace, b.blocks;  

--Nikhil Tatineni--
--Oracle Database --

Querys Used to Monitor Oracle Database, Tablespaces and datafiles

To check Tablespace free space:
SELECT TABLESPACE_NAME, SUM(BYTES/1024/1024) "Size (MB)"  FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME;


To check Tablespace by datafile:
SELECT tablespace_name, File_id, SUM(bytes/1024/1024)"Size (MB)" FROM DBA_FREE_SPACE
group by tablespace_name, file_id;


To Check Tablespace used and free 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;


Tablespace (File wise) 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 Growth rate of  Tablespace
Note: The script will not show the growth rate of the SYS, SYSAUX Tablespace. T
he script is used in Oracle version 10g onwards.


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;


List all Tablespaces with free space < 10% or full space> 90%
Select a.tablespace_name,sum(a.tots/1048576) Tot_Size,
sum(a.sumb/1024) Tot_Free, sum(a.sumb)*100/sum(a.tots) Pct_Free,
ceil((((sum(a.tots) * 15) - (sum(a.sumb)*100))/85 )/1048576) Min_Add
from (select tablespace_name,0 tots,sum(bytes) sumb
from dba_free_space a
group by tablespace_name
union
Select tablespace_name,sum(bytes) tots,0 from
dba_data_files
group by tablespace_name) a
group by a.tablespace_name
having sum(a.sumb)*100/sum(a.tots) < 10
order by pct_free;


Script to find all object Occupied space for a Tablespace
Select OWNER, SEGMENT_NAME, SUM(BYTES)/1024/1024 "SZIE IN MB" from dba_segments
where TABLESPACE_NAME = 'SDH_HRMS_DBF'
group by OWNER, SEGMENT_NAME;


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;


Sort (Temp) space used by Session
SELECT   S.sid || ',' || S.serial# sid_serial, S.username, S.osuser, P.spid, S.module, S.program, SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used,
T.tablespace, COUNT(*) sort_ops
FROM v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P
WHERE T.session_addr = S.saddr
AND S.paddr = P.addr AND T.tablespace = TBS.tablespace_name
GROUP BY S.sid, S.serial#, S.username, S.osuser, P.spid, S.module, S.program, TBS.block_size, T.tablespace ORDER BY sid_serial;


Sort (Temp) Space Usage by Statement
SELECT S.sid || ',' || S.serial# sid_serial, S.username, T.blocks * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,T.sqladdr address, Q.hash_value, Q.sql_text
FROM v$sort_usage T, v$session S, v$sqlarea Q, dba_tablespaces TBS
WHERE T.session_addr = S.saddr
AND T.sqladdr = Q.address (+) AND T.tablespace = TBS.tablespace_name
ORDER BY S.sid;


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;


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;


--Reference: http://shahiddba.blogspot.com/2012/05/script-to-monitor-tablespacesdatafiles.html