Oracle用户空间统计

慕3681

　　select owner,sum(bytes)/1024/1024 sum_MB
from dba_segments
where tablespace_name='USERS' and owner like 'ZJ%'
group by owner
order by 2 desc;
SETNEWPAGENONEHEADINGOFFSPACE0PAGESIZE0TRIMOUTONTRIMSPOOLONLINESIZE2500colsep|feedbackofftermoutoffpages0
setcolsep|
altersessionsetnls_date_format='yyyy-mm-ddhh24:mi:ss';
spooldeltab.sql
select'truncatetable'||tname||'dropstorage'||';'fromtab
whereTNAMEnotin('ZJTYID_YYTID','LZW_GPRS_ALL01',
'TMP_LZW_YYTC_YXPLAN','TMP_LZW_ZGDSJ','TMP_LZW_QDSFWT',
'TB_LZW_ZGD_TMP','TB_LZW_DIC_MINGAN_TMP',
'TB_LZW_JC_BOSS_TMP','TB_LZW_JK_BOSS_TMP','TB_LZW_GQ_TMP','TB_LZW_GG_TMP',
'TB_LZW_PLAN_BILL_TMP','TB_LZW_PLAN_TMP','TB_LZW_PLAN_TMP','TB_LZW_GLB_OPEN_PLAN_TMP','TMP_LZW_QWYYTCID','TMP_LZW_QWYYTCNAME',
'TB_LZW_GPRS_ALL01','TMP_LZW_201065_2_QQTYYTCID');
spooloff;
exit;

nohupsqlplusZJLZW_S/XXXX@zjxxx@deltab.sql>deltab.log&
selectowner,sum(bytes)/1024/1024sum_mbfromdba_segmentswheretablespace_name='USERS'
andownerlike'ZJ%'andowner='ZJLZW_S'groupbyowner

2010-04-13