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>
7 Comments
Post a comment
How do you import another schema from another database i have run the expdp nw i what to imp
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
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
Which oracle_sid must i set the database am going to import or the database i export from
which database you want to import, that’s one.
hi where can i send you my error screen am getting when installing oracle in Linux Ubuntu 10.1
Thanks i managed to import data