Oracle Tablespace per User report

set termout off
set newpage 0
set pagesize 60
set linesize 75
set feedback off
set trimout on
set verify on
set wrap on
set termout off
set echo off

spool tssumuser.rslt
ttitle center 'TSSUSER - Database Tablespace per User Summary' skip 1 -
left "PAGE:" sql.pno format 999 right CWS_DATE skip 2

col username format a20 heading 'Username' justify c
col tablespace_name format a20 heading 'Tablespace Name' justify c
col mquota format a12 heading 'MB Quota' justify c
col mbytes format 999,999,990.99 heading 'MB Used' justify c

break on username

select
username,
tablespace_name,
decode(greatest(max_bytes, -1), -1, 'Unrestricted',
to_char(max_bytes/1048576, '999,999,990')) mquota,
bytes/1048576 mbytes
from
sys.dba_ts_quotas
where
(max_bytes != 0) or
(bytes != 0)
order by
1,3;

clear col
clear bre



TSSUSER - Database Tablespace per User Summary
PAGE: 1 CWS_DATE

Username Tablespace Name MB Quota MB Used
-------------------- -------------------- ------------ ---------------
ANUJTEST ANUJTEST Unrestricted 7.00
APPQOSSYS SYSAUX Unrestricted 0.00
FLOWS_FILES SYSAUX Unrestricted 0.00
OLAPSYS SYSAUX Unrestricted 7.31
PERFSTAT PERFSTAT Unrestricted 114.00
RMAN RMAN Unrestricted 5.94
RRR USERS Unrestricted 0.00

Oracle User Info

User Quick info



SET VERIFY OFF FEEDBACK OFF PAGES 10000 LIN 80 RECSEP OFF ECHO OFF ARRAY 5
CL COL
SPOOL output.lst
COL username NOPRINT
COL external_name NOPRINT
COL default_tablespace FOR A16 HEADING "Default TS"
COL temporary_tablespace FOR A16 HEADING "Temporary TS"
COL account_status FOR A16
COL password FOR A16
COL profile FOR A16
COL grantee FOR A16 NOPRINT
COL owner FOR A16
COL table_name FOR A16
COL grantor FOR A16
PROMPT
PROMPT ============================= DBA_USERS ===============================
SELECT *
FROM dba_users WHERE username = UPPER('&&1')
/
PROMPT
PROMPT ============================= Granted roles ===========================
SELECT * FROM dba_role_privs WHERE grantee = UPPER('&&1')
/
PROMPT
PROMPT ============================= Granted system privileges ===============
SELECT * FROM dba_sys_privs WHERE grantee = UPPER('&&1')
/
PROMPT
PROMPT ============================= Granted object privileges ===============
COL privilege FOR A20 WORD_WRAPPED
SELECT * FROM dba_tab_privs WHERE grantee = UPPER('&&1')
/
PROMPT
PROMPT ============================= Granted column privileges ===============
SELECT *
FROM dba_col_privs WHERE grantee = UPPER('&&1')
/
PROMPT
PROMPT ============================= Tablespace quotas =======================
SELECT tablespace_name,
DECODE(max_bytes ,-1,'UNLIMITED',TO_CHAR(max_bytes/1048576,9999.9 )||' Mb') "Quota"
FROM dba_ts_quotas
WHERE username = UPPER('&&1')
/
PROMPT
PROMPT ============================= Database objects ========================
SELECT object_type, COUNT(*) FROM dba_objects WHERE owner = UPPER('&&1')
GROUP BY object_type
/
PROMPT
PROMPT ============================= End of report ===========================
SPOOL OFF
UNDEFINE USER
ed output.lst
PROMPT Output is spooled in output.lst
SET FEEDBACK ON RECSEP WR
SET PAGES 24
CL COL

Oracle fragmented tables on schema

Fragmentation report
fragmented tables




col TABLE_NAME format a25
SELECT
OWNER,
SEGMENT_NAME TABLE_NAME,
SEGMENT_TYPE,
GREATEST(ROUND(100 * (NVL(HWM - AVG_USED_BLOCKS,0)/GREATEST(NVL(HWM,1),1) ), 2), 0) WASTE_PER,
ROUND(BYTES/1024, 2) TABLE_KB,
NUM_ROWS,
BLOCKS,
EMPTY_BLOCKS,
HWM HIGHWATER_MARK,
AVG_USED_BLOCKS,
CHAIN_PER,
EXTENTS,
MAX_EXTENTS,
ALLO_EXTENT_PER,
DECODE(GREATEST(MAX_FREE_SPACE - NEXT_EXTENT, 0), 0,'N','Y') CAN_EXTEND_SPACE,
NEXT_EXTENT, MAX_FREE_SPACE,
O_TABLESPACE_NAME TABLESPACE_NAME
FROM
(
SELECT
A.OWNER OWNER,
A.SEGMENT_NAME,
A.SEGMENT_TYPE,
A.BYTES,
B.NUM_ROWS,
A.BLOCKS BLOCKS,
B.EMPTY_BLOCKS EMPTY_BLOCKS,
A.BLOCKS - B.EMPTY_BLOCKS - 1 HWM,
DECODE( ROUND((B.AVG_ROW_LEN * NUM_ROWS * (1 + (PCT_FREE/100)))/C.BLOCKSIZE, 0),
0, 1 ,ROUND((B.AVG_ROW_LEN * NUM_ROWS * (1 + (PCT_FREE/100)))/C.BLOCKSIZE, 0)) + 2 AVG_USED_BLOCKS,
ROUND(100 * (NVL(B.CHAIN_CNT, 0)/GREATEST(NVL(B.NUM_ROWS, 1), 1)), 2) CHAIN_PER,
ROUND(100 * (A.EXTENTS/A.MAX_EXTENTS), 2) ALLO_EXTENT_PER,
A.EXTENTS EXTENTS,
A.MAX_EXTENTS MAX_EXTENTS,
B.NEXT_EXTENT NEXT_EXTENT,
B.TABLESPACE_NAME O_TABLESPACE_NAME
FROM
SYS.DBA_SEGMENTS A,
SYS.DBA_TABLES B,
SYS.TS$ C
WHERE A.OWNER =B.OWNER
AND SEGMENT_NAME = TABLE_NAME
AND SEGMENT_TYPE = 'TABLE'
AND B.TABLESPACE_NAME = C.NAME
UNION ALL
SELECT
A.OWNER OWNER,
SEGMENT_NAME || '.' || B.PARTITION_NAME,
SEGMENT_TYPE,
BYTES,
B.NUM_ROWS,
A.BLOCKS BLOCKS,
B.EMPTY_BLOCKS EMPTY_BLOCKS,
A.BLOCKS - B.EMPTY_BLOCKS - 1 HWM,
DECODE( ROUND((B.AVG_ROW_LEN * B.NUM_ROWS * (1 + (B.PCT_FREE/100)))/C.BLOCKSIZE, 0),
0, 1,ROUND((B.AVG_ROW_LEN * B.NUM_ROWS * (1 + (B.PCT_FREE/100)))/C.BLOCKSIZE, 0)) + 2 AVG_USED_BLOCKS,
ROUND(100 * (NVL(B.CHAIN_CNT,0)/GREATEST(NVL(B.NUM_ROWS, 1), 1)), 2) CHAIN_PER,
ROUND(100 * (A.EXTENTS/A.MAX_EXTENTS), 2) ALLO_EXTENT_PER,
A.EXTENTS EXTENTS,
A.MAX_EXTENTS MAX_EXTENTS,
B.NEXT_EXTENT,
B.TABLESPACE_NAME O_TABLESPACE_NAME
FROM
SYS.DBA_SEGMENTS A,
SYS.DBA_TAB_PARTITIONS B,
SYS.TS$ C,
SYS.DBA_TABLES D
WHERE
A.OWNER = B.TABLE_OWNER
AND SEGMENT_NAME = B.TABLE_NAME
AND SEGMENT_TYPE = 'TABLE PARTITION'
AND B.TABLESPACE_NAME = C.NAME
AND D.OWNER = B.TABLE_OWNER
AND D.TABLE_NAME = B.TABLE_NAME
AND A.PARTITION_NAME = B.PARTITION_NAME
),
(
SELECT
TABLESPACE_NAME F_TABLESPACE_NAME,
MAX(BYTES) MAX_FREE_SPACE
FROM
SYS.DBA_FREE_SPACE
GROUP BY
TABLESPACE_NAME
)
WHERE
F_TABLESPACE_NAME = O_TABLESPACE_NAME
AND GREATEST(ROUND(100 * (NVL(HWM - AVG_USED_BLOCKS, 0)/GREATEST(NVL(HWM, 1), 1) ), 2), 0) > 25
AND OWNER = 'SCOTT'
AND BLOCKS > 128
ORDER BY
10 DESC, 1 ASC, 2 ASC;



OWNER TABLE_NAME SEGMENT_TYPE WASTE_PER TABLE_KB NUM_ROWS BLOCKS EMPTY_BLOCKS HIGHWATER_MARK AVG_USED_BLOCKS CHAIN_PER EXTENTS MAX_EXTENTS
------------------------------ ------------------------- ------------------ ---------- ---------- ---------- ---------- ------------ -------------- --------------- ---------- ---------- -----------
ALLO_EXTENT_PER C NEXT_EXTENT MAX_FREE_SPACE TABLESPACE_NAME
--------------- - ----------- -------------- ------------------------------
SCOTT ANUJ_BIG_TABLE TABLE 91.43 26048 9999 3256 0 3255 279 0 41 2147483645
0 Y 1048576 53805056 USERS