Labels

Friday, May 27, 2011

tablespace usage and schema managment

--space check including temporary tablespace

SELECT /* + RULE */ df.tablespace_name "Tablespace",
df.bytes / (1024 * 1024 * 1024) "Size (GB)",
SUM(fs.bytes) / (1024 * 1024 * 1024) "Free (GB)",
Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free",
Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used"
FROM dba_free_space fs,
(SELECT tablespace_name,SUM(bytes) bytes
FROM dba_data_files
GROUP BY tablespace_name) df
WHERE fs.tablespace_name (+) = df.tablespace_name
GROUP BY df.tablespace_name,df.bytes
UNION ALL
SELECT /* + RULE */ df.tablespace_name tspace,
fs.bytes / (1024 * 1024 * 1024),
SUM(df.bytes_free) / (1024 * 1024 * 1024),
Nvl(Round((SUM(fs.bytes) - df.bytes_used) * 100 / fs.bytes), 1),
Round((SUM(fs.bytes) - df.bytes_free) * 100 / fs.bytes)
FROM dba_temp_files fs,
(SELECT tablespace_name,bytes_free,bytes_used
FROM v$temp_space_header
GROUP BY tablespace_name,bytes_free,bytes_used) df
WHERE fs.tablespace_name (+) = df.tablespace_name
GROUP BY df.tablespace_name,fs.bytes,df.bytes_free,df.bytes_used
ORDER BY 4 DESC;

--Tablespace usage in MB including free and used percentage
SELECT F.TABLESPACE_NAME,
       TO_CHAR ((T.TOTAL_SPACE - F.FREE_SPACE),'999,999') "USED (MB)",
       TO_CHAR (F.FREE_SPACE, '999,999') "FREE (MB)",
       TO_CHAR (T.TOTAL_SPACE, '999,999') "TOTAL (MB)",
       TO_CHAR ((ROUND ((F.FREE_SPACE/T.TOTAL_SPACE)*100)),'999')||' %' PER_FREE
FROM   (
       SELECT       TABLESPACE_NAME, 
                    ROUND (SUM (BLOCKS*(SELECT VALUE/1024
                                        FROM V$PARAMETER 
                                        WHERE NAME = 'db_block_size')/1024)
                           ) FREE_SPACE
       FROM DBA_FREE_SPACE
       GROUP BY TABLESPACE_NAME
       ) F,
       (
       SELECT TABLESPACE_NAME,
       ROUND (SUM (BYTES/1048576)) TOTAL_SPACE
       FROM DBA_DATA_FILES
       GROUP BY TABLESPACE_NAME
       ) T
WHERE F.TABLESPACE_NAME = T.TABLESPACE_NAME
AND (ROUND ((F.FREE_SPACE/T.TOTAL_SPACE)*100)) < 10;


--Free space in the tablespace


select tablespace_name, free_percent
from (
            SELECT b.tablespace_name, b.tablespace_size_mb, sum(nvl(fs.bytes,0))/1024/1024 free_size_mb,
            (sum(nvl(fs.bytes,0))/1024/1024/b.tablespace_size_mb *100) free_percent
            FROM dba_free_space fs,
                 (SELECT tablespace_name, sum(bytes)/1024/1024 tablespace_size_mb FROM dba_data_files
                  GROUP BY tablespace_name
                 ) b
           where fs.tablespace_name = b.tablespace_name
           group by b.tablespace_name, b.tablespace_size_mb
        ) ts_free_percent
WHERE free_percent < 50
ORDER BY free_percent;


---checking tablespace usage

select a.tablespace_name,a.total_space,b.free_space from
(select tablespace_name,sum(bytes)/1024/1024 total_space from dba_data_files group by tablespace_name) a,
(select tablespace_name,sum(bytes)/1024/1024 free_space from dba_free_space group by tablespace_name) b
 where a.tablespace_name=b.tablespace_name(+);

--checking temporary tablespace usage

select a.tablespace_name,a.total_size,b.used_size,b.free_size
from
(select tablespace_name,bytes/1024/1024 total_size from dba_temp_files) a,
(select tablespace_name,bytes_used/1024/1024 used_size, bytes_free/1024/1024 free_size from v$temp_space_header)b
where a.tablespace_name(+)=b.tablespace_name;

--checking the user which is accessing the database

select a.sql_text,b.username,b.sid,b.serial#,b.command
from v$sqlarea a, v$session b
where a.address=b.sql_address;

--convert tablespace to local managemnt tablespace

exec dbms_space_admin.tablespace_migrate_to_local('tbs1');

--Check the platform to which transportable tablespace can be performed
select endian_format,a.platform_name
from
v$transportable_platform a, v$database b
where a.platform_name = b.platform_name;

exec dbms_tts.transport_set_check('test1',true);


--create tabelspace 

create tabelespace tbs1 'F:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST9\TEST2.DBF' size 10m extent management local segment space management auto;

--add datafile to tablespace
alter tablespace tbs1 add datafile 'F:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST9\TEST3.DBF' size 20m;

--droping dattafile from a tablespace
alter tablespace tbs1 drop datafile 'F:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST9\TEST3.DBF';

-droping tablespace
drop tablespace tbs1 including contents;
-droping tablespace.

drop tablespace tbs1 including contents and datafiles;--This will physically drop the datafile also.

--Resize the datafile

alter database datafile 'F:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST9\TEST3.DBF' resize 30m;

--To check the default tablespace

select * from database_properties where property_name like 'DEFAULT%';


To check the temporary tablespace
select tablespace_name,file_name from dba_temp_file;
v$tempfile
v$temp_space_header


--Transportable Tablespace

1)check the platform portabilty
select * from transportable_platform;
Check in the source and destination database wheather this is suitable or not.

select endian_format,a.platform_name
from
v$transportable_platform a, v$database b
where a.platform_name = b.platform_name;

2) check for the self containt tablespace
exec dbms_tts.transport_check_set('tbs1',true);

3)check any kind of violation in the tablespace

select * from transport_set_violations;

4)Make the tablespace readonly mode
sql>alter tablespace tbs1 read only;

5)Export the tablespace which you want to transport

EXPDP system/password DUMPFILE=expdat.dmp DIRECTORY=dpump_dir
        TRANSPORT_TABLESPACES = tbs1

6)Tranfer the dump file to destination using dbms_file_transfer or any other medium

7)now improt this dump file
IMPDP system/password DUMPFILE=expdat.dmp DIRECTORY=dpump_dir
   TRANSPORT_DATAFILES=
   F:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST9\TEST3.DBF


No comments:

Post a Comment