表空间监控(三)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/
########################################################################################
导言:监控指定表空间详细信息的脚本。
#######################################################################################
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/