資料庫維護常用操作4--表空間操作

 

select* from dba_data_files;

1. 建立表空間:

SQL> create tablespace lifenfang datafile '/s01/oracle/oradata/test_1.dbf'  size 10M autoextend on next 50M;

Tablespace created.

2. 建立一個表使用這個表空間:

create table daodao_1 tablespace lifenfang  as select * from dba_objects ;

3. 查詢檔案大小:

SQL> select BYTES/1024/1024,MAXBYTES/1024/1024 from Dba_Data_Files where tablespace_name ='lifenfang';

select  segment_name,block_id,blocks FROM dba_extents where Tablespace_name ='LIFENFANG' order by block_id desc;

4. 監控表空間的大小

create or replace procedure p_monit_tbs_1  as 

 --監控表空間增量  

 vMessage         varchar(250); --下發簡訊  

 vDb_name         varchar2(10); --資料庫名  

 vTBS_FREE_GB     number; --剩餘表空間閥值  

EGIN 

 --制定監控的目標  

 vTBS_FREE_GB := 35; 

 --獲取資料庫名  

select name into vDb_name from v$database; 

Db_name:='NEW_'||vDb_name;

for x in (SELECT tablespace_name,(free_space_mb+free_allocate_mb)total_free_mb FROM dba_tablespace_free 

WHERE tablespace_name NOT IN ('OSS01_HIS_DATA','TEST1_DATA','GG_DATA','TEST_TBS1') ) loop 

 if (round(x.total_free_mb/1024,2)<= vTBS_FREE_GB) then 

     begin 

       vMessage := '[報警郵件] 資料庫' || vDb_name || 

                   '表空間'|| x.tablespace_name||'還剩'||x.total_free_mb|| 'MB'; 

       end; 

   end if; 

 end loop;

5. 檢視使用者所在表空間

select * from dba_data_files order by tablespace_name;

select * from dba_tablespaces;

select * from user_tables;----tablespace_name

select * from dba_file_groups;

6. 查詢剩餘表空間

SELECT t.segment_name,TO_CHAR(SUM(BYTES)/(1024*1024),'999G999D999') CNT_MB

FROM user_segments t

WHERE SEGMENT_TYPE LIKE 'TABLE%'

GROUP BY t.segment_name order by 2 desc;