课程第13次

secoug

Notice: This blog is written by Bo Tang.

1. select  * from dba_tablespaces;
   
2. 
   
3. select  t.table_name, t.tablespace_name
   
4. from dba_tables t where t.table_name like 'T0%';
   
5. 
   
6. select * from database_properties;
   
7. 
   
8. select  * from dba_role_privs  rp where rp.grantee='HR';
   
9. 
   
10. select * from dba_sys_privs sp where sp.grantee='HR';
    
11. 
    

复制代码

1. select  * from dba_segments  s
   
2. where s.segment_name='T05207_A';

复制代码

1. 
   
2. select * from dba_extents e where e.owner='HR' and e.segment_name='T05207_A';

复制代码

380090里头放的是4个比特的位图（9iR1之前放的是free-list）

1. select  t.segment_space_management from dba_tablespaces t where t.tablespace_name='USERS';

复制代码

1. select t.tablespace_name, t.segment_space_management from dba_tablespaces t
   
2. where t.tablespace_name in ( 'SYSTEM','UNDOTBS1' ,'TEMP', 'SYSAUX');
   
3. 
   

复制代码

pct_used(人为指定40%)

1. create tablespace tbs2 datafile  size 10M segment space management manual;
   

复制代码

1. select t.tablespace_name, t.segment_space_management from dba_tablespaces t
   
2. where t.tablespace_name in ( 'TBS2', 'TBS1');

复制代码

1. select  t.table_name, t.pct_free, t.pct_used, t.ini_trans
   
2.   from dba_tables t where t.owner='HR' and t.table_name  in ('T05207_B', 'T05207_C');

复制代码

1. SQL>  alter table t05207_b pctfree 20 pctused 50 ;
   
2. 
   
3. Table altered.
   
4. 
   
5. SQL> alter table t05207_c pctfree 20 pctused 50;
   
6. 
   
7. Table altered.
   

复制代码

得出推论：关注pct_free和ini_trans会跟调优有密切关系。调整块里头的行密度：

1. SQL> create table t05207_d( a char(200)) ;
   
2. 
   
3. Table created.
   
4. 
   
5. SQL> begin
   
6.   2   for i in 1..1000
   
7.   3   loop
   
8.   4    insert into t05207_d values(to_char(i));
   
9.   5   end loop;
   
10.   6   commit;
    
11.   7  end;
    
12.   8  /
    
13. 
    
14. PL/SQL procedure successfully completed.
    

复制代码

1. select  s.bytes/1024/1024 bytesM, s.blocks
   
2. from dba_segments s where s.owner='HR' and s.segment_name='T05207_D';

复制代码

1. alter table hr.t05207_d pctfree 80;
   
2. 
   
3. alter table hr.t05207_d move tablespace users;
   

复制代码

1.   
   
2. select  s.bytes/1024/1024 bytesM, s.blocks, s.tablespace_name
   
3. from dba_segments s where s.owner='HR' and s.segment_name='T05207_D';
   

复制代码

索引是没有pct_used概念的，索引的initrans应该是对应表的两倍。

1. select  i.index_name,
   
2.          i.ini_trans,
   
3.          i.pct_free      
   
4.   from dba_indexes i where i.owner='HR' and i.table_name in ('T05207_B' ,'T05207_C');

复制代码

1. select  * from  dba_segments s where s.owner='HR' and s.segment_name='TBIG';
   
2. 
   
3. select * from dba_extents e where e.owner='HR' and e.segment_name='TBIG';
   
4. 
   
5. 
   
6. create tablespace tbs3 datafile  '/u01/app/oracle/oradata/orcl/tbs3_a.dbf'size 10M ,
   
7.                      '/u01/app/oracle/oradata/orcl/tbs3_b.dbf' size 10M  uniform size 1M ;
   
8.                      
   
9. create tablespace tbs4 datafile size 5M blocksize 4096;
   
10. 
    
11. 
    
12. create bigfile tablespace tbs5 datafile size 5M autoextend on maxsize 4T;  
    
13. 
    
14. alter tablespace tbs4 add datafile size 5M ;
    
15. 
    
16. alter tablespace tbs5 add  datafile size 5M;

复制代码

查看用量：

1. select  sum(bytes)/1024/1024 from dba_data_files df
   
2. where df.tablespace_name='SYSTEM';
   
3. 
   
4. --346.25
   
5. --800
   
6. 
   
7. select  sum(bytes)/1024/1024 from dba_free_space fs
   
8. where fs.tablespace_name='SYSTEM';
   
9. 
   
10. --36.0625
    
11. --27.5625
    
12. 
    
13. 
    
14. select  800-27.5625   from dual;
    
15. 
    
16. select  sum(bytes)/1024/1024 from dba_data_files df
    
17. where df.tablespace_name='UNDOTBS1'
    
18. ;
    
19. 
    
20. --115
    
21. 
    
22. select sum(e.bytes)/1024/1024
    
23. from dba_undo_extents e
    
24. where e.tablespace_name='UNDOTBS1'  and e.status <> 'EXPIRED';
    
25. 
    
26. select  sum(bytes)/1024/1024 from dba_temp_files tf
    
27. where tf.tablespace_name='TEMP'
    
28. ;
    
29. 
    
30. --627
    
31. 
    
32. select  tfs.free_space/1024/1024
    
33. 
    
34.    from dba_temp_free_space  tfs
    
35.     where tfs.tablespace_name='TEMP';
    
36. --626

复制代码