课程第53次（2018-05-30星期三）

botang

1. [oracle@station90 ~]$ sqlplus /nolog
   
2. 
   
3. SQL*Plus: Release 12.1.0.2.0 Production on Wed May 30 19:19:52 2018
   
4. 
   
5. Copyright (c) 1982, 2014, Oracle.  All rights reserved.
   
6. 
   
7. SQL> conn sys/oracle_4U@pdb2 as sysdba
   
8. Connected.
   
9. SQL> conn hr/oracle_4U@pdb2
   
10. Connected.
    
11. SQL> create table t05207_m ( a number )  tablespace tbs05207_m ;
    
12. 
    
13. Table created.
    
14. 
    
15. SQL> insert into t05207_m values ( 1 ) ;
    
16. insert into t05207_m values ( 1 )
    
17.             *
    
18. ERROR at line 1:
    
19. ORA-01950: no privileges on tablespace 'TBS05207_M'
    
20. 
    
21. 
    
22. SQL> insert into t05207_m values ( 1 ) ;
    
23. 
    
24. 1 row created.
    
25. 
    
26. SQL> commit;
    
27. 
    
28. Commit complete.
    
29. 
    
30. SQL> create table t05207_a ( a number )  tablespace users;
    
31. 
    
32. Table created.
    
33. 
    
34. SQL> insert into t05207_a  values (1) ;
    
35. 
    
36. 1 row created.
    
37. 
    
38. SQL> rollback;
    
39. 
    
40. Rollback complete.
    
41. 
    
42. SQL> create index i05207_a on t05207_A (a ) ;
    
43. 
    
44. Index created.
    
45. 
    
46. SQL> create index i05207_m on t05207_m (a ) ;
    
47. 
    
48. Index created.
    
49. 
    
50. SQL>
    

复制代码

表空间的基本查询语句：

1. select * from cdb_tablespaces t
   
2. where t.segment_space_management <> 'MANUAL';
   
3. 
   
4. select * from cdb_tablespaces t
   
5. where t.segment_space_management = 'MANUAL';
   
6. 
   
7. create tablespace tbs05207_m datafile size 10M segment space management manual;
   
8. 
   
9. alter user hr quota 10M on tbs05207_m;
   
10. 
    
11. select  * from cdb_segments s where s.owner='HR' and s.segment_name='T05207_M';
    
12. 
    
13. ---header block is 128
    
14. 
    
15. select  * from cdb_extents s where s.owner='HR' and s.segment_name='T05207_M';
    
16. 
    
17. ---first block is 128
    
18. 
    
19. select  * from cdb_tables t where t.owner='HR' and t.table_name='T05207_M';
    
20. 
    
21. select  * from cdb_ts_quotas ts where ts.tablespace_name='USERS';
    
22. 
    
23. --------------------------------
    
24. select  * from cdb_segments s where s.owner='HR' and s.segment_name='T05207_A';
    
25. ---header block is 338
    
26. 
    
27. select  * from cdb_extents s where s.owner='HR' and s.segment_name='T05207_A';
    
28. ---first block is 336
    
29.    
    
30. select  * from cdb_tables t where t.owner='HR' and t.table_name='T05207_A';
    

复制代码

得出结论：表上pct_free判断：是不是满块。
                 pct_used判断：是不是空块，也就是buffer busy wait等待事件等的东西，用来判断能不能插入。 segment space management 为auto的表空间上不能设。 segment space management 为manual的表空间上默认40%。
                索引上能设置pct_free ? 有。 pct_used ? 绝对没有！！！！

1. alter index hr.i05207_m rebuild tablespace tbs05207_m;
   
2. 
   
3. select  * from cdb_indexes i
   
4.    where i.owner='HR' and i.index_name in ('I05207_A', 'I05207_M');

复制代码

1. SQL> create table t05207_b ( a  number ) initrans 10 ;
   
2. 
   
3. Table created.
   
4. 
   
5. SQL> create index i05207_b on t05207_b (a) initrans 20 ;
   
6. 
   
7. Index created.
   
8. 
   
9. SQL>
   

复制代码

1. select  * from cdb_tables t where t.owner='HR' and t.table_name='T05207_B';
   
2. 
   
3. select  * from cdb_indexes i
   
4.    where i.owner='HR' and i.index_name ='I05207_B';

复制代码

公共关联的表（同时多个应用要读取），极端的做法是（分散数据）：

1. alter table t05207_b  pctfree 90 ;
   
2. alter table t05207_b move tablespace users;
   

复制代码

1. SQL> alter index i05207_b rebuild pctfree 90   online ;

复制代码

关于 uniform size 512K

1. select * from cdb_tablespaces t
   
2. where t.allocation_type <> 'SYSTEM';
   
3. 
   
4. select * from cdb_tablespaces t
   
5. where t.allocation_type = 'SYSTEM';
   
6. 
   
7. select  * from database_properties;
   
8. 
   
9. alter database default tablespace users;
   
10. 
    
11. select  * from cdb_extents e where e.owner='HR' and e.segment_name='T04209_UNAME';
    
12. 
    
13. alter user   hr  quota unlimited on ldata;
    
14. 
    
15. alter table hr.t04209_uname  move tablespace ldata;
    

复制代码

1. ---cdb2
   
2. alter system set db_16k_cache_size =1M;
   
3. 
   
4. create tablespace tbs16k datafile '/u01/app/oracle/oradata/cdb2/pdb2_1/tbs16k.dbf'
   
5.   size 10M     autoextend on    next 2M   maxsize 4G     uniform size 2M
   
6. blocksize 16k ;
   
7. 
   
8. create  bigfile  tablespace tbsbig datafile size  10M autoextend on  maxsize 4T  ;

复制代码

1. 
   
2. select  
   
3. (  (select sum(bytes) from cdb_data_files  d
   
4. where d.tablespace_name='USERS')  -
   
5. 
   
6. (select sum(bytes) from cdb_free_space f
   
7.   where f.tablespace_name='USERS')  )  /
   
8. 
   
9. (select sum(bytes) from cdb_data_files  d
   
10. where d.tablespace_name='USERS')  
    
11. 
    
12.   from dual;

复制代码

1. select  
   
2. (select sum(bytes) from cdb_undo_extents e
   
3.   where e.status<>'EXPIRED')   /
   
4. 
   
5. (select sum(bytes) from cdb_data_files  d
   
6. where d.tablespace_name='UNDOTBS1')  
   
7. 
   
8.   from dual;

复制代码

1.   select  
   
2. (select  sum(ep.bytes_used+ep.bytes_cached) from v_$temp_extent_pool  ep
   
3.   )   /
   
4. (select sum(bytes) from cdb_temp_files  d
   
5. where d.tablespace_name='TEMP_PDB2')  
   
6. 
   
7.   from dual;
   
8. 
   
9. 
   
10. 或者：
    
11. 
    
12.   select  
    
13. (select  sum(ep.bytes_used) from v_$temp_extent_pool  ep
    
14.   )   /
    
15. (select sum(bytes) from cdb_temp_files  d
    
16. where d.tablespace_name='TEMP_PDB2')  
    
17. 
    
18.   from dual;
    
19. 
    

复制代码