Oracle之表空間、索引、管理權(quán)限及角色

Oracle表空間

果洛州網(wǎng)站建設(shè)公司創(chuàng)新互聯(lián)公司,果洛州網(wǎng)站設(shè)計制作，有大型網(wǎng)站制作公司豐富經(jīng)驗。已為果洛州上千余家提供企業(yè)網(wǎng)站建設(shè)服務(wù)。企業(yè)網(wǎng)站搭建\外貿(mào)網(wǎng)站建設(shè)要多少錢，請找那個售后服務(wù)好的果洛州做網(wǎng)站的公司定做！

表空間是數(shù)據(jù)庫的邏輯組成部分，從物理上講，數(shù)據(jù)庫數(shù)據(jù)存放在數(shù)據(jù)文件中

從邏輯上講，數(shù)據(jù)庫則是存放在表空間中，表空間由一個或多個數(shù)據(jù)文件組成

數(shù)據(jù)庫的邏輯結(jié)構(gòu)

oracle中邏輯結(jié)構(gòu)包括表空間、段、區(qū)和塊

說明一下數(shù)據(jù)庫由表空間構(gòu)成，而表空間又是由段構(gòu)成，而段又是由區(qū)構(gòu)成，而

區(qū)又是由oracle塊構(gòu)成的這樣的一種結(jié)構(gòu)，可以提高數(shù)據(jù)的效率

表空間用于從邏輯上組織數(shù)據(jù)庫的數(shù)據(jù)。數(shù)據(jù)庫邏輯上是由一個或是多個表空間組成的

通過表空間可以達到以下作用：

1、控制數(shù)據(jù)庫占用的磁盤空間

2、dba可以將不同數(shù)據(jù)類型部署到不同的位置，這樣有利于提高I/O性能，同時

利于備份和恢復(fù)等管理操作

建立表空間

建立表空間是使用create tablespace命令完成的，需要注意的是，一般情況下，建立表空間

是特權(quán)用戶或是dba來執(zhí)行的，如果用其他用戶來創(chuàng)建表空間，則用戶需要具有create tablespace

的系統(tǒng)權(quán)限

建立數(shù)據(jù)表空間

在建立數(shù)據(jù)庫后，為便于管理表，最好建立自己的表空間

SQL> create tablespace data01 datafile '/oracle/datafile/data01.dbf' size 10m uniform 128k;

create tablespace data01 datafile '/oracle/datafile/data01.dbf' size 10m uniform 128k

                                                                                 *

ERROR at line 1:

ORA-02180: invalid option for CREATE TABLESPACE

報錯區(qū)域在uniform 128k，區(qū)大小設(shè)置錯誤

SQL> create tablespace data01 datafile '/oracle/datafile/data01.dbf' size 10m;

Tablespace created.

查看表空間信息

[oracle@aliyun_test oracle]$ ll datafile/data01.dbf 

-rw-r----- 1 oracle oinstall 10493952 Sep 27 09:54 datafile/data01.dbf

使用數(shù)據(jù)表空間(如果不帶表空間，默認會放到system表空間)

創(chuàng)建一張表，并將表空間data01給創(chuàng)建的這張表

SQL> create table mydept(deptno number(2),dname varchar2(14),loc varchar2(13)) tablespace data01;

Table created.

創(chuàng)建好的表空間如何查詢呢？由表的信息dab_tablespaces來進行查詢，先查看表結(jié)構(gòu)以及字段說明

SQL> desc dba_tablespaces;

 Name                                      Null?    Type

 ----------------------------------------- -------- ----------------------------

 TABLESPACE_NAME                           NOT NULL VARCHAR2(30)

 BLOCK_SIZE                                NOT NULL NUMBER

 INITIAL_EXTENT                                     NUMBER

 NEXT_EXTENT                                        NUMBER

 MIN_EXTENTS                               NOT NULL NUMBER

 MAX_EXTENTS                                        NUMBER

 MAX_SIZE                                           NUMBER

 PCT_INCREASE                                       NUMBER

 MIN_EXTLEN                                         NUMBER

 STATUS                                             VARCHAR2(9)

 CONTENTS                                           VARCHAR2(9)

 LOGGING                                            VARCHAR2(9)

 FORCE_LOGGING                                      VARCHAR2(3)

 EXTENT_MANAGEMENT                                  VARCHAR2(10)

 ALLOCATION_TYPE                                    VARCHAR2(9)

 PLUGGED_IN                                         VARCHAR2(3)

 SEGMENT_SPACE_MANAGEMENT                           VARCHAR2(6)

 DEF_TAB_COMPRESSION                                VARCHAR2(8)

 RETENTION                                          VARCHAR2(11)

 BIGFILE                                            VARCHAR2(3)

 PREDICATE_EVALUATION                               VARCHAR2(7)

 ENCRYPTED                                          VARCHAR2(3)

 COMPRESS_FOR                                       VARCHAR2(12)

查看表空data01：

SQL> select * from dba_tablespaces where tablespace_name='DATA01';

TABLESPACE_NAME                BLOCK_SIZE INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS   MAX_SIZE PCT_INCREASE MIN_EXTLEN STATUS    CONTENTS  LOGGING   FORCE_LOGGING EXTENT_MANAGEMENT ALLOCATION_TYPE PLUGGED_IN SEGMENT_SPACE_MANAGEMENT DEF_TAB_COMPRESSION RETENTION   BIGFILE PREDICATE_EVALUATION ENCRYPTED COMPRESS_FOR

------------------------------ ---------- -------------- ----------- ----------- ----------- ---------- ------------ ---------- --------- --------- --------- ------------- ----------------- --------------- ---------- ------------------------ ------------------- ----------- ------- -------------------- --------- ------------

DATA01                               8192          65536                       1  2147483645 2147483645                   65536 ONLINE    PERMANENT LOGGING   NO            LOCAL             SYSTEM          NO         AUTO                     DISABLED            NOT APPLY   NO      HOST                 NO        

可以詳細清楚的查詢該表空間的詳細信息

修改表空間的狀態(tài)

當(dāng)建立表空間時，表空間處于online狀態(tài)，此時該表空間時可以訪問的，并且該表空間時可以讀寫的

既可以查詢該表空間的數(shù)據(jù)，而且還可以在表空間執(zhí)行各種語句。但是在進行系統(tǒng)維護或是數(shù)據(jù)

維護時，可能需要改變表空間的狀態(tài)。一般情況下由特權(quán)用戶或是dba來操作

1、使表空間脫機

alter tablespace data01 offline;  data01就是表空間名稱

2、使表空間聯(lián)機

alter tablespace data01 online；

3、將表空間設(shè)為只讀狀態(tài)

當(dāng)建立表空間時，表空間可以讀寫，如果不希望在該表空間上執(zhí)行update，delete，insert

等操作，那么可以將表空間修改為只讀表空間

alter tablespace data01 read only；

事例如下：

上述已經(jīng)使用了表空間在表mydept上面，于是向里面插入數(shù)據(jù)

SQL> insert into mydept values (1,'xiaohuang','wuhan');

1 row created.

然后將表空間設(shè)置為只讀狀態(tài)

SQL> alter tablespace data01 read only;

Tablespace altered.

然后再向表mydept插入數(shù)據(jù)，看下是否成功

SQL> insert into mydept values (2,'xiaobai','shanghai');

insert into mydept values (2,'xiaobai','shanghai')

            *

ERROR at line 1:

ORA-00372: file 5 cannot be modified at this time

ORA-01110: data file 5: '/oracle/datafile/data01.dbf'

提示出錯，表示不能修改表空間的數(shù)據(jù)文件

如何恢復(fù)呢，只需再將表空間設(shè)置為可讀可寫就行，見如下操作

SQL> alter tablespace data01 read write;

Tablespace altered.

然后再向里面插入數(shù)據(jù)即可

SQL> insert into mydept values (2,'xiaobai','shanghai');

1 row created.

插入數(shù)據(jù)成功

刪除表空間

一般情況下，由特權(quán)用戶或是dba操作，如果是其他用戶操作，需要具有drop tablespace系統(tǒng)權(quán)限

drop tablespace '表空間名' including contents and datafiles;

說明：including contents表示刪除表空間的同時刪除該表空間的所有數(shù)據(jù)庫對象，而datafiles

表示將數(shù)據(jù)庫文件也刪除

SQL> drop tablespace data01 including contents and datafiles;

Tablespace dropped.

表示刪除表空間操作完成，下面查看是否已經(jīng)刪除

SQL> select * from dba_tablespaces where tablespace_name='DATA01';

no rows selected      由dba_tablespaces表查詢并沒有查詢到，即已經(jīng)刪除

[oracle@aliyun_test ~]$ ll /oracle/datafile/

total 0       可以看出數(shù)據(jù)文件也已經(jīng)刪除掉了

1、知道表空間名，顯示該表空間包括的所有表

再次創(chuàng)建表空間data01來使用

SQL> create tablespace data01 datafile '/oracle/datafile/data01.dbf' size 10m;

Tablespace created.

創(chuàng)建一張表使用該表空間

SQL> create table mydept(deptno number(2),dname varchar2(14),loc varchar2(13)) tablespace data01;

Table created.

然后通過表空間名data01如何來查詢到什么表使用了它呢？

使用到的表為all_tables

SQL> desc all_tables;

 Name                                      Null?    Type

 ----------------------------------------- -------- ----------------------------

 OWNER                                     NOT NULL VARCHAR2(30)

 TABLE_NAME                                NOT NULL VARCHAR2(30)

 TABLESPACE_NAME                                    VARCHAR2(30)

可以查詢到有一個tablespace_name的字段，于是如下操作

SQL> select tablespace_name,table_name from all_tables where tablespace_name='DATA01';

TABLESPACE_NAME                TABLE_NAME

------------------------------ ------------------------------

DATA01                         MYDEPT

查詢到使用的表以及對應(yīng)的表空間

如上，知道了表名稱也可以知道表空間

SQL> conn scott/redhat@test;

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 

Connected as scott@test

SQL> select tablespace_name,table_name from user_tables where table_name='MYDEPT';

TABLESPACE_NAME                TABLE_NAME

------------------------------ ------------------------------

DATA01                         MYDEPT

下面使用system用戶查詢的

SQL> select tablespace_name,table_name from all_tables where table_name='MYDEPT';

TABLESPACE_NAME                TABLE_NAME

------------------------------ ------------------------------

DATA01                         MYDEPT

通過2，可以了解到scott.emp表是在system這個表空間上，如果將表空間system改為只讀

但是不會成功，因為system是系統(tǒng)表空間，如果是普通表空間，就可以修改為只讀

擴展表空間

表空間是由數(shù)據(jù)文件組成的，表空間的大小實際就是數(shù)據(jù)文件相加后的大小，假定表emp存放在

data01表空間上，初始大小為2m，當(dāng)數(shù)據(jù)滿2m后，如果再向表emp插入數(shù)據(jù)，這樣就會顯示空間不足的錯誤

所以就需要擴展表空間？

1、增加數(shù)據(jù)文件

alter tablespace data01 add datafile '/oracle/datafile/data02.dbf' size 20m;

SQL> alter tablespace data01 add datafile '/oracle/datafile/data02.dbf' size 20m;

Tablespace altered.

[oracle@aliyun_test ~]$ ll /oracle/datafile/

total 30736

-rw-r----- 1 oracle oinstall 10493952 Sep 27 14:05 data01.dbf

-rw-r----- 1 oracle oinstall 20979712 Sep 27 14:06 data02.dbf

2、增加數(shù)據(jù)文件的大小

alter tablespace data01 '/oracle/datafile/data01.dbf' resize 20m;

3、設(shè)置文件的自動增長

alter tablespace data01 '/oracle/datafile/data01.dbf' autoextend on next 10m maxsize 500m;

如何遷移表空間的數(shù)據(jù)文件？

1、確定數(shù)據(jù)文件所在的表空間

select tablespace_name from dba_data_files where file_name='/oracle/datafile/data01.dbf';

TABLESPACE_NAME

------------------------------

DATA01

知道了表空間后，然后使表空間狀態(tài)脫機

2、使表空間脫機

確保數(shù)據(jù)文件的一致性，將表空間轉(zhuǎn)變?yōu)閛ffline狀態(tài)

alter tablespace data01 offline;

3、使用命令移動數(shù)據(jù)文件到指定的目標(biāo)位置

sql>host move /oracle/datafile/data01.dbf /test/oracle/datafile/data01.dbf;

4、執(zhí)行alter tablespace命令，數(shù)據(jù)文件重命名

在物理上移動了數(shù)據(jù)后，還必須執(zhí)行alter tablespace命令對數(shù)據(jù)庫文件進行邏輯修改

alter tablespace data01 rename datafile '/oracle/datafile/data01.dbf' to '/test/oracle/datafile/data01.dbf';

5、使表空間聯(lián)機狀態(tài)

在移動了數(shù)據(jù)文件后，為了使用戶可以訪問該表空間，必須將其轉(zhuǎn)變?yōu)閛nline狀態(tài)

alter tablespace data01 online；

至此遷移表空間的數(shù)據(jù)文件完成

顯示表空間信息

查詢數(shù)據(jù)字典視圖dba_tablespaces，顯示表空間的信息

select tablespace_name from dba_tablespaces;

顯示表空間所包含的數(shù)據(jù)文件

查詢數(shù)據(jù)字典視圖dba_data_files，可顯示表空間所包含的數(shù)據(jù)文件

select file_name,bytes from dba_data_files where tablespace_name='表空間名';

其他表空間

除了常用的數(shù)據(jù)表空間外，還有其他類型的表空間

1、索引表空間

2、undo表空間

3、臨時表空間

4、非標(biāo)準塊表空間

數(shù)據(jù)的完整性

數(shù)據(jù)的完整性用于確保數(shù)據(jù)庫遵從一定的商業(yè)和邏輯規(guī)則，在oracle中，數(shù)據(jù)完整性可以使用約束

觸發(fā)器、應(yīng)用程序（過程、函數(shù)）三種方法來實現(xiàn)，在這三種方法中，因為約束易于維護并且具有

最好的性能，所以作為維護數(shù)據(jù)完整性的首選

約束

約束用于確保數(shù)據(jù)庫數(shù)據(jù)滿足特定的商業(yè)規(guī)則，在oracle中，約束包括：not null、unique、primary key、foreign key和check五種。

not null：非空，如果在列上定義了not null，那么插入數(shù)據(jù)時，必須為列提供數(shù)據(jù)

unique：唯一鍵，當(dāng)定義了唯一的約束，該列的值是不能重復(fù)的，但是可以為null

primary key：主鍵，用于唯一的標(biāo)識表行的數(shù)據(jù)，當(dāng)定義主鍵約束后，該列不但不能重復(fù)而且不能為null一張表最多只能有一個主鍵，但是可以有多個唯一鍵

foreign key：外鍵，用于定義主表和從表之間的關(guān)系，外鍵約束要定義在從表上，主表則必須具有主鍵約束或是unique約束，當(dāng)定義外鍵約束后，要求外鍵列數(shù)據(jù)必須在主表的主鍵列存在或是為null

check：用于強制行數(shù)據(jù)必須滿足的條件，假定在sal列上定義了check約束，并要求sal列值在1000~2000之間，如果不在1000~2000之間就會提示錯誤

SQL> create table goods(goodid char(8) primary key,--主鍵

  2  goodname varchar2(30),

  3  unitprice number(10,2) check (unitprice > 0),--單價必須大于0

  4  category varchar2(8),

  5  provider varchar2(30))

  6  ;

SQL> create table customer(customerid char(8) primary key, --主鍵

  2  name varchar2(20) not null,--不為空

  3  address varchar2(20),

  4  email varchar2(20) unique,--不允許重復(fù)，唯一鍵

  5  sex char(4) default '男' check(sex in ('男','女')),---默認為男，不是男就是女

  6  cardid char(18))

  7  ;

SQL> create table purchase(customerid char(8) references customer(customerid),---由于這是外鍵，必須關(guān)聯(lián)到主表的字段

  2  goodid char(8) references goods(goodid),--同上，數(shù)據(jù)類型必須和主表一致

  3  nums number(5) check(nums between 1 and 30))  --數(shù)量必須在1~30之間

  4  ;

如果在建表時忘記建立必須的約束，則可以在建表后使用alter table命令為

表增加相應(yīng)的約束。但是注意：增加not null約束是修改null約束，即使用關(guān)鍵字

modify選項，而增加其他的約束則使用add關(guān)鍵字選項

alter table goods modify gooname not null;

alter table customer add constraint cardunique(約束名) unique(cardid(char);

alter table customer add constraint addresscheck check(address in ('東城','西城'));

刪除約束

當(dāng)不需要某個約束時，可以進行刪除

alter table 表名 drop constraint 約束名稱;

當(dāng)刪除主鍵約束的時候，可能會報錯

alter table 表名 drop primary key;

這是因為如果在兩張表存在主從關(guān)系，那么刪除主表的主鍵約束時，必須帶上cascade選項，例

alter table 表名 drop primary key cascade;   相當(dāng)于破壞了主外鍵關(guān)系

顯示約束信息

1、顯示約束信息

通過查詢數(shù)據(jù)字典視圖user_constraint，可以顯示當(dāng)前用戶的所有的約束信息

select constraint_name,constraint_type,status,validated from user_constraints where table_name='表名';

2、顯示約束列

通過查詢數(shù)據(jù)字典視圖user_cons_columns，可以顯示約束所對用的表列信息

select column_name,position from user_cons_columns where constraint_name='約束名';

3、當(dāng)然也有容易的方法，使用pl/sql工具查看

定義約束有如下方式：

列級定義

列級定義是在定義列的同時定義約束

如在department表定義主鍵約束

create table department(dept_id number(2) constraint pk_department primary key,name varchar(12),loc varchar2(12));

關(guān)鍵字：constraint 約束名稱：pk_department 約束類型：primary key

表級定義

表級定義是指在定義了所有列后，在定義約束，not null約束只能在列級上定義

create emp2(emp_id number(4),name varchar2(15),dept_id number(2),constraint pk_emp primary key(emp_id),constraint

fk_department foreign key(dept_id) references department(dept_id));

索引是用于加速數(shù)據(jù)存取的數(shù)據(jù)對象，合理的使用索引可以大大降低I/O次數(shù)，從而提高數(shù)據(jù)訪問性能

創(chuàng)建索引

單列索引是基于單個列所建立的索引

create index 索引名 on 表名（列名）

以name字段查詢：select * from customer where name='sp';如果經(jīng)常這樣查就可以將name字段創(chuàng)建索引，提高查詢速度

create index name_index on customer(name);---給name字段建立index

復(fù)合索引

復(fù)合索引是基于兩列或是多列的索引，在同一張表上可以有多個索引，但是要求列的組合必須不同

create index emp_index1 on emp（ename，job）；select * from customer where ename='sp' and job='MANAGER';創(chuàng)建聯(lián)合索引

create index emp_index1 on emp（job，ename）；

使用原則

1、在大表上建立索引才有意義

2、在where字句或是連接條件上經(jīng)常引用 的列上創(chuàng)建索引

3、索引的層次不要超過4層（多級索引不要超過4層）

索引的缺點

1、建立索引，系統(tǒng)要占用大約為表的1.2倍的硬盤和內(nèi)存空間來保存索引

2、更新數(shù)據(jù)的時候，系統(tǒng)必須要有額外的實際來同時對索引進行更新，以維持數(shù)據(jù)和索引的一致性

如下字段建立索引是不恰當(dāng)?shù)?/p>

1、很少或從不引用的字段

2、邏輯型的字段，如男或女等，提高查詢速率是以消耗一定的系統(tǒng)資源為代價的，索引不能盲目的建立

顯示表的所有索引

在同一張表上可以有多個索引，通過查詢數(shù)據(jù)字典視圖dba_indexes和user_indexes，可以顯示索引信息其中dba_indexes

用于顯示數(shù)據(jù)庫所有的索引信息，而user_indexs用于顯示當(dāng)前用戶的索引信息

select index_name,index_type fom user_indexes where table_name='表名';

顯示索引列

通過查詢數(shù)據(jù)字典視圖user_ind_columns，可以顯示索引對應(yīng)的列的信息

select table_name,column_name fom user_ind_columns where index_name='索引名';

管理權(quán)限和角色

系統(tǒng)權(quán)限介紹

系統(tǒng)權(quán)限是指執(zhí)行特定類型sql命令的權(quán)利，它用于控制用戶可以執(zhí)行的一個或是一組數(shù)據(jù)庫操作。比如當(dāng)用戶

具有create table權(quán)限時，可以在其方案中建表，當(dāng)用戶具有100中系統(tǒng)權(quán)限

常用的有：

create session連接數(shù)據(jù)庫                             create table建表

create view建視圖 create public sysnonym：鍵同意詞

create procedure建立過程、函數(shù)、包                   create trigger建觸發(fā)器

create cluster建簇

顯示系統(tǒng)權(quán)限

oracle提供了多條系統(tǒng)權(quán)限，而且oracle的版本越高，提供的系統(tǒng)權(quán)限就越多

select * from system_privilege_map order by name;

授予系統(tǒng)權(quán)限

一般情況下，授予系統(tǒng)權(quán)限是由dba完成的，如果其他用戶來授予系統(tǒng)權(quán)限，則要求該用戶必須具有g(shù)rant any privilege

的系統(tǒng)權(quán)限，可以帶有with admin option選項，被授予的用戶或是角色還可以將該系統(tǒng)權(quán)限授予其他用戶

1、創(chuàng)建兩個用戶ken，tom

create user ken identified by redhat；

2、給用戶key授權(quán)

grant create session，Crete table to ken with admin option;

grant create view to  ken;

3、給tom授權(quán)

grant create session,create table to tom;這是登錄的系統(tǒng)權(quán)限

revoke create session from tom;

授予數(shù)據(jù)對象權(quán)限

角色不能帶有with grant option（授予角色）

只修改某張表的某一列（字段）

grant update on emp(sal) to monkey；只能在表中的字段sal上修改

grant select on emp(ename,sal) to monkey;能在字段ename，sal上查詢

grant index on emp to monkey;允許monkey能在表emp上創(chuàng)建索引

名稱欄目：Oracle之表空間、索引、管理權(quán)限及角色
標(biāo)題URL：http://muchs.cn/article2/jcgiic.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供網(wǎng)站收錄、云服務(wù)器、企業(yè)建站、外貿(mào)建站、網(wǎng)站內(nèi)鏈、做網(wǎng)站

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源： 創(chuàng)新互聯(lián)