设为首页收藏本站

Botang唐波's Oracle Station

查看: 221|回复: 0

课程第53次(2018-05-30星期三)

[复制链接]

708

主题

1070

帖子

7759

积分

管理员

Rank: 9Rank: 9Rank: 9

积分
7759
发表于 2018-5-30 19:45:33 | 显示全部楼层 |阅读模式
  1. [oracle@station90 ~]$ sqlplus /nolog

  2. SQL*Plus: Release 12.1.0.2.0 Production on Wed May 30 19:19:52 2018

  3. Copyright (c) 1982, 2014, Oracle.  All rights reserved.

  4. SQL> conn sys/oracle_4U@pdb2 as sysdba
  5. Connected.
  6. SQL> conn hr/oracle_4U@pdb2
  7. Connected.
  8. SQL> create table t05207_m ( a number )  tablespace tbs05207_m ;

  9. Table created.

  10. SQL> insert into t05207_m values ( 1 ) ;
  11. insert into t05207_m values ( 1 )
  12.             *
  13. ERROR at line 1:
  14. ORA-01950: no privileges on tablespace 'TBS05207_M'


  15. SQL> insert into t05207_m values ( 1 ) ;

  16. 1 row created.

  17. SQL> commit;

  18. Commit complete.

  19. SQL> create table t05207_a ( a number )  tablespace users;

  20. Table created.

  21. SQL> insert into t05207_a  values (1) ;

  22. 1 row created.

  23. SQL> rollback;

  24. Rollback complete.

  25. SQL> create index i05207_a on t05207_A (a ) ;

  26. Index created.

  27. SQL> create index i05207_m on t05207_m (a ) ;

  28. Index created.

  29. SQL>
复制代码
表空间的基本查询语句:
  1. select * from cdb_tablespaces t
  2. where t.segment_space_management <> 'MANUAL';

  3. select * from cdb_tablespaces t
  4. where t.segment_space_management = 'MANUAL';

  5. create tablespace tbs05207_m datafile size 10M segment space management manual;

  6. alter user hr quota 10M on tbs05207_m;

  7. select  * from cdb_segments s where s.owner='HR' and s.segment_name='T05207_M';

  8. ---header block is 128

  9. select  * from cdb_extents s where s.owner='HR' and s.segment_name='T05207_M';

  10. ---first block is 128

  11. select  * from cdb_tables t where t.owner='HR' and t.table_name='T05207_M';

  12. select  * from cdb_ts_quotas ts where ts.tablespace_name='USERS';

  13. --------------------------------
  14. select  * from cdb_segments s where s.owner='HR' and s.segment_name='T05207_A';
  15. ---header block is 338

  16. select  * from cdb_extents s where s.owner='HR' and s.segment_name='T05207_A';
  17. ---first block is 336
  18.    
  19. 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. select  * from cdb_indexes i
  3.    where i.owner='HR' and i.index_name in ('I05207_A', 'I05207_M');
复制代码

  1. SQL> create table t05207_b ( a  number ) initrans 10 ;

  2. Table created.

  3. SQL> create index i05207_b on t05207_b (a) initrans 20 ;

  4. Index created.

  5. SQL>
复制代码
  1. select  * from cdb_tables t where t.owner='HR' and t.table_name='T05207_B';

  2. select  * from cdb_indexes i
  3.    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. select * from cdb_tablespaces t
  4. where t.allocation_type = 'SYSTEM';

  5. select  * from database_properties;

  6. alter database default tablespace users;

  7. select  * from cdb_extents e where e.owner='HR' and e.segment_name='T04209_UNAME';

  8. alter user   hr  quota unlimited on ldata;

  9. alter table hr.t04209_uname  move tablespace ldata;
复制代码
  1. ---cdb2
  2. alter system set db_16k_cache_size =1M;

  3. create tablespace tbs16k datafile '/u01/app/oracle/oradata/cdb2/pdb2_1/tbs16k.dbf'
  4.   size 10M     autoextend on    next 2M   maxsize 4G     uniform size 2M
  5. blocksize 16k ;

  6. create  bigfile  tablespace tbsbig datafile size  10M autoextend on  maxsize 4T  ;
复制代码

  1. select  
  2. (  (select sum(bytes) from cdb_data_files  d
  3. where d.tablespace_name='USERS')  -

  4. (select sum(bytes) from cdb_free_space f
  5.   where f.tablespace_name='USERS')  )  /

  6. (select sum(bytes) from cdb_data_files  d
  7. where d.tablespace_name='USERS')  

  8.   from dual;
复制代码
  1. select  
  2. (select sum(bytes) from cdb_undo_extents e
  3.   where e.status<>'EXPIRED')   /

  4. (select sum(bytes) from cdb_data_files  d
  5. where d.tablespace_name='UNDOTBS1')  

  6.   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.   from dual;


  7. 或者:

  8.   select  
  9. (select  sum(ep.bytes_used) from v_$temp_extent_pool  ep
  10.   )   /
  11. (select sum(bytes) from cdb_temp_files  d
  12. where d.tablespace_name='TEMP_PDB2')  

  13.   from dual;

复制代码







回复

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

QQ|手机版|Botang唐波's Oracle Station   

GMT+8, 2018-8-20 02:55 , Processed in 0.109242 second(s), 24 queries .

快速回复 返回顶部 返回列表