【Oracle】实验三 Oracle数据库逻辑存储结构管理

-- 1 
create tablespace booktbs1 datafile 'E:\Oracle\Oracle11gapp\admin\booksales\booktbs1.dbf' size 50m;
-- 2
create tablespace booktbs2 datafile 'E:\Oracle\Oracle11gapp\admin\booksales\booktbs2.dbf' size 50m
extent management local  uniform size 1m segment  space management manual;
-- 3
create temporary tablespace temp02 tempfile 'E:\Oracle\Oracle11gapp\admin\booksales\temp02.dbf' size 20m
extent management local uniform size 15m;
-- 4
alter tablespace temp  tablespace group tempgroup;
alter tablespace temp tablespace group tempgroup;
--5
create undo tablespace undo02 datafile 'E:\Oracle\Oracle11gapp\admin\booksales\undo02.dbf' size 10m;
alter system set undo_tablespace = undo02;
-- 6
alter tablespace booktbs1 add datafile 'E:\Oracle\Oracle11gapp\admin\booksales\booktbs11.dbf' size 50m;
-- 7
alter database datafile 'E:\Oracle\Oracle11gapp\admin\booksales\booktbs2.dbf' 
autoextend on next 5m maxsize 100m;
-- 8
create table test(id number primary key,name char(20)) tablespace booktbs1;
insert into test values(1,'first row');
-- 9
alter tablespace booktbs1 offline;
select tablespace_name,status from dba_tablespaces;
-- 10
alter tablespace booktbs1 online;
select tablespace_name,status from dba_tablespaces;
-- 11
alter tablespace booktbs1 read only;
select tablespace_name,status from dba_tablespaces;
-- 12
alter tablespace booktbs1 read write;
select tablespace_name,status from dba_tablespaces;
-- 13
alter database default tablespace booktbs1;
alter database default temporary tablespace tempgroup;
-- 14
-- 进入归档模式
shutdown immediate;
startup mount;
alter database archivelog;
archive log list
alter database open;

alter tablespace users begin backup;
host copy  E:\ORACLE\ORACLE11GAPP\ORADATA\BOOKSALES\USERS01.DBF E:\Oracle\Oracle11gapp\oradata\booksales\backup;
alter tablespace users end backup;

alter tablespace booktbs1 begin backup;
host copy  E:\ORACLE\ORACLE11GAPP\ADMIN\BOOKSALES\BOOKTBS1.DBF E:\Oracle\Oracle11gapp\oradata\booksales\backup;
alter tablespace booktbs1 end backup;

alter tablespace booktbs3 begin backup;
host copy  E:\ORACLE\ORACLE11GAPP\ADMIN\BOOKSALES\BOOKTBS3.DBF E:\Oracle\Oracle11gapp\oradata\booksales\backup;
alter tablespace booktbs1 end backup;
-- 15
select tablespace_name,status from dba_tablespaces;
-- 16
select tablespace_name,file_id,bytes/1024/1024,file_name from dba_data_files order by file_id;
-- 17
drop tablespace booktbs2 including contents and datafiles;