Bo's Oracle Station

查看: 2770|回复: 0

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

[复制链接]

1005

主题

1469

帖子

1万

积分

管理员

Rank: 9Rank: 9Rank: 9

积分
12012
发表于 2016-1-20 09:46:25 | 显示全部楼层 |阅读模式
  1. select  * from dba_tables t where t.owner='HR' and t.table_name='EMPLOYEES';

  2. select  * from dba_segments s where s.owner='HR' and s.segment_name='EMPLOYEES';

  3. select  * from dba_extents e where e.owner='HR' and e.segment_name='EMPLOYEES';

  4. select  * from dba_tablespaces t where t.tablespace_name in ('EXAMPLE','TEMP','SYSTEM','UNDOTBS1');

  5. -----
  6. select  * from database_properties;

  7. alter database default tablespace users;

  8. select  * from dba_extents e where e.owner='HR' and e.segment_name='T04209_UNAME';
  9. select  * from dba_segments s where s.owner='HR' and s.segment_name='T04209_UNAME';

  10. ---

  11. select  * from dba_tables t where t.owner='HR' and t.table_name='EMPLOYEES';

  12. select  * from dba_segments s where s.owner='HR' and s.segment_name='EMPLOYEES';

  13. select  * from dba_extents e where e.owner='HR' and e.segment_name='EMPLOYEES';

  14. select  * from dba_tablespaces t where t.tablespace_name in ('EXAMPLE','TEMP','SYSTEM','UNDOTBS1');

  15. -----
  16. select  * from database_properties;

  17. alter database default tablespace users;

  18. select  * from dba_extents e where e.owner='HR' and e.segment_name='T04209_UNAME_ORI';
  19. select  * from dba_segments s where s.owner='HR' and s.segment_name='T04209_UNAME_ORI';
  20. select  * from dba_tables t where t.owner='HR' and t.table_name='T04209_UNAME_ORI';

  21. ---
  22. select  * from dba_extents e where e.owner='HR' and e.segment_name='T04209_UNAME';

  23. ---
  24. CREATE SMALLFILE TABLESPACE "TBS05207_B"
  25.   DATAFILE '+DATA' SIZE 10M AUTOEXTEND ON NEXT 512K MAXSIZE 500M
  26.     LOGGING EXTENT MANAGEMENT DICTIONARY
  27.     DEFAULT STORAGE ( INITIAL 64K NEXT 64K MAXEXTENTS UNLIMITED PCTINCREASE 100);
  28.    
  29. ----   
  30. select  * from dba_tables t where t.owner='HR' and t.table_name='T04209_UNAME';
  31. alter table  hr.t04209_uname move tablespace tbs05207_b;
  32. select  * from dba_segments s where s.owner='HR' and s.segment_name='T04209_UNAME';


  33. alter table hr.t04209_uname pctfree 20  pctused 50;
  34. alter table  hr.t04209_uname move tablespace tbs05207_a;
  35. alter table hr.t04209_uname pctfree 15  pctused 55;


  36. ----
  37. alter table hr.t04209_uname pctfree 95 ;

  38. -----

  39. alter table hr.t04209_uname  initrans 20;
  40. ---
  41. select  * from dba_indexes i where i.owner='HR' and i.table_name='EMPLOYEES';

  42. select   t.pct_free  ,t.pct_used  , t.pct_increase   
  43.    from dba_tables t where t.owner='HR' and t.table_name='T04209_UNAME';
  44.    
  45. select       i.pct_free , i.pct_increase, i.pct_threshold  ,i.tablespace_name   
  46.   from dba_indexes i where i.owner='HR' and i.table_name='T04209_UNAME';
  47.   
  48. alter index hr.i04209_uname_1   rebuild tablespace  tbs05207_b;
  49. ----
  50. select  
  51.   ( (select  sum(d.bytes)
  52.     from dba_data_files d where d.tablespace_name='TEMP')-   
  53.     nvl((select   sum(d.bytes)
  54.       from dba_free_space d where d.tablespace_name='TEMP'),0))/      
  55. (select  sum(d.bytes)
  56.     from dba_data_files d where d.tablespace_name='TEMP')
  57.    
  58.     from dual;
  59. ---
  60. select   sum(e.bytes)/1024/1024
  61.   from dba_undo_extents e where  e.status <> 'EXPIRED';

  62. select  sum(d.bytes)/1024/1024
  63.     from dba_data_files d where d.tablespace_name='UNDOTBS1';
  64. ---
  65.     select  sum(d.bytes)/1024/1024
  66.     from dba_temp_files d where d.tablespace_name='TEMP';
  67.    
  68.     select  sum(p.BYTES_USED)/1024/1024
  69.       from v_$temp_extent_pool    p ;
  70.       
  71.       ---
  72.       
  73. select * from dba_tables t where t.tablespace_name='TBS05207_A';

  74. alter table hr.t04209_uname add constraint  p1  primary key (  uname )   ;


  75. select  * from dba_constraints c where c.owner='HR' and c.table_name='T04209_UNAME_ORI';


  76. drop tablespace tbs05207_a   including contents   and datafiles   cascade constraints ;

  77. ---

  78. create  bigfile   tablespace tbs05207_big  datafile  size 10M
  79.   autoextend  on maxsize 4T ;
  80.   
  81.   select * from dba_tablespaces;
  82.   
  83.   
  84.   create  tablespace tbs05207_c
  85.     datafile size 10M   blocksize 32768;



复制代码


回复

使用道具 举报

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

本版积分规则

QQ|手机版|Bo's Oracle Station   

GMT+8, 2024-3-29 21:34 , Processed in 0.035807 second(s), 24 queries .

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