表空間監控（三）tablespace detail
表空間監控（三）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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 表空間監控（二）datafile size detailAI
- oracle表空間增加監控Oracle
- 監控oracle表空間指令碼Oracle指令碼
- oracle監控表空間,JOB，rman備份Oracle
- 【TABLESPACE】Oracle表空間最佳實踐Oracle
- Oracle基礎 01 表空間 tablespaceOracle
- Tablespace Fragmentation - 表空間碎片問題Fragment
- 監控和管理Oracle UNDO表空間的使用Oracle
- 【TABLESPACE】Oracle 表空間結構說明Oracle
- oracle的臨時表空間temporary tablespaceOracle
- 多臺ORACLE資料庫表空間監控方案Oracle資料庫
- [原創] 利用Oracle metric(threshold)監控表空間Oracle
- WINDOWS 環境下 監控ORACLE臨時表空間WindowsOracle
- 利用Oracle threshold(度量閥值)監控表空間Oracle
- 監控硬碟空間指令碼硬碟指令碼
- Oracle Bigfile Tablespace大檔案表空間Oracle
- 臨時表空間temporary tablespace相關操作
- 【tablespace】表空間離線的3種模式模式
- oracle空間使用監控指令碼Oracle指令碼
- linux 下監控磁碟空間Linux
- 聊聊Oracle可傳輸表空間（Transportable Tablespace）（上）Oracle
- 聊聊Oracle可傳輸表空間（Transportable Tablespace）（中）Oracle
- 聊聊Oracle可傳輸表空間（Transportable Tablespace）（下）Oracle
- MySQL Transportable Tablespace(傳輸表空間) 使用詳解MySql
- 表空間配額和UNLIMITED TABLESPACE許可權MIT
- 【Oracle】oracle tablespace&datafile -- oracle表空間 分享[轉]Oracle
- Oracle create tablespace 建立表空間語法詳解Oracle
- AIX分頁（交換）空間的監控AI
- 【TABLESPACE】使用“ALTER TABLESPACE”命令的“RENAME”功能實現表空間快速重新命名
- 自動監控Oracle 表空間資訊併傳送郵件指令碼Oracle指令碼
- Linux發郵件磁碟空間監控Linux
- 大資料解決方案（一）之表空間--bigfile tablespace大資料
- Oracle10新特性：臨時表空間組(temporary tablespace group)Oracle
- 10G新特性： 臨時表空間組(temporary tablespace group)
- 用dbms_streams_tablespace_adm表空間的遷移(4)
- 用dbms_streams_tablespace_adm表空間的遷移(3)
- DB CONTROL中表空間監控的SQLSQL
- 使用rman在不同平臺之間傳送oracle asm表空間（transport tablespace)OracleASM