| Oracle® Database Administrator's Guide 11g Release 1 (11.1) Part Number B28310-01 |
|
|
View PDF |
| Oracle® Database Administrator's Guide 11g Release 1 (11.1) Part Number B28310-01 |
|
|
View PDF |
Oracle Database provides data dictionary views and PL/SQL packages that allow you to display information about the space usage of schema objects. Views and packages that are unique to a particular schema object are described in the chapter of this book associated with that object. This section describes views and packages that are generic in nature and apply to multiple schema objects.
These Oracle-supplied PL/SQL packages provide information about schema objects:
See Also:
Oracle Database PL/SQL Packages and Types Reference for a description of PL/SQL packagesExample: Using DBMS_SPACE.UNUSED_SPACE
The following SQL*Plus example uses the DBMS_SPACE package to obtain unused space information.
SQL> VARIABLE total_blocks NUMBER
SQL> VARIABLE total_bytes NUMBER
SQL> VARIABLE unused_blocks NUMBER
SQL> VARIABLE unused_bytes NUMBER
SQL> VARIABLE lastextf NUMBER
SQL> VARIABLE last_extb NUMBER
SQL> VARIABLE lastusedblock NUMBER
SQL> exec DBMS_SPACE.UNUSED_SPACE('SCOTT', 'EMP', 'TABLE', :total_blocks, -
> :total_bytes,:unused_blocks, :unused_bytes, :lastextf, -
> :last_extb, :lastusedblock);
PL/SQL procedure successfully completed.
SQL> PRINT
TOTAL_BLOCKS
------------
5
TOTAL_BYTES
-----------
10240
...
LASTUSEDBLOCK
-------------
3
These views display information about space usage in schema objects:
The following sections contain examples of using some of these views.
See Also:
Oracle Database Reference for a complete description of data dictionary viewsThe following query returns the name and size of each index segment in schema hr:
SELECT SEGMENT_NAME, TABLESPACE_NAME, BYTES, BLOCKS, EXTENTS
FROM DBA_SEGMENTS
WHERE SEGMENT_TYPE = 'INDEX'
AND OWNER='HR'
ORDER BY SEGMENT_NAME;
The query output is:
SEGMENT_NAME TABLESPACE_NAME BYTES BLOCKS EXTENTS ------------------------- --------------- -------- ------ ------- COUNTRY_C_ID_PK EXAMPLE 65536 32 1 DEPT_ID_PK EXAMPLE 65536 32 1 DEPT_LOCATION_IX EXAMPLE 65536 32 1 EMP_DEPARTMENT_IX EXAMPLE 65536 32 1 EMP_EMAIL_UK EXAMPLE 65536 32 1 EMP_EMP_ID_PK EXAMPLE 65536 32 1 EMP_JOB_IX EXAMPLE 65536 32 1 EMP_MANAGER_IX EXAMPLE 65536 32 1 EMP_NAME_IX EXAMPLE 65536 32 1 JHIST_DEPARTMENT_IX EXAMPLE 65536 32 1 JHIST_EMPLOYEE_IX EXAMPLE 65536 32 1 JHIST_EMP_ID_ST_DATE_PK EXAMPLE 65536 32 1 JHIST_JOB_IX EXAMPLE 65536 32 1 JOB_ID_PK EXAMPLE 65536 32 1 LOC_CITY_IX EXAMPLE 65536 32 1 LOC_COUNTRY_IX EXAMPLE 65536 32 1 LOC_ID_PK EXAMPLE 65536 32 1 LOC_STATE_PROVINCE_IX EXAMPLE 65536 32 1 REG_ID_PK EXAMPLE 65536 32 1 19 rows selected.
Information about the currently allocated extents in a database is stored in the DBA_EXTENTS data dictionary view. For example, the following query identifies the extents allocated to each index segment in the hr schema and the size of each of those extents:
SELECT SEGMENT_NAME, SEGMENT_TYPE, TABLESPACE_NAME, EXTENT_ID, BYTES, BLOCKS
FROM DBA_EXTENTS
WHERE SEGMENT_TYPE = 'INDEX'
AND OWNER='HR'
ORDER BY SEGMENT_NAME;
The query output is:
SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME EXTENT_ID BYTES BLOCKS ------------------------- ------------ --------------- --------- -------- ------ COUNTRY_C_ID_PK INDEX EXAMPLE 0 65536 32 DEPT_ID_PK INDEX EXAMPLE 0 65536 32 DEPT_LOCATION_IX INDEX EXAMPLE 0 65536 32 EMP_DEPARTMENT_IX INDEX EXAMPLE 0 65536 32 EMP_EMAIL_UK INDEX EXAMPLE 0 65536 32 EMP_EMP_ID_PK INDEX EXAMPLE 0 65536 32 EMP_JOB_IX INDEX EXAMPLE 0 65536 32 EMP_MANAGER_IX INDEX EXAMPLE 0 65536 32 EMP_NAME_IX INDEX EXAMPLE 0 65536 32 JHIST_DEPARTMENT_IX INDEX EXAMPLE 0 65536 32 JHIST_EMPLOYEE_IX INDEX EXAMPLE 0 65536 32 JHIST_EMP_ID_ST_DATE_PK INDEX EXAMPLE 0 65536 32 JHIST_JOB_IX INDEX EXAMPLE 0 65536 32 JOB_ID_PK INDEX EXAMPLE 0 65536 32 LOC_CITY_IX INDEX EXAMPLE 0 65536 32 LOC_COUNTRY_IX INDEX EXAMPLE 0 65536 32 LOC_ID_PK INDEX EXAMPLE 0 65536 32 LOC_STATE_PROVINCE_IX INDEX EXAMPLE 0 65536 32 REG_ID_PK INDEX EXAMPLE 0 65536 32 19 rows selected.
For the hr schema, no segment has more than one extent allocated to it.
Information about the free extents (extents not allocated to any segment) in a database is stored in the DBA_FREE_SPACE data dictionary view. For example, the following query reveals the amount of free space available as free extents in the SMUNDO tablespace:
SELECT TABLESPACE_NAME, FILE_ID, BYTES, BLOCKS
FROM DBA_FREE_SPACE
WHERE TABLESPACE_NAME='SMUNDO';
The query output is:
TABLESPACE_NAME FILE_ID BYTES BLOCKS --------------- -------- -------- ------ SMUNDO 3 65536 32 SMUNDO 3 65536 32 SMUNDO 3 65536 32 SMUNDO 3 65536 32 SMUNDO 3 65536 32 SMUNDO 3 65536 32 SMUNDO 3 131072 64 SMUNDO 3 131072 64 SMUNDO 3 65536 32 SMUNDO 3 3407872 1664 10 rows selected.
It is possible that a segment cannot be allocated to an extent for any of the following reasons:
The tablespace containing the segment does not have enough room for the next extent.
The segment has the maximum number of extents.
The segment has the maximum number of extents allowed by the data block size, which is operating system specific.
The following query returns the names, owners, and tablespaces of all segments that satisfy any of these criteria:
SELECT a.SEGMENT_NAME, a.SEGMENT_TYPE, a.TABLESPACE_NAME, a.OWNER
FROM DBA_SEGMENTS a
WHERE a.NEXT_EXTENT >= (SELECT MAX(b.BYTES)
FROM DBA_FREE_SPACE b
WHERE b.TABLESPACE_NAME = a.TABLESPACE_NAME)
OR a.EXTENTS = a.MAX_EXTENTS
OR a.EXTENTS = 'data_block_size' ;
Note:
When you use this query, replacedata_block_size with the data block size for your system.Once you have identified a segment that cannot allocate additional extents, you can solve the problem in either of two ways, depending on its cause:
If the tablespace is full, add a datafile to the tablespace or extend the existing datafile.
If the segment has too many extents, and you cannot increase MAXEXTENTS for the segment, perform the following steps.
Export the data in the segment
Drop and re-create the segment, giving it a larger INITIAL storage parameter setting so that it does not need to allocate so many extents. Alternatively, you can adjust the PCTINCREASE and NEXT storage parameters to allow for more space in the segment.
Import the data back into the segment.
