包括系统表空间system,辅助表空间sysaux,两个还原表空间undotbs1和undotbs2,三个临时表空间temp,temp02,temp03,数据表空间test01,test02,test03,test04,test06和两个用户表空间users和example.

SQL>create tablespace test01 datafile '/db/oradata/orcl/test01.dbf' size 10M uniform size 64K;

SQL>create tablespace test02 datafile '/db/oradata/orcl/test02.dbf' size 10M autoallocate;

SQL>create tablespace test03 datafile '/db/oradata/orcl/test03.dbf' size 10M reuse autoextend on next 500K maxsize 100M;

SQL>alter system set db_create_file_dest = '/db/oradata/orcl';

SQL>create tablespace test04;

SQL>select tablespace_name,file_name from dba_data_files;

SQL>create bigfile tablespace test06 datafile '/db/oradata/orcl/test06.dbf'

size 20M autoextend on;

SQL>create undo tablespace undotbs2 datafile '/db/oradata/orcl/undotbs2.dbf'

size 10M autoextend on retention noguarantee;

SQL>create temporary tablespace temp02

tempfile '/db/oradata/orcl/temp02.dbf' size 10M autoextend on;

SQL>create temporary teblespace temp03

tempfile 'db/oradata/orcl/temp03.dbf' size 10M autoextend on

tablespace group tbs_group1;

SQL>alter tablespace temp02 tablespace group tbs_group1;

SQL>alter tablespace temp02 tablespace group '';

SQL>select * from dba_tablespace_groups;

SQL>select tablespace_name,contents,status from dba_tablespaces;

***********表空间扩容

SQL>alter tablespace users add datafile '/db/oradata/orcl/users02.dbf' size 50M;

SQL>alter database datafile '/db/oradata/orcl/users02.dbf' resize 100M;

SQL>alter database datafile '/db/oradata/orcl/users02.dbf' autoextend on next 50M maxsize 1000M;

#lslv lv1 --一个裸设备的配置信息

#extendlv -a ie -ex lv1 472 hdisk5 hdisk6 --为lv1扩展空间

*****************表空间维护

SQL>show user;

SQL>alter tablespace users offline;

SQL>select tablespace_name ,status from dba_tablespace where tablespace_name='USERS';

SQL>show user

SQL>alter tablespace users online;

SQL>select file_name,tablespace_name from dba_data_files

where tablespace_name='USERS';

SQL>alter database datafile '/db/oradata/orcl/users01.dbf' offline;

SQL>alter database datafile '/db/oradata/orcl/users01.dbf' online;

SQL>recover datafile '/db/oradata/orcl/users01.dbf';

SQL>alter database datafile '/db/oradata/orcl/users01.dbf' online;

SQL>alter tablespace USERS read only;

SQL>alter tablespace USERS read write;

SQL>alter tablespace USERS RENAME TO USERS01;

SQL>drop tablespace test01 including contents and datafiles;

SQL>drop tablespace temp01 including contents and datafiles;

SQL>drop tablespace undotbs2 including contents and datafiles;

**************还可以在OEM中维护表空间

**********还原表空间管理

SQL>show parameter undo;

SQL>show parameter undo_tablespace;

SQL>create undo tablespace undotbs2 datafile '/db/oradata/orcl/undotbs02.dbf'

size 10M autoextend on;

SQL>alter system set undo_tablespace=undotbs2 scope=both;

SQL>drop tablespace undotbs1 uncluding contents and datafiles;

SQL>select tablespace_name from dba_tablespaces where contents='UNDO';

*********同样可以在OEM中管理还原表空间

********重做日志管理

牵涉到三方面:(1)日志组和日志成员的创建(2)日志组和日志成员的删除(3)日志切换和检查点

增加日志组前,需要考虑:

(1)日志文件的大小:10g,11g默认为50M,日志大小与用户业务系统的繁忙程序相关.日志的大小最好能承载数据库几十分钟的业务量.太小会造成日志组切换频繁;太大则导致系统恢复时间增长.

(2)日志组数目的多少:在发生日志切换时,一个可以使用的下一个日志组很重要.日志组少,可能会发生由于等待数据库归档等操作的完成,需要使用的下一个日志组不可用,造成性能故障.如果从告警文件内读到有checkpoint not complete等信息,表明日志组少。

SQL>col member format a30;

SQL>select a.group#,a.member,b.bytes/1024/1024 from v$logfile a,v$log b

where a.group# = b.group#; --显示３组

SQL>alter database add logfile 'db/oradata/orcl/redo04.log' size 50M; --添加组

SQL>alter database add logfile group 8

'db/oradata/orcl/redo08.log' size 50M;--加组