Tablespace usage
set pages 999
col tablespace_name format a40
col "size MB" format 999,999,999
col "free MB" format 99,999,999
col "% Used" format 999
select tsu.tablespace_name, ceil(tsu.used_mb) "size MB"
, decode(ceil(tsf.free_mb), NULL,0,ceil(tsf.free_mb)) "free MB"
, decode(100 - ceil(tsf.free_mb/tsu.used_mb*100), NULL, 100,
100 - ceil(tsf.free_mb/tsu.used_mb*100)) "% used"
from (select tablespace_name, sum(bytes)/1024/1024 used_mb
from dba_data_files group by tablespace_name union all
select tablespace_name || ' **TEMP**'
, sum(bytes)/1024/1024 used_mb
from dba_temp_files group by tablespace_name) tsu
, (select tablespace_name, sum(bytes)/1024/1024 free_mb
from dba_free_space group by tablespace_name) tsf
where tsu.tablespace_name = tsf.tablespace_name (+)
order by 4
/
Output:
TABLESPACE_NAME size MB free MB % used ---------------------------------------- ------------ ----------- ------ USERS_VIT 25 25 0 INDX 5 5 1 PERFSTAT 5 5 1 USERS 25 25 3 UNDOTBS2 2,000 1,886 5 APP_USER_DATA 10 10 6 ODM 10 10 6 TOOLS 600 509 15 DRSYS 20 15 25 WEB_DATA 100 72 28 CAP_DATA 3,500 2,316 33 CAP_INDEXES 4,500 2,705 39 SYSAUX 350 132 62 XDB 60 13 79 SYSTEM 650 84 87 ANDY 10 2 88 TEMP **TEMP** 1,050 0 100 TEMP_VIT **TEMP** 25 0 100 18 rows selected.
Show the files that comprise a tablespace
set lines 100 col file_name format a70 select file_name , ceil(bytes / 1024 / 1024) "size MB" from dba_data_files where tablespace_name like '&TSNAME' /
Tablespaces that are >=80% full, and how much to add to make them 80% again
set pages 999 lines 100 col "Tablespace" for a50 col "Size MB" for 999999999 col "%Used" for 999 col "Add (80%)" for 999999 select tsu.tablespace_name "Tablespace" , ceil(tsu.used_mb) "Size MB" , 100 - floor(tsf.free_mb/tsu.used_mb*100) "%Used" , ceil((tsu.used_mb - tsf.free_mb) / .8) - tsu.used_mb "Add (80%)" from (select tablespace_name, sum(bytes)/1024/1024 used_mb from dba_data_files group by tablespace_name) tsu , (select ts.tablespace_name , nvl(sum(bytes)/1024/1024, 0) free_mb from dba_tablespaces ts, dba_free_space fs where ts.tablespace_name = fs.tablespace_name (+) group by ts.tablespace_name) tsf where tsu.tablespace_name = tsf.tablespace_name (+) and 100 - floor(tsf.free_mb/tsu.used_mb*100) >= 80 order by 3,4 /
Output:
Tablespace Size MB %Used Add (80%)
-------------------------------------------------- ---------- ----- ---------
APPS_TS_NOLOGGING_AUTO 150 82 4
APPS_TS_SEED_AUTO 3072 85 185
SYSTEM 13312 88 1257
APPS_TS_INTERFACE_AUTO 3107 89 322
APPS_TS_ARCHIVE_AUTO 1822 94 306
APPS_TS_SUMMARY_AUTO 1667 95 311
APPS_TS_QUEUES_AUTO 1899 96 362
APPS_TS_MEDIA_AUTO 31614 96 6068
APPS_TS_TX_DATA_AUTO 171556 96 33019
MGIXX_CONV 35408 97 7329
MGIXX_IDX 272572 97 54635
APPS_TS_TX_IDX_AUTO 82982 98 18637
-------------------------------------------------- ---------- ----- ---------
APPS_TS_NOLOGGING_AUTO 150 82 4
APPS_TS_SEED_AUTO 3072 85 185
SYSTEM 13312 88 1257
APPS_TS_INTERFACE_AUTO 3107 89 322
APPS_TS_ARCHIVE_AUTO 1822 94 306
APPS_TS_SUMMARY_AUTO 1667 95 311
APPS_TS_QUEUES_AUTO 1899 96 362
APPS_TS_MEDIA_AUTO 31614 96 6068
APPS_TS_TX_DATA_AUTO 171556 96 33019
MGIXX_CONV 35408 97 7329
MGIXX_IDX 272572 97 54635
APPS_TS_TX_IDX_AUTO 82982 98 18637
12 rows selected.
User quotas on all tablespaces
col quota format a10
select username
, tablespace_name
, decode(max_bytes, -1, 'unlimited'
, ceil(max_bytes / 1024 / 1024) || 'M' ) "QUOTA"
from dba_ts_quotas
where tablespace_name not in ('TEMP')
/
Output:
USERNAME TABLESPACE_NAME QUOTA
-------------------- ---------------------------------------- ----------
AP APPS_TS_TX_IDX_AUTO unlimited
OE APPS_TS_TX_IDX_AUTO unlimited
EGO APPS_TS_TX_DATA_AUTO unlimited
AR APPS_TS_TX_IDX_AUTO unlimited
JTF APPS_TS_TX_IDX_AUTO unlimited
-------------------- ---------------------------------------- ----------
AP APPS_TS_TX_IDX_AUTO unlimited
OE APPS_TS_TX_IDX_AUTO unlimited
EGO APPS_TS_TX_DATA_AUTO unlimited
AR APPS_TS_TX_IDX_AUTO unlimited
JTF APPS_TS_TX_IDX_AUTO unlimited
List all objects in a tablespace
set pages 999
col owner format a15
col segment_name format a40
col segment_type format a20
select owner
, segment_name
, segment_type
from dba_segments
where lower(tablespace_name) like lower('%&tablespace%')
order by owner, segment_name
/
Output:
OWNER SEGMENT_NAME SEGMENT_TYPE --------------- ---------------------------------------- -------------------- ANDY BIN$Q+k4qsgBLNngRAAQg5ZRWg==$0 INDEX ANDY BIN$Q+k4qsgCLNngRAAQg5ZRWg==$0 TABLE ANDY BIN$Q+k4qsgELNngRAAQg5ZRWg==$0 INDEX ANDY BIN$Q+k4qsgFLNngRAAQg5ZRWg==$0 TABLE ANDY BOX TABLE ANDY BOX_PK INDEX ANDY CD TABLE ANDY CD_PK INDEX ANDY LOCATION TABLE ANDY LOCATION_PK INDEX ANDY LOTS_OF_ROWS TABLE ANDY POO TABLE ANDY SERVERS TABLE ANDY SYS_C0023466 INDEX ANDY SYS_C0023467 INDEX ANDY SYS_C0027234 INDEX ANDY TEST_TABLE TABLE ANDY TRACK TABLE ANDY TRACK_PK INDEX 19 rows selected.
Show all tablespaces used by a user
select tablespace_name , ceil(sum(bytes) / 1024 / 1024) "MB" from dba_extents where owner like '&user_id' group by tablespace_name order by tablespace_name /
Output:
TABLESPACE_NAME MB ---------------------------------------- ---------- ANDY_DATA 9 ANDY_INDEX 15 2 rows selected.
Create a temporary tablespace
create temporary tablespace temp tempfile '<file_name>' size 500M /
Alter a databases default temporary tablespace
alter database default temporary tablespace temp /
Show segments that are approaching max_extents
col segment_name format a40
select owner
, segment_type
, segment_name
, max_extents - extents as "spare"
, max_extents
from dba_segments
where owner not in ('SYS','SYSTEM')
and (max_extents - extents) < 10
order by 4
/
To change maxextents
alter <segment_type> <segment_name> storage(maxextents 150);
List the contents of the temporary tablespace(s)
set pages 999 lines 100 col username format a15 col mb format 999,999 select su.username , ses.sid , ses.serial# , su.tablespace , ceil((su.blocks * dt.block_size) / 1048576) MB from v$sort_usage su , dba_tablespaces dt , v$session ses where su.tablespace = dt.tablespace_name and su.session_addr = ses.saddr / Output:
USERNAME SID SERIAL# TABLESPACE MB --------------- ---------- ---------- ------------------------------- -------- REP_USER 117 1243 TEMP 25 APP_DATA_OWNER 81 32 TEMP 231 2 rows selected.
No comments:
Post a Comment