Determine If Tablespace Empty 3

[Michael42]

Hello,

From SQLPlus how can I determine if there is anything (tables, indexes etc.) in a Tablespace before I delete it?

I recently moved tables and indexes to another Tablespace.

Thanks,

Michael42

 

[hoinz]

you may query view dba_extents.

 

[Michael42]

Thanks hoinz! That was very useful.

 

[SantaMufasa]

Michael,

Not wanting to "gild the lily" that you may already have with Hoinz's excellent suggestion, you may find the following script, "Freespace.sql" useful. It can tell you how full (or empty) a tablespace is. Here is a sample invocation of my "@freespace.sql" script. (I have widened the screen display slightly to eliminate annoying screen wrap of the output.):

@freespace
                                                                                         % Free
                                                                                             of
                                Total            Total                                     Pot.
Tablespace                  Potential          Current                         Potential  Total Auto
Name               #        File Size        File Size       Bytes Used       Bytes Free  Bytes Ext. Filename
--------------- ---- ---------------- ---------------- ---------------- ---------------- ------ ---- --------------------------------------------------
DATA1              4    2,097,152,000      104,857,600       45,481,984    2,051,670,016     97 Yes  'D:\DHUNT\ORACLEDATA\DBDATA\DHUNT\DHUNTDATA01.DBF'
RBS                2    2,097,152,000       20,971,520           65,536    2,097,086,464     99 Yes  'D:\DHUNT\ORACLEDATA\DBDATA\DHUNT\DHUNTRBS01.DBF'
SYSTEM             1    2,097,152,000      153,092,096      149,422,080    1,947,729,920     92 Yes  'D:\DHUNT\ORACLEDATA\DBDATA\DHUNT\DHUNTSYS01.DBF'
TEMP               3    2,097,152,000       10,485,760        1,073,152    2,096,078,848     99 Yes  'D:\DHUNT\ORACLEDATA\DBDATA\DHUNT\DHUNTTEMP01.DBF'
YADA               5    2,097,152,000        2,097,152           65,536    2,097,086,464     99 Yes  'D:\DHUNT\ORACLEDATA\DBDATA\DHUNT\DHUNTYADA01.DBF'
                     ---------------- ---------------- ---------------- ----------------
sum                    10,485,760,000      291,504,128      196,108,288   10,289,651,712

5 rows selected.


Note: Physical disk limitations may prevent an AUTOEXTEND-ing file from reaching its potential

Wrote spool file "TablespaceUsage.txt".
************************************************************************************************************************************************************

Here is the code for "Freespace.sql":

set echo on
REM **************************************************************
REM David L. Hunt (file author) distributes this and other
REM files/scripts for educational purposes only, to illustrate the
REM use or application of various computing techniques. Neither the
REM author nor Dasages, LLC, makes any warranty regarding this
REM script's fitness for any industrial application or purpose nor is
REM there any claim that this or any similarly-distributed scripts
REM are error free or should be used for any purpose other than
REM illustration.
REM **************************************************************
set echo off
set linesize 165
set pagesize 40
set heading on
col fname heading "Filename" format a60
col fnum heading "#" format 999
col ts heading "Tablespace|Name" format a15
col tb heading "Total|Potential|File Size" format 999,999,999,999
col cb heading "Total|Current|File Size" like tb
col used heading "Bytes Used" like tb
col free heading "Potential|Bytes Free" like tb
col autoext heading "Auto|Ext." format a4
col percentfree heading "% Free|of|Pot.|Total|Bytes" format 999
break on report
compute sum of tb cb used free on report
spool TablespaceUsage.txt
select     substr(tablespace_name,1,15) ts
   ,d.file_id fnum
   ,decode(e.file#,null,d.bytes,(e.maxextend * blksize)) tb
   ,d.bytes cb
   ,decode(d.bytes,0,0,d.bytes-nvl(freebytes,0)) used
   ,decode(e.file#,null,d.bytes,(e.maxextend * blksize))
    -decode(d.bytes,0,0,d.bytes-nvl(freebytes,0)) free
   ,trunc(((decode(e.file#,null,d.bytes,(e.maxextend * blksize))
     -decode(d.bytes,0,0,d.bytes-nvl(freebytes,0)))/
      decode(e.file#,null,d.bytes,(e.maxextend * blksize))
         ),2) * 100 percentfree
   ,decode(e.file#,null,'No','Yes') autoext
   ,''''||substr(file_name,1,55)||'''' fname
from       sys.dba_data_files d
   ,(select file_id,sum(bytes) freebytes
           from sys.dba_free_space
           group by file_id) f
   ,sys.filext$ e
   ,v$datafile v
   ,(select value blksize from v$parameter
           where name = 'db_block_size') b
where      d.file_id=f.file_id(+)
  and      d.file_id=e.file#(+)
  and      v.file#=d.file_id
order by tablespace_name,creation_time
/
spool off
prompt
prompt Note: Physical disk limitations may prevent an AUTOEXTEND-ing file from reaching its potential
prompt
prompt Wrote spool file "TablespaceUsage.txt".
prompt

If a tablespace is "empty", then you will see "Bytes Used" for the tablespace's files "at minimum", which is 65,536 for "locally managed tablespaces" or 16,392.

Let me know if this is useful.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 21:34 (24Nov04) UTC (aka "GMT" and "Zulu"),
@ 14:34 (24Nov04) Mountain Time

 

[Michael42]

Mufasa,

Yes it must be XMas early me - great script!

Thanks for posting sir.

-Michael42

 

[KenCunningham]

....so give Santa a star like I just have!!!

 

[Michael42]

KenCunningham,

>> so give Santa a star like I just have!!!

I have ... again. The first time obviiously did not take - connection timed out etc. Sometimes I find this wonderfully useful forum of my dreams (I am sincere about this) has more than routine negative network phenomena. Perhaps Tek-Tips should look at load\server balancing and other options. After all, any service that people pay for (advertisers etc.) has an expectation of 99.9% uptime given current technologies. I am not the only client experiencing this. I have found this consistant from multiple states and military and civilian facilities.

Don't get the wrong idea - I think this is the BEST web site on the Internet period ... it just has more than usual stability issues.




Thanks,

Michael42

 

[SantaMufasa]

Michael and Ken,

You guys both have always shown fantastic appreciation with your kind awarding of "Purple Stars". I appreciate that very much since "Purple Stars" are a form of "payment" for the time folks spend in helping others to resolve issues. Because of Michael's consistent and constant "appreciation patterns", I was not worried about star issues.

I have noticed some of the same "stability" issues that Michael mentioned; not the least of which occurred for quite some time this morning (Thanksgiving Day). I shall pass along your observations, Michael, to Tek-Tips's management/infrastructure group.

Thanks again, to both of you, for your thoughtfulness. (In my case, it makes it all worthwhile ! )

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 18:38 (25Nov04) UTC (aka "GMT" and "Zulu"),
@ 11:38 (25Nov04) Mountain Time

 

[KenCunningham]

Michael - just to clarify. My comment wasn't meant as a criticism of any sort, I know it's easy to forget to award sometimes when one moves quickly to another topic. For what it's worth, the time-out problem is just as bad this side of the pond too, Santa.

 

[Michael42]

KenCunningham,

No worries - thanks.