Bo's Oracle Station

查看: 2652|回复: 0

第36次活动:2017-11-27(星期一晚上7:00-9:30)

[复制链接]

1005

主题

1469

帖子

1万

积分

管理员

Rank: 9Rank: 9Rank: 9

积分
12012
发表于 2017-11-27 19:40:12 | 显示全部楼层 |阅读模式
我们已经无法创建:Failed to commit: ORA-12913: Cannot create dictionary managed tablespace。因为 system表空间已经是本地管理的。

我们无法创建:Failed to commit: ORA-03206: maximum file size of (536870912) blocks in AUTOEXTEND clause is out of range。因为没写bigfile。

  1. create tablespace tbs1 datafile size 20M
  2.   extent management dictionary;
  3.   
  4.   select  * from dba_extents e
  5.    where e.owner='HR' and e.segment_name='T04209_UNAME';
  6.       
  7.   select * from dba_tablespaces;
  8.   
  9.   truncate table hr.t_big;
  10.   
  11.   select  segment_name, bytes/1024/1024
  12.     from dba_segments s where s.tablespace_name='USERS'
  13.     order by bytes desc;
  14.    
  15.       
  16.   select   sum(bytes)/1024/1024
  17.     from dba_segments s where s.tablespace_name='USERS'
  18.    ;
  19.   
  20. alter database datafile '+data/orcl/datafile/users.259.816169553'   resize 500M ;

  21. alter tablespace users online;

  22. select * from dba_tablespaces;

  23.    select  * from dba_extents e
  24.    where e.owner='HR' and e.segment_name='T04209_UNAME';
  25.    
  26.    ----
  27.    
  28. select  * from dba_extents e where e.owner='HR' and e.segment_name='T04209_UNAME';
  29.    
  30. select  * from dba_segments s where s.owner='HR' and s.segment_name='T04209_UNAME';


  31. create table hr.t05207_a ( a  number )  
  32.   storage ( initial 512K  next 512K  ) pctfree 20 pctused 50
  33.   initrans 2 ;
  34.   
  35.   create table hr.t05207_b ( a  number )  ;
  36.   
  37.   create tablespace tbs2 datafile '/u01/app/oracle/oradata/orcl/tbs2.dbf'
  38.    size 5M  segment space management manual;
  39.    
  40.    create table hr.t05207_c ( a number) tablespace tbs2;
  41.    
  42.    select  * from dba_tables t where
  43.     t.table_name  like 'T05207%';
  44.    
  45.     create index hr.i05207_a  on hr.t05207_a ( a )  pctfree 20 initrans 4;
  46.    
  47.     select  * from dba_indexes i where
  48.     i.table_name  like 'T05207%';
  49.    
  50.     select  * from dba_extents e where e.owner='HR' and e.segment_name='T04209_UNAME';
复制代码
  1. select  f.tablespace_name , sum(f.bytes)/1024/1024
  2. from dba_data_files f
  3.   where f.tablespace_name in ('SYSTEM', 'EXAMPLE','UNDOTBS1')
  4.   group by f.tablespace_name;
  5.   

  6.   
  7.   select  f.tablespace_name , sum(f.bytes)/1024/1024
  8. from dba_temp_files f
  9.    group by f.tablespace_name;
  10.    
  11.    
  12.     select  f.tablespace_name , sum(f.bytes)/1024/1024
  13. from dba_free_space f
  14.   where f.tablespace_name in ('SYSTEM', 'EXAMPLE','UNDOTBS1','TEMP')
  15.   group by f.tablespace_name;
  16.   
  17.   select  sum(e.bytes)/1024/1024
  18.    from dba_undo_extents e
  19.    where e.tablespace_name='UNDOTBS1'   and e.status <> 'EXPIRED';
  20.    
  21.    select sum(p.BYTES_CACHED)/1024/1024, sum(p.BYTES_USED)/1024/1024
  22.     from v_$temp_extent_pool  p
  23.     where p.TABLESPACE_NAME='TEMP';
复制代码
  1. grant connect to user1 identified by oracle_4U;

  2. grant create session to user2 identified by oracle_4U;

  3. create user user3 identified by oracle_4U ;

  4. grant create session to user3;

  5. grant create table to user3;

  6. alter user user3 quota 1M on users;

  7. select  * from dba_ts_quotas;

  8. alter user user3 quota 0 on users;
复制代码


回复

使用道具 举报

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

本版积分规则

QQ|手机版|Bo's Oracle Station   

GMT+8, 2024-3-28 16:10 , Processed in 0.043730 second(s), 24 queries .

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