в 13:45, 26 мая 2010

2010-05-26T13:45:46Z

Admin: 1 версия

2010-05-26T10:08:14Z

1 версия

Новая страница

== Join dba_extents and dba_rollback_segs==



<source lang="sql">
SQL>
SQL> SELECT b.segment_name, status, sum(bytes) sum_bytes
2 FROM dba_extents a, dba_rollback_segs b
3 WHERE a.segment_name = b.segment_name
4 and rownum < 50
5 AND a.segment_type = "ROLLBACK"
6 GROUP BY b.segment_name, status
7 ORDER BY status DESC, b.segment_name;
SEGMENT_NAME STATUS SUM_BYTES
------------------------------ ---------------- ----------
SYSTEM ONLINE 393216
SQL>
SQL></source>

== Log dba_extents==



<source lang="sql">
SQL>
SQL> CREATE TABLE extents_log
2 (log_date DATE,
3 segment_name VARCHAR2(81),
4 segment_type VARCHAR2(17),
5 count_extents NUMBER,
6 bytes_extents NUMBER);
Table created.
SQL>
SQL>
SQL> INSERT INTO extents_log
2 SELECT TRUNC(SYSDATE), segment_name, segment_type,
3 COUNT(*), SUM(bytes)
4 FROM dba_extents
5 WHERE segment_name IN
6 ("COL$", "DEPENDENCY$", "ERROR$", "IDL_CHAR$",
7 "IDL_SB4$", "IDL_UB1$", "IDL_UB2$", "LINK$", "OBJ$",
8 "OBJAUTH$", "SOURCE$", "SYSAUTH$", "TRIGGER$",
9 "TRIGGERCOL$", "USER$")
10 GROUP BY segment_name, segment_type;
13 rows created.
SQL>
SQL>
SQL> SELECT segment_name, log_date, segment_type,
2 count_extents, bytes_extents
3 FROM extents_log
4 ORDER BY segment_name, log_date;
SEGMENT_NAME LOG_DATE SEGMENT_TYPE COUNT_EXTENTS BYTES_EXTENTS
--------------- --------- ----------------- ------------- -------------
DEPENDENCY$ 25-JUL-08 TABLE 17 2097152
ERROR$ 25-JUL-08 TABLE 2 131072
IDL_CHAR$ 25-JUL-08 TABLE 17 2097152
IDL_SB4$ 25-JUL-08 TABLE 18 3145728
IDL_UB1$ 25-JUL-08 TABLE 46 32505856
IDL_UB2$ 25-JUL-08 TABLE 28 13631488
LINK$ 25-JUL-08 TABLE 1 65536
OBJ$ 25-JUL-08 TABLE 17 2097152
OBJAUTH$ 25-JUL-08 TABLE 2 131072
SOURCE$ 25-JUL-08 TABLE 81 83886080
SYSAUTH$ 25-JUL-08 TABLE 1 65536
SEGMENT_NAME LOG_DATE SEGMENT_TYPE COUNT_EXTENTS BYTES_EXTENTS
--------------- --------- ----------------- ------------- -------------
TRIGGER$ 25-JUL-08 TABLE 4 262144
TRIGGERCOL$ 25-JUL-08 TABLE 1 65536
13 rows selected.
SQL>
SQL>
SQL> drop table extents_log;
Table dropped.
SQL>
SQL></source>

== Query DBA_EXTENTS for Extent number in the segment, Starting block number for the extent, Size of the extent in bytes, Size of the extent, in Oracle blocks==



<source lang="sql">
SQL> select
6 Extent_ID, /*Extent number in the segment*/
7 Block_ID, /*Starting block number for the extent*/
8 Bytes, /*Size of the extent, in bytes*/
9 Blocks /*Size of the extent, in Oracle blocks*/
10 from DBA_EXTENTS
11 where Segment_Name = "segment_name"
12 order by Extent_ID;
SQL>
SQL></source>

== Query DBA_EXTENTS for Name of the segment, Type of segment (ex. TABLE, INDEX)==



<source lang="sql">
SQL> select
4 Segment_Name, /*Name of the segment*/
5 Segment_Type /*Type of segment (ex. TABLE, INDEX)*/
10 from DBA_EXTENTS
11 where Segment_Name = "segment_name"
12 order by Extent_ID;
SQL>
SQL></source>

== Query dba_extents for segment name==



<source lang="sql">
SQL>
SQL> COLUMN segment_name FORMAT a15
SQL> SELECT segment_name, segment_type, COUNT(*), SUM(bytes)
2 FROM dba_extents
3 WHERE segment_name IN
4 ("COL$", "DEPENDENCY$", "ERROR$", "IDL_CHAR$",
5 "IDL_SB4$", "IDL_UB1$", "IDL_UB2$", "LINK$", "OBJ$",
6 "OBJAUTH$", "SOURCE$", "SYSAUTH$", "TRIGGER$",
7 "TRIGGERCOL$", "USER$")
8 and rownum < 50
9 GROUP BY segment_name, segment_type
10 ORDER BY segment_type, segment_name;
SEGMENT_NAME SEGMENT_TYPE COUNT(*) SUM(BYTES)
--------------- ------------------ ---------- ----------
OBJ$ TABLE 17 2097152
OBJAUTH$ TABLE 2 131072
SOURCE$ TABLE 30 15728640
SQL>
SQL></source>

== Query DBA_EXTENTS for Tablespace name,Owner of the segment==



<source lang="sql">
SQL> select
2 Tablespace_Name, /*Tablespace name*/
3 Owner /*Owner of the segment*/
10 from DBA_EXTENTS
11 where Segment_Name = "segment_name"
12 order by Extent_ID;
SQL>
SQL></source>

← Предыдущая	Версия 13:45, 26 мая 2010
(нет различий)

Oracle PL/SQL Tutorial/System Tables Data Dictionary/dba extents - История изменений

в 13:45, 26 мая 2010

Admin: 1 версия