数据库维护常用操作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;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30018455/viewspace-1665104/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/30018455/viewspace-1665104/