Listing Objects in an Oracle Tablespace

SQL lists every segment within a specific tablespace in an Oracle database

A detailed explanation of Oracle Database SQL code used to list objects within a tablespace, including purpose, breakdown, key points, and insights.

Sample SQL Command

 1set pages 999
 2col owner format a15
 3col segment_name format a40
 4col segment_type format a20
 5select owner
 6,      segment_name
 7,      segment_type
 8from   dba_segments
 9where  lower(tablespace_name) like lower('%&tablespace%')
10order by owner, segment_name
11/

Sample Oracle Output

 1OWNER           SEGMENT_NAME                             SEGMENT_TYPE
 2--------------- ---------------------------------------- --------------------
 3LARRY            BIN$Q+k4qsgBLNngRAAQg5ZRWg==$0           INDEX
 4LARRY            BIN$Q+k4qsgCLNngRAAQg5ZRWg==$0           TABLE
 5LARRY            BIN$Q+k4qsgELNngRAAQg5ZRWg==$0           INDEX
 6LARRY            BIN$Q+k4qsgFLNngRAAQg5ZRWg==$0           TABLE
 7LARRY            BOX                                      TABLE
 8LARRY            BOX_PK                                   INDEX
 9LARRY            CD                                       TABLE
10LARRY            CD_PK                                    INDEX
11LARRY            LOCATION                                 TABLE
12LARRY            LOCATION_PK                              INDEX
13LARRY            LOTS_OF_ROWS                             TABLE
14LARRY            POO                                      TABLE
15LARRY            SERVERS                                  TABLE
16LARRY            SYS_C0023466                             INDEX
17LARRY            SYS_C0023467                             INDEX
18LARRY            SYS_C0027234                             INDEX
19LARRY            TEST_TABLE                               TABLE
20LARRY            TRACK                                    TABLE
21LARRY            TRACK_PK                                 INDEX
22
2319 rows selected.

Purpose:

• To identify and display every segment (tables, indexes, partitions and LOBs that have storage allocated) residing in a chosen tablespace.

Breakdown:

1. set pages 999: This setting sets the number of lines on each page of output to 999, so column headings repeat less often if the tablespace holds a large number of objects.
2. col owner format a15, col segment_name format a40**, col segment_type format a20**: These lines define column formatting in the output. They set specific widths for the "owner", "segment_name", and "segment_type" columns to improve readability.
3. select owner, segment_name, segment_type: This clause selects three specific columns from the dba_segments system view:
   • owner: The schema (user) who owns the object.
   • segment_name: The actual name of the object.
   • segment_type: The type of segment (TABLE, INDEX, TABLE PARTITION, INDEX PARTITION, LOBSEGMENT, etc.).
4. from dba_segments: This specifies the data source for the query. dba_segments holds information about all database segments, including their type, owner, and tablespace association.
5. where lower(tablespace_name) like lower('%&tablespace%'): This clause filters the results based on the tablespace name.
   • lower(tablespace_name): Converts the tablespace name to lowercase for case-insensitive comparison.
   • like lower('%&tablespace%'): Uses a wildcard comparison with % to accept any tablespace name passed as the &tablespace variable.
6. order by owner, segment_name: This clause sorts the output first by owner and then by segment name within each owner.

Key Points:

• This code relies on the dba_segments system view, which requires appropriate privileges to access (typically DBA or SYSDBA).
• The like clause with wildcards allows for listing objects in any tablespace whose name matches the provided part.
• The code filters for all object types (segment_type). If you only need specific types (e.g., tables), modify the where clause accordingly.
• Consider additional filtering using other columns from dba_segments for more granular results.
• Empty tables created with deferred segment creation (the default, DEFERRED_SEGMENT_CREATION = TRUE) have no segment until the first row is inserted, so they do not appear. Query dba_tables or dba_indexes by tablespace_name to catch them.

Insights and Explanations:

• set pages only controls how many lines SQL*Plus prints on each page, and so how often it repeats column headings; it has no effect on query cost. Use set pages 0 to suppress headings entirely when spooling to a file.
• Because the filter uses '%name%', a partial name can match more than one tablespace (for example, USERS also matches USERS2). Enter the full tablespace name when you want only one.
• The code provides a basic listing of segments. To show size, add bytes or blocks from dba_segments. dba_segments has no creation-date column; for the object creation date, join to dba_objects (column created) on owner and object_name = segment_name.

The query lists every segment within a specified tablespace. Empty tables with deferred segment creation are the main gap, so adapt it to your needs.