Where to find EXTENTS and SEGMENTS of a table

Yockee

Hi,

I have thousands of tables that do not have Extents nor Segments.

Here are my queries :

1. Query to find tables : SELECT table_name FROM all_tables WHERE table_name LIKE 'KEYSET%' (this gives me some thousands records / tables)
2. To find segments : select segment_name,segment_type, bytes from dba_segments WHERE segment_name LIKE 'KEYSET%' (gives me 28 records)
3. To find extents : select segment_name,segment_type, bytes from dba_extents WHERE segment_name LIKE 'KEYSET% (gives me 28 records)

Questions are :

1. The segments_name in both extent are not exactly the same. Why ?
2. Why many many tables do not have segments nor extents ? I thought if a table exists, it must have been allocated an extent / segment

BTW, i want to delete those unused keyset tables and then reclaim the space. I just need to see the after and before the deletion happens in term of space.

Need your enlightment. Please.

Ps : I am not a dba.

Thanks

EdStevens

Looks like deferred segment creation. See https://docs.oracle.com/en/database/oracle/oracle-database/18/sqlrf/CREATE-TABLE.html#GUID-F9CE0CC3-13AE-4744-A43C-EAC7A71AAAB6

I am a bit concerned about the fact that you have "thousands" of tables whose names all begin with 'KEYSET'. This would suggest a very poor, not well thought out, set of naming standards.

JohnWatson2

They could be external tables. Or IOTs. Or clustered tables. However, to have thousands is unusual.

Yockee

Hi @EdStevens and @JohnWatson2 ,

Those tables are generated by the system in certain condition. Its supposed to be be deleted automatically, but somehow doesnt.

So, IOTs and Clustered tables do not have segments / extents ? How do they exist and consume space ?

Thanks

EdStevens

Those tables are generated by the system in certain condition. Its supposed to be be deleted automatically, but somehow doesnt.

Constantly creating/deleting tables as part of normal system operation speaks of a poor design. Creation and deletion of tables should be something that only happens when an application is installed or updated. Not a part of normal operations. This sounds like it is creating "temporary" tables for a one-time use, to be deleted after use. But the fact that they have no extents sounds like the problem is compounded by by creating them in anticipation of a use that never actually happens. This kind of application design is common on SQL Server, and then gets implemented in Oracle by developers who think it's ok to apply MSSQL thinking to other databases. In oracle, we use Global Temporary Tables (GTT) instead of constantly creating/dropping tables for temporary use.

Of course, none of that actually solves your problem . . . Hmm. Beyond intellectual curiosity, exactly what is your problem?

Ps : I am not a dba.

Who is? And why isn't he or she addressing this?

Jonathan Lewis

... or global temporary tables.

There are many ways that a table definition can exist without an exact matching physical segment. The only thing odd about your initial observation (if I've interpreted it correctly) is that you've got some extents with names that don't match any segment (or vice versa), this should not happen.

Here's an IOT example to show you the type of thing that can happen - cut and pasted from 19c. There are no objects of any kind in the schema before I start:

Wrote file /mnt/working/afiedt.buf

  1  create table iot1 (id number primary key, v1 varchar2(20))
  2  organization index
  3* overflow
SQL> /

Table created.

SQL> select segment_name from user_segments;

no rows selected

SQL> insert into iot1 values(1,'xxx');

1 row created.

SQL> select segment_name from user_segments;

SEGMENT_NAME
-------------------------
SYS_IOT_OVER_98003
SYS_IOT_TOP_98003

2 rows selected.

1) Oracle defaults to "deferred segment creation" in all recent versions; i.e. it doesn't create the segment until you insert some data; and in many cases if you truncate the table with the "drop all storage" clause the segment (and corresponding index segments) will disappear.

2) IOTs are index segments, and if you don't name the primary key it will be called something like 'SYS_IOT_TOP_nnnnn' where nnnnn is the object_id of the table. If you've specified an overflow segment the overflow segment name will be sys_iot_over_nnnnn and it will appear in the all/dba/user_tables views as a table.

A query like the following might help you work out what has happened:

select  table_name, iot_name, cluster_name, temporary, 
        decode(temporary,'N',segment_created,'Y',null) seg_created 
from    user_tables

Regards

Jonathan Lewis

Yockee

Hi @EdStevens ,

Thanks for the reply.

Yes, the KEYSETs are meant to be temporary.

First off all, I am curios why those KEYSET tables don not have Segment nor Extent. Thanks for enlighten me on this one.

Secondly, I want to prove that those KEYSET really take so much space by looking into Segment and Extent table which I cant find them. Where can I find such information that those tables really are taking so much space ?

My goal is I want to delete those KEYSETs and reclaim the space. Also, I need to compare the number of space after and before the deletion.

thank you :)

Yockee

Hello @Jonathan Lewis,

thank you for your help.

I query on the KEYSET tables. Turns out many of them are just empty.

I also run your query for 1 table. it gives me this :

Keyset_291 doesn't have Segment nor Extent and the table is empty too; The IOT_Name is also NULL.

What does it mean ?

Thank you :)

Jonathan Lewis

There are two reasons I can think of why you'd get that result.

1) It's a simple heap table which has never had any data in it, or have been truncated. Oracle allows (and defaults to) "segment creation deferred" which means the segment will only be created when data is first inserted and the segment will disappear on a truncate.

2) It's a partitioned table, so the table definition doesn't have a data segment, the partitions do.

To check (2), add the column "partitioned" to the query I sent you, that will tell you that the table is partitioned (or not), and you could query all/user/dba_tab_partitions to learn more about the partitions.

To check (1), and to find out (most of) how the table was created you can call dbms_meta.get_ddl() which will read the data dictionary and generate SQL to recreate the table. (The first command assumes you're using SQL*Plus and is needed to get more than the first 80 characters of the output displayed)

set long 20000

select dbms_metadata.get_ddl('TABLE',{table},{owner}) from dual;

The {table} should be the table name in quotes, in capitals.

The {owner} should be the table owner in quotes, in capitals.

If you're on a recent version of Oracle I would expect to see "SEGMENT CREATION DEFERRED" appearing somewhere in the output, shortly after the column definition.

Regards

Jonathan Lewis

Yockee

Thanks @Jonathan Lewis. Now it starts to make sense.

I have added "partitioned" column as you suggested. Turns out that they are not partitioned.

One more thing. There are a thousands of empty KEYSET tables. Is it necessary to delete these tables (even though they contain no data) and reclaim the space so that the database performance will improve ? How to reclaim the space safely ?

Thanks

User_H3J7U

@Jonathan Lewis

There are two reasons I can think of why you'd get that result.

Why are there two reasons? SEG_CREATED=NO. For partitioned tables, the query should retrun N/A.