TABLESPACE And Temporary Tablespace Analyse

select file_name,bytes/1024/1024/1024 from dba_data_files where TABLESPACE_NAME='APPS_TS_TX_DATA';

--ALTER DATABASE DATAFILE '/kzn01/TEST/db/apps_st/data/a_txn_data06.dbf' RESIZE 2G;

----------------------------------------
select * from dba_data_files where TABLESPACE_NAME='APPS_TS_TX_IDX';

ALTER TABLESPACE APPS_TS_TX_IDX ADD DATAFILE '/kzn03/Oracle/PROD/db/apps_st/data/a_txn_ind07.dbf' SIZE 2G;

---------------------------------------
list tablespaces, size, free space, and percent free
 
SELECT df.tablespace_name TABLESPACE, 
df.total_space_mb TOTAL_SPACE_GB,
(df.total_space_mb - fs.free_space_mb) USED_SPACE_GB, fs.free_space_mb FREE_SPACE_GB,
ROUND(100 * (fs.free_space / df.total_space),2) PCT_FREE
FROM (SELECT tablespace_name, SUM(bytes) TOTAL_SPACE,TRUNC((SUM(bytes) / 1048576/1024),2) TOTAL_SPACE_MB
FROM dba_data_files GROUP BY tablespace_name) df,
(SELECT tablespace_name, SUM(bytes) FREE_SPACE,TRUNC((SUM(bytes) / 1048576/1024),2) FREE_SPACE_MB
FROM dba_free_space GROUP BY tablespace_name) fs
WHERE df.tablespace_name = fs.tablespace_name(+) ORDER BY fs.tablespace_name;



--------------------------------------------------
Full database size

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;

============================================================================

Master Note: Overview of Oracle Temporary Tablespaces (Doc ID 1498442.1)

SELECT PROPERTY_NAME, PROPERTY_VALUE
FROM database_properties
WHERE property_name='DEFAULT_TEMP_TABLESPACE';

SELECT group_name, tablespace_name FROM dba_tablespace_groups;
SELECT * FROM dba_tablespace_groups;

SELECT PROPERTY_NAME, PROPERTY_VALUE
FROM database_properties
WHERE property_name='DEFAULT_TEMP_TABLESPACE';


SELECT * FROM dba_tablespace_groups;

SELECT TABLESPACE_NAME,free_space/1024/1024/1024 from DBA_TEMP_FREE_SPACE;

SELECT * from DBA_TEMP_FREE_SPACE;

SELECT file#, name, bytes/1024/1024 mb FROM v$tempfile;

SELECT * FROM v$tempfile;

 
 
-- /u03/CRP1GST/db/apps_st/data/temp02.dbf
 
--ALTER DATABASE TEMPFILE '/u01/UAT/db/apps_st/data/temp03.dbf' RESIZE 2G;
 --ALTER TABLESPACE TEMP2 ADD TEMPFILE '/u01/app/oracle/tempfile/tempo3.dbf' size 1000M reuse;
 commit;
 

TBALESPACE CHECK

 
 SELECT df.tablespace_name TABLESPACE, 
df.total_space_mb TOTAL_SPACE_GB,
(df.total_space_mb - fs.free_space_mb) USED_SPACE_GB, fs.free_space_mb FREE_SPACE_GB,
ROUND(100 * (fs.free_space / df.total_space),2) PCT_FREE
FROM (SELECT tablespace_name, SUM(bytes) TOTAL_SPACE,TRUNC((SUM(bytes) / 1048576/1024),2) TOTAL_SPACE_MB
FROM dba_data_files GROUP BY tablespace_name) df,
(SELECT tablespace_name, SUM(bytes) FREE_SPACE,TRUNC((SUM(bytes) / 1048576/1024),2) FREE_SPACE_MB
FROM dba_free_space GROUP BY tablespace_name) fs
WHERE df.tablespace_name = fs.tablespace_name(+) ORDER BY fs.tablespace_name;