Rising Sun

// tnsnames.ora
本地網絡服務名配置 --> 添加 --> 服務名(gis) --> 主機名(電腦名) --
// 創建表空間
create?tablespace?tran
datafile?'D:\Oracle\oradata\orcl\tran.dbf'
size?10M;
// 用戶
create?user?tran?identified?by?tran
default ?tablespace?tran;

// 授權成功。
grant?connect?,resource?to?tran;

// 連接。
?connect?tran / tran

// 表已創建。
create?table?test?as?select? * ?from?user_objects;


// 表空間已刪除
SQL > ?drop?tablespace?tran
?? 2 ??;
drop?tablespace?tran
*
第? 1 ?行出現錯誤:
ORA - 01549 :?表空間非空,?請使用?INCLUDING?CONTENTS?選項


SQL > ?drop?tablespace?tran?including?contents
?? 2 ??;

表空間已刪除。

// SQL>?drop?user?tran;


用戶已刪除。




// //////////////
ORACLE應用經驗( 5 ) - 表空間
二、查看有哪些表空間
svrmgrl > ?SELECT? * ?FROM?DBA_TABLESPACES;
?????????SYSTEM???RBS?????TEMP?????TOOLS????USERS
三、將USERS表空間DROP
svrmgrl > ?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.3672
DD_IDX????????????????????????? 787.18164
JX_DATA???????????????????????? 827.94531
JX_IDX????????????????????????? 503.16016
RBS????????????????????????????? 371.9668
SYSTEM????????????????????????? 457.81445
TEMP??????????????????????????? 1499.9961
TOOLS?????????????????????????? 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 / drd4
RBS?????????????????????????????????? 500 ? / dev / rdrd / drd14
RBS????????????????????????????????? 1000 ? / dev / rdrd / drd15
RBS?????????????????????????????????? 500 ? / dev / rdrd / drd32
TOOLS????????????????????????????????? 50 ? / dev / rdrd / drd5
TEMP???????????????????????????????? 1000 ? / dev / rdrd / drd22
TEMP????????????????????????????????? 500 ? / dev / rdrd / drd23
JX_DATA?????????????????????????????? 500 ? / dev / rdrd / drd33

六、對應SYSTEM表空間有一個回退段，為SYSTEM，另有一些回退段是屬于RBS的，
????先將RBS下的回退段都OFFLINE，并DROP，然后將RBS表空間DROP并重新創建，
????最后，創建回退段?；赝硕?個，每個大小為RBS / 4 ，這個值可以當作OPTIMAL值，
????即等于INITIAL + NEXT * MAXEXTENTS

svrmgrl > ?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，要ONLINE
svrmgrl > ?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_IDX

svrmgrl > ?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? " ******* " ;