(52-24)第40次：2016-01-19星期二

botang · 发表于 2016-1-20 09:46:25

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','SYSTEM','UNDOTBS1');
-----
select * from database_properties;
alter database default tablespace users;
select * from dba_extents e where e.owner='HR' and e.segment_name='T04209_UNAME';
select * from dba_segments s where s.owner='HR' and s.segment_name='T04209_UNAME';
---
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','SYSTEM','UNDOTBS1');
-----
select * from database_properties;
alter database default tablespace users;
select * from dba_extents e where e.owner='HR' and e.segment_name='T04209_UNAME_ORI';
select * from dba_segments s where s.owner='HR' and s.segment_name='T04209_UNAME_ORI';
select * from dba_tables t where t.owner='HR' and t.table_name='T04209_UNAME_ORI';
---
select * from dba_extents e where e.owner='HR' and e.segment_name='T04209_UNAME';
---
CREATE SMALLFILE TABLESPACE "TBS05207_B"
DATAFILE '+DATA' SIZE 10M AUTOEXTEND ON NEXT 512K MAXSIZE 500M
LOGGING EXTENT MANAGEMENT DICTIONARY
DEFAULT STORAGE ( INITIAL 64K NEXT 64K MAXEXTENTS UNLIMITED PCTINCREASE 100);
----
select * from dba_tables t where t.owner='HR' and t.table_name='T04209_UNAME';
alter table hr.t04209_uname move tablespace tbs05207_b;
select * from dba_segments s where s.owner='HR' and s.segment_name='T04209_UNAME';
alter table hr.t04209_uname pctfree 20 pctused 50;
alter table hr.t04209_uname move tablespace tbs05207_a;
alter table hr.t04209_uname pctfree 15 pctused 55;
----
alter table hr.t04209_uname pctfree 95 ;
-----
alter table hr.t04209_uname initrans 20;
---
select * from dba_indexes i where i.owner='HR' and i.table_name='EMPLOYEES';
select t.pct_free ,t.pct_used , t.pct_increase
from dba_tables t where t.owner='HR' and t.table_name='T04209_UNAME';
select i.pct_free , i.pct_increase, i.pct_threshold ,i.tablespace_name
from dba_indexes i where i.owner='HR' and i.table_name='T04209_UNAME';
alter index hr.i04209_uname_1 rebuild tablespace tbs05207_b;
----
select
( (select sum(d.bytes)
from dba_data_files d where d.tablespace_name='TEMP')-
nvl((select sum(d.bytes)
from dba_free_space d where d.tablespace_name='TEMP'),0))/
(select sum(d.bytes)
from dba_data_files d where d.tablespace_name='TEMP')
from dual;
---
select sum(e.bytes)/1024/1024
from dba_undo_extents e where e.status <> 'EXPIRED';
select sum(d.bytes)/1024/1024
from dba_data_files d where d.tablespace_name='UNDOTBS1';
---
select sum(d.bytes)/1024/1024
from dba_temp_files d where d.tablespace_name='TEMP';
select sum(p.BYTES_USED)/1024/1024
from v_$temp_extent_pool p ;
---
select * from dba_tables t where t.tablespace_name='TBS05207_A';
alter table hr.t04209_uname add constraint p1 primary key ( uname ) ;
select * from dba_constraints c where c.owner='HR' and c.table_name='T04209_UNAME_ORI';
drop tablespace tbs05207_a including contents and datafiles cascade constraints ;
---
create bigfile tablespace tbs05207_big datafile size 10M
autoextend on maxsize 4T ;
select * from dba_tablespaces;
create tablespace tbs05207_c
datafile size 10M blocksize 32768;

复制代码