第29次：2015-03-31星期二

botang · 发表于 2015-4-1 09:58:47

2015-03-31-a.sql:

select * from dba_tables t where t.owner='HR' and t.table_name='EMPLOYEES';
select * from dba_segments s where s.owner='HR' and s.segment_name='EMPLOYEES';
select * from dba_extents e where e.owner='HR' and e.segment_name='EMPLOYEES';
-----
select * from dba_tablespaces t where t.tablespace_name in ('EXAMPLE','TEMP','UNDOTBS1','SYSTEM');
-----
create tablespace tbs05207;
select * from dba_data_files f where f.tablespace_name='TBS05207';
select * from dba_tablespaces t where t.tablespace_name='TBS05207';
create tablespace tbs05207_b segment space management manual;
select * from dba_data_files f where f.tablespace_name='TBS05207_B';
select * from dba_tablespaces t where t.tablespace_name='TBS05207_B';
---------
select * from dba_tables t where t.owner='HR' and t.table_name like 'T05207%';
select * from dba_indexes i where i.owner='HR' and i.table_name like 'T05207%';
------

复制代码

2015-03-31-b.sql

select * from dba_tablespaces t where t.tablespace_name='SYSTEM';
create bigfile tablespace tbs05207_uniform
datafile '+DATA' size 50M autoextend on
next 1M maxsize 16T
uniform size 1M;
alter tablespace users add datafile '+DATA' size 30M autoextend on ;
select * from dba_extents e where e.owner='HR' and e.segment_name='T04209_UNAME';
select * from database_properties;
alter database default tablespace tbs05207_uniform ;
------
select sum(f.bytes)/1024/1024
from dba_data_files f where f.tablespace_name='UNDOTBS1';
-----
select sum(s.bytes)/1024/1024
from dba_segments s where s.tablespace_name='EXAMPLE';
-----
select
(select sum(f.bytes)/1024/1024
from dba_data_files f where f.tablespace_name='UNDOTBS1')-
(select sum(fs.bytes)/1024/1024
from dba_free_space fs where fs.tablespace_name='UNDOTBS1')
from dual;
----
-----
select sum( ue.bytes)/1024/1024
from dba_undo_extents ue
where ue.status <> 'EXPIRED';
----
select sum(f.bytes)/1024/1024
from dba_temp_files f where f.tablespace_name='TEMP';
select * from v$temp_extent_pool;
---
drop tablespace tbs05207_uniform including contents and datafiles cascade constraints ;
alter database default tablespace users;
select * from dba_Tablespaces;
alter tablespace users rename to users1;
select * from database_properties;
alter tablespace users1 rename to users;

复制代码