This forum is currently locked. You can't register or post questions at this time. (read more)

Identify objects in a Tablespace

All posts relating to Oracle database administration.

Moderator: Tim...

sivakumarocp
Advisor
Posts: 260
Joined: Sat Jun 27, 2009 3:53 am
Location: India

Identify objects in a Tablespace

Postby sivakumarocp » Fri Apr 11, 2014 3:16 am

Hi Tim,

In my production database, one of the tablespace got fragmented heavily, it has only 9 objects in it. So I am planning to move those objects to new tablespace then i'll drop current TBS and i'll rename the new tablespace with the old one.

Before to drop the old TBS, I'll make sure that all those 9 objects are moved to new TBS. Here my concern is, I took the segment list from the fragmented TBS on DBA_SEGMENTS view, this view will show the objects which has segments in it.

but what will happen on a objects which was created on a tablespace but no segment allocated to it. if so then those objects wont listed in DBA_SEGMENTS view. If I drop the old TBS then those empty objects also get dropped.

Here my question is, how can I find out objects which was created on a tablespace with no segment allocated in it.
Regards
Sivakumar.A
www.sivakumardba.com

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Identify objects in a Tablespace

Postby Tim... » Fri Apr 11, 2014 1:27 pm

Hi.

Query DBA_TABLES, DBA_INDEXES and DBA_MVIEWS, DBA_TAB_PARTITIONS.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

User avatar
matthew_morris
Member
Posts: 13
Joined: Thu May 23, 2013 12:15 am
Location: Orlando, FL
Contact:

Re: Identify objects in a Tablespace

Postby matthew_morris » Sun Apr 13, 2014 9:26 pm

Alternately, you could just use online redefinition to rebuild the objects while keeping them in the existing tablespace.
One of its primary uses is to reduce fragmentation.

http://docs.oracle.com/cd/B28359_01/ser ... ADMIN01514
Matthew Morris
DBA/Developer/Author
OCP DBA 7.3, 8, 9i, 10g, 11g, 12c
OCP Advanced PL/SQL Developer
My website: http://www.oraclecertificationprep.com
My blog: http://ocprep.blogspot.com/

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Identify objects in a Tablespace

Postby Tim... » Sun Apr 13, 2014 9:52 pm

Hi.

This is not a suitable solution. Rebuilding the table, using a move or an online table redefinition, does not guarantee the resulting segments will be at the start of the data file and free space in the middle and at the end of the file. Since the post was aimed and reducing the fragmentation in the datafile, this is not relevant.

Note. I'm assuming the "fragmentation" mentioned by the OP was to do with gaps of unused space in the datafile.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

User avatar
matthew_morris
Member
Posts: 13
Joined: Thu May 23, 2013 12:15 am
Location: Orlando, FL
Contact:

Re: Identify objects in a Tablespace

Postby matthew_morris » Mon Apr 14, 2014 2:26 am

Tim... wrote:Note. I'm assuming the "fragmentation" mentioned by the OP was to do with gaps of unused space in the datafile.


OK. I'll grant that I was assuming the OP was referring to fragmentation in the segments. To get so many fragments of unused space in the datafile(s) that it would be considered 'heavily fragmented' would require creating and dropping a large number of extents in that tablespace. Intra-segment fragmentation is much more common.

That said -- as I was writing this, I thought of an interesting 'what-if' question. If the situation the OP is worried about were the case -- a 10th object was created in that table, but with deferred segment creation and no segment was ever allocated. Assume the other nine tables have been moved to the new TS and dropped from the old. If the old tablespace is dropped, but the INCLUDING CONTENTS clause is not included... will Oracle drop it or return an error? The documentation on the IC clause simply says "If you omit this clause, and the tablespace is not empty, then the database returns an error and does not drop the tablespace." That could be read either way when referring to objects with deferred segment creation. Something to test. :)
Matthew Morris
DBA/Developer/Author
OCP DBA 7.3, 8, 9i, 10g, 11g, 12c
OCP Advanced PL/SQL Developer
My website: http://www.oraclecertificationprep.com
My blog: http://ocprep.blogspot.com/

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Identify objects in a Tablespace

Postby Tim... » Mon Apr 14, 2014 7:08 am

Hi.

That question is easily answered.

Code: Select all

sys@db11g> CREATE TABLESPACE ts1 DATAFILE 'C:\APP\ORACLE\ORADATA\DB11G\TS1.DBF' size 1M;

Tablespace created.

sys@db11g> CREATE TABLE test.tab1 (id NUMBER) TABLESPACE ts1;

Table created.

sys@db11g> SELECT owner, tablespace_name FROM dba_tables WHERE table_name = 'TAB1';

OWNER                          TABLESPACE_NAME
------------------------------ ------------------------------
TEST                           TS1

1 row selected.

sys@db11g> DROP TABLESPACE ts1;

Tablespace dropped.

sys@db11g> SELECT owner, tablespace_name FROM dba_tables WHERE table_name = 'TAB1';

no rows selected

sys@db11g>


If we avoid INCLUDING CONTENTS AND DATAFILES, the object metadata remains.

Code: Select all

sys@db11g> DROP TABLESPACE ts1;

Tablespace dropped.

sys@db11g> SELECT owner, tablespace_name FROM dba_tables WHERE table_name = 'TAB1';

OWNER                          TABLESPACE_NAME
------------------------------ ------------------------------
TEST                           TS1

1 row selected.

sys@db11g>


Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog


Return to “Oracle Database Administration”

Who is online

Users browsing this forum: No registered users and 5 guests

cron