SQL for Space usage for Tablespaces and Segments
SQL for Space usage for Tablespaces and Segments
================================
SQLs
================================
View per Tablespace. Free space, Used space, Total space.
SET LINESIZE 120
SET PAGESIZE 200
SELECT TABLESPACE_NAME,
(MAX(MAX_SPACE)-MAX(USED_SPACE) )AS DBA_FREE_SPACE_MB,
MAX(USED_SPACE) AS USED_SPACE_MB,
MAX(MAX_SPACE) AS MAX_SPACE_MB,
ROUND(( (MAX(MAX_SPACE)-MAX(USED_SPACE) ) /MAX(MAX_SPACE) ) *100) as FREE_PCT,
CASE WHEN (ROUND(( (MAX(MAX_SPACE)-MAX(USED_SPACE) ) /MAX(MAX_SPACE) ) *100)<15) THEN 'Y' ELSE 'N' END AS ADD_MORE_SPACE
FROM (
SELECT tablespace_name,
ROUND(SUM(bytes)/1024/2014) AS FREE_SPACE,
0 AS MAX_SPACE,
0 AS USED_SPACE
FROM DBA_FREE_SPACE
WHERE tablespace_name LIKE '%IGT%' OR tablespace_name LIKE '%DWH%' OR tablespace_name LIKE '%REPORTS%' OR tablespace_name = 'SYSTEM'
GROUP BY tablespace_name
UNION ALL
SELECT tablespace_name,
0 AS FREE_SPACE,
ROUND( SUM(CASE WHEN (bytes>maxbytes) THEN bytes ELSE maxbytes END)/1024/1024) AS MAX_SPACE,
0 AS USED_SPACE
FROM DBA_DATA_FILES
WHERE tablespace_name LIKE '%IGT%' OR tablespace_name LIKE '%DWH%' OR tablespace_name LIKE '%REPORTS%' OR tablespace_name = 'SYSTEM'
GROUP BY tablespace_name
UNION ALL
SELECT tablespace_name,
0 AS FREE_SPACE,
0 AS MAX_SPACE,
ROUND(SUM(bytes/1024/1024)) AS USED_SPACE
FROM DBA_SEGMENTS
WHERE tablespace_name LIKE '%IGT%' OR tablespace_name LIKE '%DWH%' OR tablespace_name LIKE '%REPORTS%' OR tablespace_name = 'SYSTEM'
GROUP BY tablespace_name
)
GROUP BY tablespace_name;
TABLESPACE_NAME DBA_FREE_SPACE_MB USED_SPACE_MB MAX_SPACE_MB FREE_PCT ADD_SPACE
-------------------- ----------------- ------------- ------------ ---------- ---------
DWH_INDEX 179149 96851 276000 65 N
IGT_TABLE 144946 5054 150000 97 N
SYSTEM 1691 309 2000 85 N
IGT_INDEX 89590 410 90000 100 N
DWH_TABLE 519 101481 102000 1 Y
SQLs
================================
View per Tablespace. Free space, Used space, Total space.
SET LINESIZE 120
SET PAGESIZE 200
SELECT TABLESPACE_NAME,
(MAX(MAX_SPACE)-MAX(USED_SPACE) )AS DBA_FREE_SPACE_MB,
MAX(USED_SPACE) AS USED_SPACE_MB,
MAX(MAX_SPACE) AS MAX_SPACE_MB,
ROUND(( (MAX(MAX_SPACE)-MAX(USED_SPACE) ) /MAX(MAX_SPACE) ) *100) as FREE_PCT,
CASE WHEN (ROUND(( (MAX(MAX_SPACE)-MAX(USED_SPACE) ) /MAX(MAX_SPACE) ) *100)<15) THEN 'Y' ELSE 'N' END AS ADD_MORE_SPACE
FROM (
SELECT tablespace_name,
ROUND(SUM(bytes)/1024/2014) AS FREE_SPACE,
0 AS MAX_SPACE,
0 AS USED_SPACE
FROM DBA_FREE_SPACE
WHERE tablespace_name LIKE '%IGT%' OR tablespace_name LIKE '%DWH%' OR tablespace_name LIKE '%REPORTS%' OR tablespace_name = 'SYSTEM'
GROUP BY tablespace_name
UNION ALL
SELECT tablespace_name,
0 AS FREE_SPACE,
ROUND( SUM(CASE WHEN (bytes>maxbytes) THEN bytes ELSE maxbytes END)/1024/1024) AS MAX_SPACE,
0 AS USED_SPACE
FROM DBA_DATA_FILES
WHERE tablespace_name LIKE '%IGT%' OR tablespace_name LIKE '%DWH%' OR tablespace_name LIKE '%REPORTS%' OR tablespace_name = 'SYSTEM'
GROUP BY tablespace_name
UNION ALL
SELECT tablespace_name,
0 AS FREE_SPACE,
0 AS MAX_SPACE,
ROUND(SUM(bytes/1024/1024)) AS USED_SPACE
FROM DBA_SEGMENTS
WHERE tablespace_name LIKE '%IGT%' OR tablespace_name LIKE '%DWH%' OR tablespace_name LIKE '%REPORTS%' OR tablespace_name = 'SYSTEM'
GROUP BY tablespace_name
)
GROUP BY tablespace_name;
TABLESPACE_NAME DBA_FREE_SPACE_MB USED_SPACE_MB MAX_SPACE_MB FREE_PCT ADD_SPACE
-------------------- ----------------- ------------- ------------ ---------- ---------
DWH_INDEX 179149 96851 276000 65 N
IGT_TABLE 144946 5054 150000 97 N
SYSTEM 1691 309 2000 85 N
IGT_INDEX 89590 410 90000 100 N
DWH_TABLE 519 101481 102000 1 Y
View Segments per Tablespace.
SET LINESIZE 120
SET PAGESIZE 200
COL tablespace_name FOR A30
COL owner FOR A30
COL segment_name FOR A30
COL USED_MB FOR 9999999999999
SELECT owner, tablespace_name, segment_name, USED_MB
FROM (
SELECT owner, tablespace_name, segment_name, ROUND(SUM(bytes)/1024/1024)AS USED_MB FROM DBA_SEGMENTS
WHERE tablespace_name LIKE '%TABLE%'
GROUP BY owner, tablespace_name,segment_name
ORDER BY ROUND(SUM(bytes)/1024/1024) DESC
) WHERE ROWNUM < 11;
SET PAGESIZE 200
COL tablespace_name FOR A30
COL owner FOR A30
COL segment_name FOR A30
COL USED_MB FOR 9999999999999
SELECT owner, tablespace_name, segment_name, USED_MB
FROM (
SELECT owner, tablespace_name, segment_name, ROUND(SUM(bytes)/1024/1024)AS USED_MB FROM DBA_SEGMENTS
WHERE tablespace_name LIKE '%TABLE%'
GROUP BY owner, tablespace_name,segment_name
ORDER BY ROUND(SUM(bytes)/1024/1024) DESC
) WHERE ROWNUM < 11;
OWNER TABLESPACE_NAME SEGMENT_NAME USED_MB
------------- -------------------- ------------------------------ --------------
USER_A DWH_TABLE FACT_ROAMER_CAMPAIGNS 71428
USER_C DWH_TABLE FACT_ROAMER_SCENARIO 12890
USER_A DWH_TABLE FACT_ROAMER_CAMPAIGNS 9872
USER_C DWH_TABLE FACT_ROAMER_CAMPAIGNS 2764
USER_A IGT_TABLE SFI_CUSTOMER_PROFILE 2624
USER_E DWH_TABLE FACT_ROAMER_SCENARIO 1880
USER_B IGT_TABLE SFI_CUSTOMER_PROFILE_PK 1849
USER_A DWH_TABLE FACT_ROAMER_SCENARIO 792
USER_A DWH_TABLE SFI_CUSTOMER_OPTIONS 768
USER_A IGT_TABLE SFI_CUSTOMER_OPTION_20131010 363
10 rows selected.
View Datafiles Usage.
COL FILE_NAME FOR A50
SELECT FILE_NAME, TABLESPACE_NAME, ROUND(BYTES/1024/1024) AS Mb, ROUND(MAXBYTES/1024/1024) AS Max_Mb , ROUND(USER_BYTES/1024/1024) USER_Mb
FROM DBA_DATA_FILES
FILE_NAME TABLESPACE_NAME MB MAX_MB USER_MB
------------------------------------------- ---------------- -------- -------- --------
/oracle_db/db1/db_igt/ora_dwh_table_01.dbf DWH_TABLE 12000 12000 11999
/oracle_db/db1/db_igt/ora_dwh_table_02.dbf DWH_TABLE 30000 30000 29999
/oracle_db/db1/db_igt/ora_dwh_table_03.dbf DWH_TABLE 30000 30000 29999
/oracle_db/db1/db_igt/ora_dwh_table_04.dbf DWH_TABLE 30000 30000 29999
ALTER DATABASE DATAFILE '/oracle_db/db1/db_igt/ora_dwh_table_01.dbf' RESIZE 30000M;
View Tempfiles Usage.
SET LINESIZE 120
SET PAGESIZE 120
COL file_name FOR A50
SELECT file_name, ROUND(BYTES/1024/1024) AS mb, ROUND(MAXBYTES/1024/1024) AS max_mb, ROUND(USER_BYTES/1024/1024) AS user_mb
FROM DBA_TEMP_FILES;
FILE_NAME MB MAX_MB USER_MB
-------------------------------------------------- ---------- ---------- ----------
/oracle_db/db1/db_igt/ora_temporary_01.dbf 30000 20000 29999
/oracle_db/db1/db_igt/ora_temporary_02.dbf 1024 10240 1023
================================
Solutions
================================
------------- -------------------- ------------------------------ --------------
USER_A DWH_TABLE FACT_ROAMER_CAMPAIGNS 71428
USER_C DWH_TABLE FACT_ROAMER_SCENARIO 12890
USER_A DWH_TABLE FACT_ROAMER_CAMPAIGNS 9872
USER_C DWH_TABLE FACT_ROAMER_CAMPAIGNS 2764
USER_A IGT_TABLE SFI_CUSTOMER_PROFILE 2624
USER_E DWH_TABLE FACT_ROAMER_SCENARIO 1880
USER_B IGT_TABLE SFI_CUSTOMER_PROFILE_PK 1849
USER_A DWH_TABLE FACT_ROAMER_SCENARIO 792
USER_A DWH_TABLE SFI_CUSTOMER_OPTIONS 768
USER_A IGT_TABLE SFI_CUSTOMER_OPTION_20131010 363
10 rows selected.
View Datafiles Usage.
COL FILE_NAME FOR A50
SELECT FILE_NAME, TABLESPACE_NAME, ROUND(BYTES/1024/1024) AS Mb, ROUND(MAXBYTES/1024/1024) AS Max_Mb , ROUND(USER_BYTES/1024/1024) USER_Mb
FROM DBA_DATA_FILES
FILE_NAME TABLESPACE_NAME MB MAX_MB USER_MB
------------------------------------------- ---------------- -------- -------- --------
/oracle_db/db1/db_igt/ora_dwh_table_01.dbf DWH_TABLE 12000 12000 11999
/oracle_db/db1/db_igt/ora_dwh_table_02.dbf DWH_TABLE 30000 30000 29999
/oracle_db/db1/db_igt/ora_dwh_table_03.dbf DWH_TABLE 30000 30000 29999
/oracle_db/db1/db_igt/ora_dwh_table_04.dbf DWH_TABLE 30000 30000 29999
ALTER DATABASE DATAFILE '/oracle_db/db1/db_igt/ora_dwh_table_01.dbf' RESIZE 30000M;
View Tempfiles Usage.
SET LINESIZE 120
SET PAGESIZE 120
COL file_name FOR A50
SELECT file_name, ROUND(BYTES/1024/1024) AS mb, ROUND(MAXBYTES/1024/1024) AS max_mb, ROUND(USER_BYTES/1024/1024) AS user_mb
FROM DBA_TEMP_FILES;
FILE_NAME MB MAX_MB USER_MB
-------------------------------------------------- ---------- ---------- ----------
/oracle_db/db1/db_igt/ora_temporary_01.dbf 30000 20000 29999
/oracle_db/db1/db_igt/ora_temporary_02.dbf 1024 10240 1023
================================
Solutions
================================
1. Increase Tablespace
A. Resize Datafile
ALTER DATABASE DATAFILE '/oracle_db/db1/db_igt/ora_table_01.dbf' RESIZE 30720M;
B. Add Datafile
ALTER TABLESPACE TABLE_IGT ADD DATAFILE '/oracle_db/db1/db_igt/ora_table_02.dbf' SIZE 1024M AUTOEXTEND ON MAXSIZE 10240M;
2. Increase TEMP Tablespace
A. Resize Datafile
--Resize to 30Gb
ALTER DATABASE TEMPFILE '/oracle_db/db1/db_igt/ora_temporary_01.dbf' RESIZE 30720M;
B. Add Datafile
--Add 1Gb
ALTER TABLESPACE TEMPORARY ADD TEMPFILE '/oracle_db/db1/db_igt/ora_temporary_02.dbf' SIZE 1024M AUTOEXTEND ON MAXSIZE 10240M;
3. Add Tablespace
CREATE TABLESPACE IGT_TABLE_TEMP DATAFILE '/oracle_db/db2/db_igt/datafiles/ora_igt_table_temp_01.dbf' SIZE 20M AUTOEXTEND ON MAXSIZE 30000M EXTENT MANAGEMENT LOCAL;
3. Add Tablespace
CREATE TABLESPACE IGT_TABLE_TEMP DATAFILE '/oracle_db/db2/db_igt/datafiles/ora_igt_table_temp_01.dbf' SIZE 20M AUTOEXTEND ON MAXSIZE 30000M EXTENT MANAGEMENT LOCAL;
Comments
Post a Comment