Oracle管理-建立資料表空間和使用者授權

oracle建立資料表空間和使用者授權  

2011-04-18 13:49:47|  分類： 工作日誌 |  標籤： |字型大小大中小 訂閱

oracle建立資料表空間和使用者授權
文章分類:資料庫
SYS使用者在CMD下以DBA身份登陸：

在CMD中打sqlplus /nolog

然後再

conn / as sysdba

//建立暫存資料表空間 

 

create temporary tablespace user_temp 

tempfile 'D:\oracle\oradata\Oracle9i\user_temp.dbf'

size 50m 

autoextend on 

next 50m maxsize 20480m 

extent management local; 

 

//建立資料資料表空間 

create tablespace test_data 

logging 

datafile 'D:\oracle\oradata\Oracle9i\user_data.dbf'

size 50m 

autoextend on 

next 50m maxsize 20480m 

extent management local; 

 

//建立使用者並指定資料表空間 

create user username identified by password 

default tablespace user_data 

temporary tablespace user_temp; 

 

//給使用者授予許可權 

 

grant connect,resource to username; 

 

//以後以該使用者登入，建立的任何資料庫物件都屬於user_temp 和user_data資料表空間，這就不用在每建立一個對象給其指定資料表空間了

 

撤權： revoke   許可權...   from 使用者名稱;

刪除使用者命令

drop user user_name cascade;

 

建立資料表空間

CREATE TABLESPACE data01

DATAFILE '/oracle/oradata/db/DATA01.dbf' SIZE 500M

UNIFORM SIZE 128k; #指定區尺寸為128k,如不指定，區尺寸預設為64k

 

刪除資料表空間

DROP TABLESPACE data01 INCL ING CONTENTS AND DATAFILES;

 

一、建立資料表空間

CREATE TABLESPACE data01

DATAFILE '/oracle/oradata/db/DATA01.dbf' SIZE 500M

UNIFORM SIZE 128k; #指定區尺寸為128k,如不指定，區尺寸預設為64k

二、建立UNDO資料表空間

CREATE UNDO TABLESPACE UNDOTBS02

DATAFILE '/oracle/oradata/db/UNDOTBS02.dbf' SIZE 50M

#注意：在OPEN狀態下某些時刻只能用一個UNDO資料表空間，如果要用建立的資料表空間，必須切換到該資料表空間:

ALTER SYSTEM SET undo_tablespace=UNDOTBS02;

三、建立暫存資料表空間

CREATE TEMPORARY TABLESPACE temp_data

TEMPFILE '/oracle/oradata/db/TEMP_DATA.dbf' SIZE 50M

四、改變資料表空間狀態

1.使資料表空間離線

ALTER TABLESPACE game OFFLINE;

如果是意外刪除了資料檔案，則必須帶有RECOVER選項

ALTER TABLESPACE game OFFLINE FOR RECOVER;

2.使資料表空間聯機

ALTER TABLESPACE game ONLINE;

3.使資料檔案離線

ALTER DATABASE DATAFILE 3 OFFLINE;

4.使資料檔案聯機

ALTER DATABASE DATAFILE 3 ONLINE;

5.使資料表空間唯讀

ALTER TABLESPACE game READ ONLY;

6.使資料表空間可讀寫

ALTER TABLESPACE game READ WRITE;

五、刪除資料表空間

DROP TABLESPACE data01 INCL ING CONTENTS AND DATAFILES;

六、擴充資料表空間

首先查看錶空間的名字和所屬檔案

select tablespace_name, file_id, file_name,

round(bytes/(1024*1024),0) total_space

from dba_data_files

order by tablespace_name;

1.增加資料檔案

ALTER TABLESPACE game

ADD DATAFILE '/oracle/oradata/db/GAME02.dbf' SIZE 1000M;

2.手動增加資料檔案尺寸

ALTER DATABASE DATAFILE '/oracle/oradata/db/GAME.dbf'

RESIZE 4000M;

3.設定資料檔案自動擴充

ALTER DATABASE DATAFILE '/oracle/oradata/db/GAME.dbf

AUTOEXTEND ON NEXT 100M

MAXSIZE 10000M;

設定後查看錶空間資訊

SELECT A.TABLESPACE_NAME,A.BYTES TOTAL,B.BYTES USED, C.BYTES FREE,

(B.BYTES*100)/A.BYTES "% USED",(C.BYTES*100)/A.BYTES "% FREE"

FROM SYS.SM$TS_AVAIL A,SYS.SM$TS_USED B,SYS.SM$TS_FREE C

WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME AND A.TABLESPACE_NAME=C.TABLESPACE

 

 

 

 

 

 

 

 

 

 

 

ORACLE資料表空間及其維護基本概念
ORACLE資料庫被劃分成稱作為資料表空間的邏輯地區——形成ORACLE資料庫的邏輯結構。

一個ORACLE資料庫能夠有一個或多個資料表空間,而一個資料表空間則對應著一個或多個物理的資料庫檔案。資料表空間是ORACLE資料庫恢複的最小單位,容納著許多資料庫實體,如表、視圖、索引、聚簇、回退段和臨時段等。
     每個ORACLE資料庫均有SYSTEM資料表空間,這是資料庫建立時自動建立的。SYSTEM資料表空間必須總要保持聯機,因為其包含著資料庫運行所要求的基本資料(關於整個資料庫的資料字
典、聯機求助機制、所有回退段、臨時段和自舉段、所有的使用者資料庫實體、其它ORACLE
軟體產品要求的表)。
     一個小型應用的ORACLE資料庫通常僅包括SYSTEM資料表空間,然而一個稍大型應用的ORACLE資料庫採用多個資料表空間會對資料庫的使用帶來更大的方便。

作用
資料表空間的作用能協助DBA使用者完成以下工作:
1.決定資料庫實體的空間分配;
2.設定資料庫使用者的空間份額;
3.控制資料庫部分資料的可用性;
4.分布資料於不同的裝置之間以改善效能;
5.備份和恢複資料。
使用者建立其資料庫實體時其必須於給定的資料表空間中具有相應的權力,所以對一個使用者來說,其要操縱一個ORACLE資料庫中的資料,應該:
1.被授予關於一個或多個資料表空間中的RESOURCE特權;
2.被指定預設資料表空間;
3.被分配指定資料表空間的儲存空間使用份額;
4.被指定預設臨時段資料表空間。

 

維護
資料表空間的維護是由ORACLE資料庫系統管理員DBA通過SQL*PLUS語句實現的,其中資料表空間
建立與修改中的檔案名稱是不能帶路徑的,因此DBA必須在ORACLE/DBS目錄中操作。
1.新資料表空間的建立
文法格式:CREATE TABLESPACE 資料表空間名
DATAFILE 檔案標識符[,檔案標識符]...
[DEFAULT STORAGE(儲存配置參數)]
[ONLINE\OFFLINE];
其中:檔案標識符=’檔案名稱’[SIZE整數[K\M][REUSE]
2.修改資料表空間配置
文法格式:ALTER TABLESPCE 資料表空間名
(ADD DATAFILE 檔案標識符[,檔案標識符]...
\RENAME DATAFILE ’檔案名稱’[,’檔案名稱’]...
TO ’檔案名稱’[,’檔案名稱’]...
\DEFAULT STORAGE(儲存配置參數)
\ONLINE\OFFLINE[NORMAL\IMMEDIATE]
\(BEGIN\END)BACKUP);
3.取消資料表空間
文法格式:DROP TABLESPACE資料表空間名[INCL ING CONTENTS];
4.檢查資料表空間使用方式
(1)檢查目前使用者空間分配情況
SELECT tablespace_name,SUM(extents),SUM(blocks),SUM(bytes)
FROM user_segments
GROUP BY tablespace_name
(2)a.檢查各使用者空間分配情況
SELECT owner,tablespace_;
(2)b.檢查各使用者空間分配情況
SELECT owner,tablespace_name,SUM(extents),SUM(blocks),SUM(bytes)
FROM dba_segments
GROUP BY owner,tablespace_name;
(3) 檢查目前使用者資料庫實體空間使用方式
SELECT tablespace_name,segment_name,segment_type,
COUNT(extent_id),SUM(blocks),SUM(bytes)
FROM user_extents
GROUP BY tablespace_name,segment_name,segment_type;
(4)檢查各使用者空間使用方式
SELECT owner,tablespace_name,COUNT(extent_id),SUM(blocks),
SUM(bytes) FROM user_extents
GROUP BY owner,tablespace_name;
(5)檢查資料庫空間使用方式
SELECT tablespace_name,COUNT(extent_id),SUM(blocks),SUM(bytes)
FROM user_extents
GROUP BY tablespace_name;
(6)檢查目前使用者自由空間情況
SELECT tablespace_name,COUNT(block_id),SUM(blocks),SUM(bytes)
FROM user_free_space
GROUP BY tablespace_name;
(7)檢查資料庫自由空間情況
SELECT tablespace_name,COUNT(block_id),SUM(blocks),SUM(bytes)
FROM dba_free_space
GROUP BY tablespace_name;
四、結論
資料表空間是ORACLE資料庫系統維護的主要對象,通過本文能詳細瞭解它的基本概念與作用
,並掌握其日常維護知識,從而保證ORACLE資料庫系統的正常運行。

用命令列建立Oracle資料表空間、使用者及許可權的分配
SQL> create tablespace test datafile 'E:\ORACLE\ORADATA\DB228\test.ora' 2 size 5M;
資料表空間已建立。
SQL> create user test identified by test default tablespace test;
使用者已建立
SQL> grant connect,resource,dba to test;
授權成功。
SQL> ORACLE中，資料表空間是資料管理的基本方法，所有使用者的對象要存放在資料表空間中，也就是使用者有空間的使用權，才能建立使用者物件．否則是不充許建立對象，因為就是想建立對象,如表,索引等，也沒有地方存放,Oracle會提示:沒有儲存配額．
  因此，在建立對象之前，首先要分配儲存空間．分配儲存，就要建立資料表空間：
  建立資料表空間樣本如下：
CREATE TABLESPACE "SAMPLE"     LOGGING    

DATAFILE 'D:\ORACLE\ORADATA\ORA92\LUNTAN.ora' SIZE 5M
EXTENT    MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO
上面的語句分以下幾部分：
第一: CREATE TABLESPACE "SAMPLE"  建立一個名為 "SAMPLE" 的資料表空間.
    對錶空間的命名,遵守Oracle 的命名規範就可了.
   ORACLE可以建立的資料表空間有三種類型:
(1)TEMPORARY: 暫存資料表空間,用於臨時資料的存放;
建立暫存資料表空間的文法如下:
CREATE TEMPORARY TABLESPACE "SAMPLE"......
   (2)UNDO : 還原資料表空間. 用於存入重做記錄檔.
建立還原資料表空間的文法如下:
CREATE UNDO TABLESPACE "SAMPLE"......
(3)使用者資料表空間: 最重要,也是用於存放使用者資料資料表空間
    可以直接寫成: CREATE TABLESPACE "SAMPLE"
TEMPORARY 和 UNDO 資料表空間是ORACLE 管理的特殊的資料表空間.只用於存放系統相關資料.
第二:   LOGGING 有 NOLOGGING 和 LOGGING 兩個選項,
      NOLOGGING: 建立資料表空間時,不建立重做日誌.
     LOGGING 和NOLOGGING正好相反, 就是在建立資料表空間時產生重做日誌.
用NOLOGGING時,好處在於建立時不用組建記錄檔,這樣資料表空間的建立較快,但是沒能日誌,資料丟失後,不能恢複,但是一般我們在建立資料表空間時,是沒有資料的,按通常的做法,是建完資料表空間,並匯入資料後,是要對資料做備份的,所以通常不需要資料表空間的建立日誌,因此,在建立資料表空間時,選擇 NOLOGGING,以加快資料表空間的建立速度.
第三: DATAFILE 用於指定資料檔案的具體位置和大小.
如: DATAFILE 'D:\ORACLE\ORADATA\ORA92\LUNTAN.ora' SIZE 5M
說明檔案的存放位置是 'D:\ORACLE\ORADATA\ORA92\LUNTAN.ora' , 檔案的大小為5M.
如果有多個檔案,可以用逗號隔開:
DATAFILE 'D:\ORACLE\ORADATA\ORA92\LUNTAN.ora' SIZE 5M,     'D:\ORACLE\ORADATA\ORA92\dd.ora' SIZE 5M
但是每個檔案都需要指明大小.單位以指定的單位為準如 5M 或 500K.
對具體的檔案,可以根據不同的需要,存放大不同的介質上,如磁碟陣列,以減少IO竟爭.
指定檔案名稱時,必須為絕對位址,不能使用相對位址.
第四: EXTENT MANAGEMENT LOCAL 儲存區管理方法
在Oracle 8i以前,可以有兩種選擇,一種是在字典中管理(DICTIONARY),另一種是本地管理(LOCAL ),從9I開始,只能是本地管理方式.因為LOCAL 管理方式有很多優點.
在字典中管理(DICTIONARY): 將資料檔案中的每一個儲存單元做為一條記錄,所以在做DM操作時,就會產生大量的對這個管理表的Delete和Update操作.做大量資料管理時,將會產生很多的DM操作,嚴得的影響效能,同時,長時間對錶資料的操作,會產生很多的磁碟片段,這就是為什麼要做磁碟整理的原因.
本地管理(LOCAL): 用二進位的方式管理磁碟,有很高的效率,同進能最大限度的使用磁碟. 同時能夠自動追蹤記錄臨近空閑空間的情況，避免進行空閑區的合併作業。
第五: SEGMENT SPACE MANAGEMENT 
磁碟擴充管理方法:
SEGMENT SPACE MANAGEMENT: 使用該選項時區大小由系統自動確定。由於 Oracle 可確定各區的最佳大小，所以區大小是可變的。
UNIFORM SEGMENT SPACE MANAGEMENT:指定區大小，也可使用預設值 (1 MB)。
第六: 段空間的管理方式:
AUTO: 只能使用在本地管理的資料表空間中. 使用LOCAL管理資料表空間時,資料區塊中的空閑空間增加或減少後，其新狀態都會在位元影像中反映出來。位元影像使 Oracle 管理空閑空間的行為更加自動化，並為管理空閑空間提供了更好的性,但對含有LOB欄位的表不能自動管理.
MAN L: 目前已不用,主要是為向後相容.

第七: 指定塊大小. 可以具體指定資料表空間資料區塊的大小.

建立例子如下:
1 CREATE TABLESPACE "SAMPLE"
2      LOGGING
3      DATAFILE 'D:\ORACLE\ORADATA\ORA92\SAMPLE.ora' SIZE 5M,
4      'D:\ORACLE\ORADATA\ORA92\dd.ora' SIZE 5M
5      EXTENT MANAGEMENT LOCAL
6      UNIFORM SEGMENT SPACE MANAGEMENT
7*     AUTO
SQL> /

資料表空間已建立。

要刪除資料表空間進,可以

SQL> DROP TABLESPACE SAMPLE;

資料表空間已丟棄。
      1） 建立使用者 :    create user username identified by pwd

default tablespace users Temporary TABLESPACE Temp;   

2） 使用者授權    grant connect,resource,dba to business;   

3） 提交    commit; create user user1 identified by

vals 'fa091872a2cc669c' default tablespace user1

temporary tablespace temp profile default account unlock;

 

-- 4 roles for user1 grant recovery_catalog_owner to user1

with admin option; grant resource to user1 with admin option;

 

grant dba to user1 with admin option;

grant connect to user1 with admin option;

alter user user1 default role all;

 

-- 3 system privileges for user1 grant select any dictionary to user1

with admin option; grant unlimited tablespace to user1 with admin option;

grant alter any procedure to user1 with admin option;
T@ORA>grant dba to test identified by t; Grant s?.

Elapsed: 00:00:02.15 T@ORA>conn test/t Connected. TEST@ORA>