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.
Using PL/SQL Packages to Display Information About Schema Object Space Usage
These Oracle-supplied PL/SQL packages provide information about schema objects:
Package and Procedure/Function Description table
Example: Using DBMS_SPACE.UNUSED_SPACE
The following SQL*Plus example uses the DBMS_SPACE package to obtain unused space information.
Using Views to Display Information About Space Usage in Schema Objects
These views display information about space usage in schema objects:
View Description Table
The following sections contain examples of using some of these views.
Example 1: Displaying Segment Information
The following query returns the name and size of each index segment in schema hr:
The query output is:
Example 2: Displaying Extent Information
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:
The query output is:
10 rows selected.
Example 4: Displaying Segments that Cannot Allocate Additional Extents
It is possible that a segment cannot be allocated to an extent for any of the following reasons:
The following query returns the names, owners, and tablespaces of all segments that satisfy any of these criteria:
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:
Oracle 10g Related Interview Questions
|Oracle 10g Interview Questions||Oracle 9i Interview Questions|
|Oracle 8i Interview Questions||Informatica Interview Questions|
|PL/SQL Interview Questions||Oracle 11g Interview Questions|
|SQL Interview Questions||Oracle apps Interview Questions|
|Sybase Interview Questions||Oracle Apps ERP Interview Questions|
|Oracle 7.3 Interview Questions||Oracle Access Manager Interview Questions|
|Oracle Application Framework Interview Questions||Oracle Apps DBA Interview Questions|
Oracle 10g Related Practice Tests
|Oracle 10g Practice Tests||Oracle 9i Practice Tests|
|Oracle 8i Practice Tests||Informatica Practice Tests|
|PL/SQL Practice Tests||Oracle 11g Practice Tests|
|SQL Practice Tests||Oracle apps Practice Tests|
|Sybase Practice Tests||Oracle Apps ERP Practice Tests|
|Oracle 7.3 Practice Tests|
All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.