第36次活动：2017-11-27（星期一晚上7:00-9:30）
我们已经无法创建：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。
create tablespace tbs1 datafile size 20M
extent management dictionary;
select* from dba_extents e
where e.owner='HR' and e.segment_name='T04209_UNAME';
select * from dba_tablespaces;
truncate table hr.t_big;
selectsegment_name, bytes/1024/1024
from dba_segments s where s.tablespace_name='USERS'
order by bytes desc;
select sum(bytes)/1024/1024
from dba_segments s where s.tablespace_name='USERS'
;
alter database datafile '+data/orcl/datafile/users.259.816169553' resize 500M ;
alter tablespace users online;
select * from dba_tablespaces;
select* from dba_extents e
where e.owner='HR' and e.segment_name='T04209_UNAME';
----
select* from dba_extents e where e.owner='HR' and e.segment_name='T04209_UNAME';
select* from dba_segments s where s.owner='HR' and s.segment_name='T04209_UNAME';
create table hr.t05207_a ( anumber )
storage ( initial 512Knext 512K) pctfree 20 pctused 50
initrans 2 ;
create table hr.t05207_b ( anumber );
create tablespace tbs2 datafile '/u01/app/oracle/oradata/orcl/tbs2.dbf'
size 5Msegment space management manual;
create table hr.t05207_c ( a number) tablespace tbs2;
select* from dba_tables t where
t.table_namelike 'T05207%';
create index hr.i05207_aon hr.t05207_a ( a )pctfree 20 initrans 4;
select* from dba_indexes i where
i.table_namelike 'T05207%';
select* from dba_extents e where e.owner='HR' and e.segment_name='T04209_UNAME';
selectf.tablespace_name , sum(f.bytes)/1024/1024
from dba_data_files f
where f.tablespace_name in ('SYSTEM', 'EXAMPLE','UNDOTBS1')
group by f.tablespace_name;
selectf.tablespace_name , sum(f.bytes)/1024/1024
from dba_temp_files f
group by f.tablespace_name;
selectf.tablespace_name , sum(f.bytes)/1024/1024
from dba_free_space f
where f.tablespace_name in ('SYSTEM', 'EXAMPLE','UNDOTBS1','TEMP')
group by f.tablespace_name;
selectsum(e.bytes)/1024/1024
from dba_undo_extents e
where e.tablespace_name='UNDOTBS1' and e.status <> 'EXPIRED';
select sum(p.BYTES_CACHED)/1024/1024, sum(p.BYTES_USED)/1024/1024
from v_$temp_extent_poolp
where p.TABLESPACE_NAME='TEMP';
grant connect to user1 identified by oracle_4U;
grant create session to user2 identified by oracle_4U;
create user user3 identified by oracle_4U ;
grant create session to user3;
grant create table to user3;
alter user user3 quota 1M on users;
select* from dba_ts_quotas;
alter user user3 quota 0 on users;
页:
[1]