Skip to content
Advertisements

Script : Tablespace free space

How do we identify tablespace free space in database ?

SQL> SELECT /* + RULE */ df.tablespace_name "Tablespace",
2 df.bytes / (1024 * 1024) "Size (MB)",
3 SUM(fs.bytes) / (1024 * 1024) "Free (MB)",
4 Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free",
5 Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used"
6 FROM dba_free_space fs,
7 (SELECT tablespace_name,SUM(bytes) bytes
8 FROM dba_data_files
9 GROUP BY tablespace_name) df
10 WHERE fs.tablespace_name (+) = df.tablespace_name
11 GROUP BY df.tablespace_name,df.bytes
12 UNION ALL
13 SELECT /* + RULE */ df.tablespace_name tspace,
14 fs.bytes / (1024 * 1024),
15 SUM(df.bytes_free) / (1024 * 1024),
16 Nvl(Round((SUM(fs.bytes) - df.bytes_used) * 100 / fs.bytes), 1),
17 Round((SUM(fs.bytes) - df.bytes_free) * 100 / fs.bytes)
18 FROM dba_temp_files fs,
19 (SELECT tablespace_name,bytes_free,bytes_used
20 FROM v$temp_space_header
21 GROUP BY tablespace_name,bytes_free,bytes_used) df
22 WHERE fs.tablespace_name (+) = df.tablespace_name
23 GROUP BY df.tablespace_name,fs.bytes,df.bytes_free,df.bytes_used
24 ORDER BY 4 DESC;
Tablespace Size (MB) Free (MB) % Free % Used
------------------------------ ---------- ---------- ---------- ----------
UNDOTBS1 130 102.5 79 21
USERS 5 3.625 73 28
VPX 1024 650.625 64 36
VUM 1000 601.75 60 40
SYSAUX 880 80.5625 9 91
SYSTEM 750 42.25 6 94
TEMP 119 0 0 100

7 rows selected.
SQL>
Advertisements
7 Comments Post a comment
  1. Desmond #

    How do you import another schema from another database i have run the expdp nw i what to imp

    November 21, 2011
  2. Desmond #

    How do i import another schema from another database i have run the expdp nw i what to do imp i have the dmp file

    November 21, 2011
    • Go to another database :

      create directory for where is your dumpfile located directory

      grant this directory to system

      impdp system/pwd directory=directoryname dumpfile=dumpfilename logfile=logfilename schema=xxxx (which schema you want import) remap_tablespace=xxxx:yyyy (If tablespace exists on another database which as same name for where you take export schema allocated tablespace, not need this one).

      Example :
      impdp system/pwd directory=test dumpfile=test.dmp logfile=testimp.log schema=xxxx remap_tablespace=xxxx:yyyy

      November 21, 2011
  3. Desmond #

    Which oracle_sid must i set the database am going to import or the database i export from

    November 21, 2011
    • which database you want to import, that’s one.

      November 21, 2011
    • desmond #

      hi where can i send you my error screen am getting when installing oracle in Linux Ubuntu 10.1

      November 23, 2011
  4. Desmond #

    Thanks i managed to import data

    November 21, 2011

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: