表空间监控（三）tablespace detail

最新推荐文章于 2025-08-01 15:48:18 发布

cqrw65623

最新推荐文章于 2025-08-01 15:48:18 发布

阅读量114

点赞数

文章标签：数据库

表空间监控（三）tablespace detail

导言：监控指定表空间详细信息的脚本。

#######################################################################################
Oracle Environment=>11.2.0.4.0 rac on two nodes
OS Environment=>AIX 6.1 (64bit)
#######################################################################################

SQL> @TSdetail.sql

Enter value for tablespace: USERS
old 12: WHERE tablespace_name = '&tablespace'
new 12: WHERE tablespace_name = 'USERS'

TSpace Sgmnt Sgmnt Sgmnt Min Max Sgmnt No of
Name Owner Type Name No of No of Size Extent
Ext Ext Mb
-------------------- --------------- --------------- ----------------------------------- ------------ ------------ ----- -----------
USERS BMCD_DB INDEX FK_BRAND_DEALER 1 2147483645 0 2
USERS BMCD_DB INDEX FK_CITY_DEALER 1 2147483645 0 1
USERS BMCD_DB INDEX FK_DEALER_BRAND 1 2147483645 0 2
USERS BMCD_DB INDEX FK_DEALER_LOAD 1 2147483645 9 2
USERS BMCD_DB INDEX FK_LOAN_DEALER 1 2147483645 13 6
USERS BMCD_DB INDEX FK_REGION_DEALER 1 2147483645 0 1
USERS BMCD_DB INDEX IDX_DEALER_NAME 1 2147483645 0 2
USERS BMCD_DB INDEX IDX_LOAN_APPNO 1 2147483645 28 7
USERS BMCD_DB INDEX IDX_LOAN_DATIM 1 2147483645 10 3
USERS BMCD_DB INDEX IDX_LOAN_SALES 1 2147483645 7 7
USERS BMCD_DB INDEX NAME_IDX 1 2147483645 0 2
USERS BMCD_DB INDEX PK_BRAND 1 2147483645 0 1
USERS BMCD_DB INDEX PK_DEALER 1 2147483645 0 1
USERS BMCD_DB INDEX PK_ETP_USER 1 2147483645 0 2
USERS BMCD_DB INDEX PK_LOAN 1 2147483645 13 6
USERS BMCD_DB INDEX PK_POSITION 1 2147483645 0 1
USERS BMCD_DB INDEX POSITIONG_NAME_IDX 1 2147483645 0 1
USERS BMCD_DB TABLE F_IM_CS_BRAND 1 2147483645 0 1
USERS BMCD_DB TABLE F_IM_CS_BRAND_DEALER_MAP 1 2147483645 0 1
USERS BMCD_DB TABLE F_IM_CS_DEALER 1 2147483645 0 4
USERS BMCD_DB TABLE F_IM_CS_ETP_USER 1 2147483645 0 7
USERS BMCD_DB TABLE F_IM_CS_LOAN 1 2147483645 56 7
USERS BMCD_DB TABLE F_IM_CS_POSITION 1 2147483645 0 1
USERS BMCD_DB TABLE F_IM_DEALER_LOAN_MAP 1 2147483645 21 7
USERS DB_BCD INDEX BOOKMARK_PK 1 2147483645 0 1
USERS DB_BCD TABLE TS_AUTH 1 2147483645 0 1
USERS DB_BCD TABLE TS_BOOKMARK 1 2147483645 0 3
USERS DB_BCD TABLE VUCS_DB_AP_PBC_CREDITSUMMAR_20 1 2147483645 0 1
USERS ODS_FX_READ TABLE CAP 1 2147483645 27 42
USERS SCOTT INDEX PK_DEPT 1 2147483645 0 1
USERS SCOTT INDEX PK_EMP 1 2147483645 0 1
USERS SCOTT TABLE BCDTEST 1 2147483645 0 3
USERS SCOTT TABLE DEPT 1 2147483645 0 1
USERS SCOTT TABLE EMP 1 2147483645 0 1
USERS SCOTT TABLE SALGRADE 1 2147483645 0 1

35 rows selected.

### NOTE ###
TSpace Name : Tablespace name
Sgmnt Owner : Segment owner
Sgmnt Type : Segment type
Sgmnt Name : Segment name
Min No of Ext : Min number of extents to be allocated to the segment
Max No of Ext : Max number of extents to be allocated to the segment
Sgmnt Size Mb : Segment size in Mb
No of Extent : Number of extents in this segment
#########################################################################

###TSdetail.sql scripts contents are as follows ###

set lines 150
set pages 40

clear break

col TSname heading 'TSpace|Name|'
col TSname format a20
col SgmntOwner heading 'Sgmnt|Owner|'
col SgmntOwner format a15
col SgmntType heading 'Sgmnt|Type|'
col SgmntType format a15
col SgmntName heading 'Sgmnt|Name|'
col SgmntName format a35
col MinExt heading 'Min|No of|Ext'
col MinExt format 99999999999
col MaxExt heading 'Max|No of|Ext'
col MaxExt format 99999999999
col SgmntSize heading 'Sgmnt|Size|Mb'
col SgmntSize format 9999
col SgmntExentNo heading 'No of|Extent|'
col SgmntExentNo format 9999999999

SELECT
ds.tablespace_name as "TSname",
ds.owner as "SgmntOwner",
ds.segment_type as "SgmntType",
ds.segment_name as "SgmntName",
ds.min_extents as "MinExt",
ds.max_extents as "MaxExt",
ROUND(ds.bytes/1024/1024,0) as "SgmntSize",
SUM(ds.extents) as "SgmntExentNo"
FROM
dba_segments ds
WHERE tablespace_name = '&tablespace'
GROUP BY
ds.tablespace_name,
ds.owner,
ds.segment_type,
ds.segment_name,
ds.min_extents,
ds.max_extents,
ds.bytes
ORDER BY
ds.tablespace_name,
ds.owner,
ds.segment_type,
ds.segment_name
;

########################################################################################
版权所有，文章允许转载，但必须以链接方式注明源地址，否则追究法律责任!【QQ交流群：53993419】
QQ：14040928
本文链接： http://blog.itpub.net/26442936/viewspace-1471625/
########################################################################################

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26442936/viewspace-1471625/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26442936/viewspace-1471625/