Discussions
Categories
- 196.9K All Categories
- 2.2K Data
- 239 Big Data Appliance
- 1.9K Data Science
- 450.3K Databases
- 221.7K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 550 MySQL Community Space
- 478 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3K ORDS, SODA & JSON in the Database
- 545 SQLcl
- 4K SQL Developer Data Modeler
- 187K SQL & PL/SQL
- 21.3K SQL Developer
- 295.9K Development
- 17 Developer Projects
- 138 Programming Languages
- 292.6K Development Tools
- 107 DevOps
- 3.1K QA/Testing
- 646K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 155 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.1K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 18 Java Essentials
- 160 Java 8 Questions
- 86K Java Programming
- 80 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 204 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 440 LiveLabs
- 38 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 171 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 232 Portuguese
Where to find EXTENTS and SEGMENTS of a table

Hi,
I have thousands of tables that do not have Extents nor Segments.
Here are my queries :
- Query to find tables : SELECT table_name FROM all_tables WHERE table_name LIKE 'KEYSET%' (this gives me some thousands records / tables)
- To find segments : select segment_name,segment_type, bytes from dba_segments WHERE segment_name LIKE 'KEYSET%' (gives me 28 records)
- To find extents : select segment_name,segment_type, bytes from dba_extents WHERE segment_name LIKE 'KEYSET% (gives me 28 records)
Questions are :
- The segments_name in both extent are not exactly the same. Why ?
- 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
Answers
-
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.
-
They could be external tables. Or IOTs. Or clustered tables. However, to have thousands is unusual.
-
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
-
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?
-
... 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
-
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 :)
-
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 :)
-
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
-
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
-
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.