Skip to Main Content
  • Questions

  • How to make sure the tablespace is empty

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Ashish.

Asked: February 27, 2017 - 7:20 pm UTC

Last updated: April 25, 2019 - 11:52 pm UTC

Version: 12.1.0.2

Viewed 10K+ times! This question is

You Asked

Hello,

I am implementing TDE (tablespace level) and once I have moved tables/indexes etc, from a tablespace to an encrypted tablespace, i want to make sure that tablespace has no objects.

I am using the following query to make sure that tablespace are empty :


SELECT UT.TABLESPACE_NAME "TABLESPACE", COUNT (US.SEGMENT_NAME) "NUM SEGMENTS"
FROM DBA_TABLESPACES UT, DBA_SEGMENTS US
WHERE UT.TABLESPACE_NAME = US.TABLESPACE_NAME
GROUP BY (UT.TABLESPACE_NAME)
ORDER BY COUNT (US.SEGMENT_NAME) DESC;

----

If the tablespace name does NOT show up in the result of the above query, I take that particular tablespace is empty.

My question is;

1) Is/are there any other way to make sure , that tablespace has no contents ?

Thanks

Ashish

and Connor said...

Yes, that will be fine.

The only one that sometimes catches out people is if they used DBA_EXTENTS, because that will *not* show recyclebin objects.

See link below for an example of that

https://connormcdonald.wordpress.com/2012/11/23/why-cant-i-resize-my-datafile/

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

DBA_SEGMENTS might not be populated

Matthew Thompson, April 25, 2019 - 10:38 pm UTC

I just did a similar thing and noticed 6 tables didn't get picked up during our move process.

After looking I found they were empty tables with SEGMENT_CREATED=NO in dba_tables so they didn't have an associated entry in dba_segments.

While it might not matter as they have no rows if you drop the tablespace including contents/datafiles it'll drop those tables.

Just something to watch if you're basing your check on dba_segments.
Connor McDonald
April 25, 2019 - 11:52 pm UTC

Nice input.

Thats part of our deferred segment creation stuff - no need to create a segment until someone adds the first row.

my tablespace is empty?

Gilberto de F Lins Guara, April 19, 2021 - 8:25 pm UTC

Try this query, the second column indicate that you have tablespace, but nobody use this

select tbs.tablespace_name, ext.tablespace_name from DBA_TABLESPACES tbs left join dba_extents ext on tbs.tablespace_name =ext .tablespace_name
where ext.tablespace_name is null ;
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.