您好,登錄后才能下訂單哦!
Oracle表空間
表空間是數(shù)據(jù)庫的邏輯組成部分,從物理上講,數(shù)據(jù)庫數(shù)據(jù)存放在數(shù)據(jù)文件中
從邏輯上講,數(shù)據(jù)庫則是存放在表空間中,表空間由一個或多個數(shù)據(jù)文件組成
數(shù)據(jù)庫的邏輯結構
oracle中邏輯結構包括表空間、段、區(qū)和塊
說明一下數(shù)據(jù)庫由表空間構成,而表空間又是由段構成,而段又是由區(qū)構成,而
區(qū)又是由oracle塊構成的這樣的一種結構,可以提高數(shù)據(jù)的效率
表空間用于從邏輯上組織數(shù)據(jù)庫的數(shù)據(jù)。數(shù)據(jù)庫邏輯上是由一個或是多個表空間組成的
通過表空間可以達到以下作用:
1、控制數(shù)據(jù)庫占用的磁盤空間
2、dba可以將不同數(shù)據(jù)類型部署到不同的位置,這樣有利于提高I/O性能,同時
利于備份和恢復等管理操作
建立表空間
建立表空間是使用create tablespace命令完成的,需要注意的是,一般情況下,建立表空間
是特權用戶或是dba來執(zhí)行的,如果用其他用戶來創(chuàng)建表空間,則用戶需要具有create tablespace
的系統(tǒng)權限
建立數(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ū)大小設置錯誤
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來進行查詢,先查看表結構以及字段說明
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)
當建立表空間時,表空間處于online狀態(tài),此時該表空間時可以訪問的,并且該表空間時可以讀寫的
既可以查詢該表空間的數(shù)據(jù),而且還可以在表空間執(zhí)行各種語句。但是在進行系統(tǒng)維護或是數(shù)據(jù)
維護時,可能需要改變表空間的狀態(tài)。一般情況下由特權用戶或是dba來操作
1、使表空間脫機
alter tablespace data01 offline; data01就是表空間名稱
2、使表空間聯(lián)機
alter tablespace data01 online;
3、將表空間設為只讀狀態(tài)
當建立表空間時,表空間可以讀寫,如果不希望在該表空間上執(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.
然后將表空間設置為只讀狀態(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ù)文件
如何恢復呢,只需再將表空間設置為可讀可寫就行,見如下操作
SQL> alter tablespace data01 read write;
Tablespace altered.
然后再向里面插入數(shù)據(jù)即可
SQL> insert into mydept values (2,'xiaobai','shanghai');
1 row created.
插入數(shù)據(jù)成功
刪除表空間
一般情況下,由特權用戶或是dba操作,如果是其他用戶操作,需要具有drop tablespace系統(tǒng)權限
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
查詢到使用的表以及對應的表空間
如上,知道了表名稱也可以知道表空間
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,當數(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、設置文件的自動增長
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ù)文件的一致性,將表空間轉變?yōu)閛ffline狀態(tài)
alter tablespace data01 offline;
3、使用命令移動數(shù)據(jù)文件到指定的目標位置
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ù)文件后,為了使用戶可以訪問該表空間,必須將其轉變?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、非標準塊表空間
數(shù)據(jù)的完整性
數(shù)據(jù)的完整性用于確保數(shù)據(jù)庫遵從一定的商業(yè)和邏輯規(guī)則,在oracle中,數(shù)據(jù)完整性可以使用約束
觸發(fā)器、應用程序(過程、函數(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:唯一鍵,當定義了唯一的約束,該列的值是不能重復的,但是可以為null
primary key:主鍵,用于唯一的標識表行的數(shù)據(jù),當定義主鍵約束后,該列不但不能重復而且不能為null一張表最多只能有一個主鍵,但是可以有多個唯一鍵
foreign key:外鍵,用于定義主表和從表之間的關系,外鍵約束要定義在從表上,主表則必須具有主鍵約束或是unique約束,當定義外鍵約束后,要求外鍵列數(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,--不允許重復,唯一鍵
5 sex char(4) default '男' check(sex in ('男','女')),---默認為男,不是男就是女
6 cardid char(18))
7 ;
SQL> create table purchase(customerid char(8) references customer(customerid),---由于這是外鍵,必須關聯(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命令為
表增加相應的約束。但是注意:增加not null約束是修改null約束,即使用關鍵字
modify選項,而增加其他的約束則使用add關鍵字選項
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 ('東城','西城'));
刪除約束
當不需要某個約束時,可以進行刪除
alter table 表名 drop constraint 約束名稱;
當刪除主鍵約束的時候,可能會報錯
alter table 表名 drop primary key;
這是因為如果在兩張表存在主從關系,那么刪除主表的主鍵約束時,必須帶上cascade選項,例
alter table 表名 drop primary key cascade; 相當于破壞了主外鍵關系
顯示約束信息
1、顯示約束信息
通過查詢數(shù)據(jù)字典視圖user_constraint,可以顯示當前用戶的所有的約束信息
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、當然也有容易的方法,使用pl/sql工具查看
定義約束有如下方式:
列級定義
列級定義是在定義列的同時定義約束
如在department表定義主鍵約束
create table department(dept_id number(2) constraint pk_department primary key,name varchar(12),loc varchar2(12));
關鍵字: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
復合索引
復合索引是基于兩列或是多列的索引,在同一張表上可以有多個索引,但是要求列的組合必須不同
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倍的硬盤和內存空間來保存索引
2、更新數(shù)據(jù)的時候,系統(tǒng)必須要有額外的實際來同時對索引進行更新,以維持數(shù)據(jù)和索引的一致性
如下字段建立索引是不恰當?shù)?/p>
1、很少或從不引用的字段
2、邏輯型的字段,如男或女等,提高查詢速率是以消耗一定的系統(tǒng)資源為代價的,索引不能盲目的建立
顯示表的所有索引
在同一張表上可以有多個索引,通過查詢數(shù)據(jù)字典視圖dba_indexes和user_indexes,可以顯示索引信息其中dba_indexes
用于顯示數(shù)據(jù)庫所有的索引信息,而user_indexs用于顯示當前用戶的索引信息
select index_name,index_type fom user_indexes where table_name='表名';
顯示索引列
通過查詢數(shù)據(jù)字典視圖user_ind_columns,可以顯示索引對應的列的信息
select table_name,column_name fom user_ind_columns where index_name='索引名';
管理權限和角色
系統(tǒng)權限介紹
系統(tǒng)權限是指執(zhí)行特定類型sql命令的權利,它用于控制用戶可以執(zhí)行的一個或是一組數(shù)據(jù)庫操作。比如當用戶
具有create table權限時,可以在其方案中建表,當用戶具有100中系統(tǒng)權限
常用的有:
create session連接數(shù)據(jù)庫 create table建表
create view建視圖 create public sysnonym:鍵同意詞
create procedure建立過程、函數(shù)、包 create trigger建觸發(fā)器
create cluster建簇
顯示系統(tǒng)權限
oracle提供了多條系統(tǒng)權限,而且oracle的版本越高,提供的系統(tǒng)權限就越多
select * from system_privilege_map order by name;
授予系統(tǒng)權限
一般情況下,授予系統(tǒng)權限是由dba完成的,如果其他用戶來授予系統(tǒng)權限,則要求該用戶必須具有grant any privilege
的系統(tǒng)權限,可以帶有with admin option選項,被授予的用戶或是角色還可以將該系統(tǒng)權限授予其他用戶
1、創(chuàng)建兩個用戶ken,tom
create user ken identified by redhat;
2、給用戶key授權
grant create session,Crete table to ken with admin option;
grant create view to ken;
3、給tom授權
grant create session,create table to tom;這是登錄的系統(tǒng)權限
revoke create session from tom;
授予數(shù)據(jù)對象權限
角色不能帶有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)建索引
免責聲明:本站發(fā)布的內容(圖片、視頻和文字)以原創(chuàng)、轉載和分享為主,文章觀點不代表本網(wǎng)站立場,如果涉及侵權請聯(lián)系站長郵箱:is@yisu.com進行舉報,并提供相關證據(jù),一經(jīng)查實,將立刻刪除涉嫌侵權內容。