Storage

Space used by Single User

COLUMN GB FORMAT 9999.9999

COLUMN owner FORMAT A30

SELECT owner, SUM(bytes)/1024/1024/1024 GB

FROM DBA_SEGMENTS

WHERE owner = '&user'

GROUP BY owner;

Space used by All Users

COLUMN owner FORMAT A30

COLUMN GB FORMAT 99999.9999

SELECT owner, SUM(bytes)/1024/1024/1024 GB

FROM DBA_SEGMENTS

GROUP BY owner;

Space used per Tablespace

SET LINESIZE 132

BREAK ON TABLESPACE_NAME SKIP PAGE

COLUMN MB FORMAT 999,999,999

COLUMN Bytes FORMAT 999,999,999,999,999

COLUMN OWNER FORMAT A30

SELECT tablespace_name,

owner,

SUM(bytes) Bytes,

SUM(bytes/1024/1024) MB

FROM dba_segments

WHERE tablespace_name LIKE UPPER('&1')

GROUP BY tablespace_name,owner

ORDER BY tablespace_name,owner;

Space Used, Allocated and Available per Tablespace

SET LINESIZE 132

SET PAGESIZE 40

COLUMN GB_ALLOC FORMAT 9999.99

COLUMN GB_MAX FORMAT 9999.99

COLUMN GB_FREE FORMAT 9999.99

COLUMN PCT_ALLOC FORMAT 999.99

COLUMN GB_USED FORMAT 9999.99

SELECT i.instance_name INSTANCE,

ddf.tablespace_name,

COUNT(ddf.file_name) NUM_DATAFILES,

SUM(ddf.bytes)/1024/1024/1024 GB_ALLOC,

(COUNT(ddf.file_name)*34359738368)/1024/1024/1024 GB_MAX,

( ( COUNT(ddf.file_name)*34359738368 )/1024/1024/1024 - SUM(ddf.bytes)/1024/1024/1024 ) GB_FREE,

( ( SUM(ddf.bytes)/1024/1024/1024 )/( ( COUNT(ddf.file_name)*34359738368 ) /1024/1024/1024 ) )*100 PCT_ALLOC,

( SELECT SUM(ds1.bytes)

FROM DBA_SEGMENTS ds1

WHERE ds1.tablespace_name=ddf.tablespace_name ) /1024/1024/1024 GB_USED,

( ( ( SELECT SUM(ds1.bytes)

FROM DBA_SEGMENTS ds1

WHERE ds1.tablespace_name=ddf.tablespace_name ) /1024/1024/1024 ) /

( ( COUNT(ddf.file_name)*34359738368 ) /1024/1024/1024 ) ) *100 PCT_USED

FROM dba_data_files ddf, v$instance i

GROUP BY ddf.tablespace_name,i.instance_name

ORDER BY ddf.tablespace_name;

Space used per User per Tablespace

SET LINESIZE 132

BREAK ON TABLESPACE_NAME SKIP PAGE

COLUMN MB FORMAT 999,999,999

COLUMN Bytes FORMAT 999,999,999,999,999

COLUMN OWNER FORMAT A30

SELECT tablespace_name,

owner,

SUM(bytes) Bytes,

SUM(bytes/1024/1024) MB

FROM dba_segments

GROUP BY tablespace_name,owner

ORDER BY tablespace_name,owner;

Segments ordered by Size for a Tablespace

SET PAGESIZE 40

SET LINESIZE 180

COLUMN MB FORMAT 999,999,999

COLUMN Bytes FORMAT 999,999,999,999,999

COLUMN OWNER FORMAT A30

COLUMN SEGMENT_NAME FORMAT A30

SELECT tablespace_name,

owner,

segment_type,

segment_name,

SUM(bytes) "Bytes",

SUM(bytes)/1024/1024 MB

FROM dba_segments

WHERE tablespace_name = '&tablespace'

GROUP BY tablespace_name, owner, segment_type, segment_name

ORDER BY SUM(bytes);

CPU

Memory