ORACLE应用经验(5)-表空间

一、加入oracle下# su - oracleas4101> lsnrctl startas4101> svrmgrlsvrmgrl> connect internal;svrmgrl> shutdown immediate;svrmgrl> startup二、察看有哪些表空间svrmgrl> select * from dba_tablespaces; system rbs temp tools users三、将users表空间dropsvrmgrl> alter tablespace users offline;svrmgrl> drop tablespace users;四、察看表空间的空余巨细svrmgrl> select tablespace_name,sum(bytes)/1024/1024 mb from dba_free_space group by tablespace_name;tablespace_name mb------------------------------ ---------dd_data 1136.3672dd_idx 787.18164jx_data 827.94531jx_idx 503.16016rbs 371.9668system 457.81445temp 1499.9961tools 36.462891五、察看数据文献安置的路途svrmgrl> select tablespace_name,bytes/1024/1024 mb,file_name from dba_data_files;tablespace_name mb file_name------------------------------ --------- ---------------system 500 /dev/rdrd/drd4rbs 500 /dev/rdrd/drd14rbs 1000 /dev/rdrd/drd15rbs 500 /dev/rdrd/drd32tools 50 /dev/rdrd/drd5temp 1000 /dev/rdrd/drd22temp 500 /dev/rdrd/drd23jx_data 500 /dev/rdrd/drd33六、对应system表空间有一个回退段，为system，另有少许回退段是属于rbs的， 先将rbs下的回退段都offline，并drop，而后将rbs表空间drop并从新创造， 结果，创造回退段。回退段4个，每个巨细为rbs/4，这个值不妨看成optimal值， 即即是initial+next*maxextentssvrmgrl> alter rollback segment r01 offline;svrmgrl> drop rollback segment r01;svrmgrl> alter tablespace rbs offline;svrmgrl> drop tablespace rbs;svrmgrl> create tablespace "rbs" datafile '/dev/rdrd/rbs01.ora' size 500m, '/dev/rdrd/rbs02.ora' size 500m;svrmgrl> create rollback segment "r01" tablespace "rbs" storage ( initial 200m next 2m optimal 250m minextents 2 maxextents 25);七、察看回退段及表空间的状况,若为online，即中断，为offline，要onlinesvrmgrl> select segment_name,tablespace_name,status from dba_rollback_segs;svrmgrl> alter rollback segment r01 online;八、偶尔表空间temp，先drop，再重修。svrmgrl> alter tablespace temp offline;svrmgrl> drop tablespace temp;svrmgrl> create tablespace temp datafile '/dev/rdrd/drd22' size 1000m storage (initial 300m next 20m minextens 2 maxextents 35 pctincrease 0);九、东西表空间tools巨细为50m充满用，体例表空间system为100m充满用。十、创造数据表空间： dd_data、dd_idx、jx_data、jx_idx、sf_data、sf_idx、jf_data、jf_idxsvrmgrl> create tablespace dd_data datafile '/dev/rdrd/drd9' size 1000m, '/dev/rdrd/drd10' size 1000m, '/dev/rdrd/drd26' size 1000m, '/dev/rdrd/drd35' size 1000m, '/dev/rdrd/drd42' size 500m;十一、创造用户svrmgrl> create user ddbh identified by ddbh default tablespace dd_data temporary tablespace temp quota unlimited on dd_data quota unlimited on dd_idx quota unlimited on rbs quota unlimited on temp;十二、用户权力svrmgrl> grant connect,resources,imp_full_database,exp_full_database, create public synonym,drop public synonym to ddbh; 若要察看v$session，kill session, drop user,create user等,则svrmgrl> grant select on v_$session to public;svrmgrl> grant alter system,drop user,create user to "*******";