oracle表和對象基礎維護筆記

一 oracle表和對象基礎維護筆記

1.1 常見概念

1.2 創建表

1.3 表常見字段

1.4 增加或刪除字段

1.5 更新字段

1.6 重命名表

1.7 改變表存儲表空間和存儲參數

1.8 刪除表

1.9 表注釋

1.10 分區表的管理

1.11 常用數據字典

二 約束

2.1 非空約束

2.2 主鍵約束

2.3 唯一性約束

2.4 外鍵約束

2.5 約束管理

三 索引

3.2 創建索引

3.3 改變索引存儲參數

3.4 重建索引

3.5 索引碎片整理

3.6 刪除索引

3.7 數據字典

四 視圖

4.1 建立視圖

4.2 視圖授權

4.3 刪除視圖

五 同義詞

5.1 創建同義詞

5.2刪除同義詞

六 序列

6.1 建立序列

6.2 刪除序列

1.1 常見概念

表命名規范：不能超過30個字，只能有數字，字母,_，#組成，$

1.2 創建表

create table [schema.]table (column datatype [default expr]);

使用子查詢創建表

create table table

             [(column,column…)]

as subquery;

---創建表

create table cw1(

name varchar2(25) not null,

id number

)

insert into cw1(name,id) values('cw',1);

create table cw2

as select * from cw1;

1.3 表常見字段

varchar2(size)  最大4000字節

char(size)   最大2000字節

number(p[,s])   p總長度，s小數位

date

long    最大可到2G

CLOB   最大可以到4G

RAW and LONG RAW   二進制數據，最大2000字節，2G

BLOB  二進制數據，最大可達到4G

BFILE   存儲外部文件的二進制數據，最大可達到4G

ROWID   行地址

create table cw3(

name varchar2(10),

sex char(4),

deptid number(10),

create_date date,

card_id long,

picture blob,

file_id bfile)

--插入數據

insert into cw2(name,id,age,cardid) values('cw1',3,19,'12345');

insert into cw2(name,id,age,cardid) values('cw1',4,20,'242345');

insert into cw2(name,id,age,cardid) values('cw1',5,21,'123322225');

insert into cw2(name,id,age,cardid) values('cw1',6,22,'1242234545');

insert into cw2(name,id,age,cardid) values('cw1',7,23,'1252342345');

insert into cw2(name,id,age,cardid) values('cw1',8,24,'124234245');

insert into cw2(name,id,age,cardid) values('cw1',9,25,'133223445');

insert into cw2(name,id,age,cardid) values('cw1',10,26,'32123345');

1.4 增加或刪除字段

alter table employees add(age number(2));

--添加字段

alter table cw2  add(age number(2));

alter table cw2  add(cardid varchar(10));

--刪除字段

alter table cw2 drop(cardid );

1.5 更新字段

alter table table_name modify column_name type;

--更改表字段：

alter table cw2 modify(age char(10));

---如果表里面存在數據，需要修改為其它類型數據，會報錯

SQL> alter table cw2 modify(age char(10));

alter table cw2 modify(age char(10))

ORA-01439: column to be modified must be empty to change datatype

SQL>

---更改同類數據類型正常

SQL> alter table cw2 modify(age number(10));

Table altered

SQL>

注意：如果是需要修改數據字段類型，需要先處理表里面數據，然后再更改類型。

1.這種方法能滿足需求，因新增字段默認添加到表末尾，有可能發生行遷移,對應用程序會產生影響

2.第二種方法，是增加一個與被修改的列類型一樣的列，之后將要修改列的數據復制到新增的列并置空要修改的列，之后修改數據類型，再從新增列將數據拷貝回來，該過程涉及兩次數據復制，如果是數據量很多，會比較慢同時也會產生很多undo和redo；優點是數據不會發生行遷移。

1.6 重命名表

alter table  XXX RENAME to  xxxxx;

----重命名表

SQL> alter table cw2 rename to cw4;

Table altered

SQL>

---如果帶schema，那么會報錯，需要去掉后面的schema

SQL> alter table system.cw4 rename to system.cw2;

alter table system.cw4 rename to system.cw2

ORA-14047: ALTER TABLE|INDEX RENAME may not be combined with other operations

SQL>

---帶schema更改如下：

SQL> alter table system.cw4 rename to cw2;

Table altered

SQL>

1.7 改變表存儲表空間和存儲參數

1.8 刪除表

刪除表：drop table_name;

SQL> drop table system.cw2;

Table dropped

SQL>

刪除表數據：

truncate    

delete    

區別：1.truncate無法rollback

      2.truncate 不能觸發任何delete觸發器

-----delete刪除數據

SQL> select * from cw3;

NAME                     ID AGE   CARDID

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

cw1                       3 19    12345

cw1                       4 20    242345

cw1                       5 21    123322225

cw1                       6 22    1242234545

cw1                       7 23    1252342345

cw1                       8 24    124234245

cw1                       9 25    133223445

cw1                      10 26    32123345

8 rows selected

SQL> delete from cw3;

8 rows deleted

SQL> select * from cw3;

NAME                     ID AGE   CARDID

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

SQL> rollback;

Rollback complete

SQL> select * from cw3;

NAME                     ID AGE   CARDID

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

cw1                       3 19    12345

cw1                       4 20    242345

cw1                       5 21    123322225

cw1                       6 22    1242234545

cw1                       7 23    1252342345

cw1                       8 24    124234245

cw1                       9 25    133223445

cw1                      10 26    32123345

8 rows selected

SQL>

----delete

----truncate刪除數據

SQL> truncate table cw3;

Table truncated

SQL> rollback;

Rollback complete

SQL> select * from cw3;

NAME                     ID AGE   CARDID

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

SQL>

----truncate

1.9 表注釋

comment on table employees IS '測試';

---添加表測試記錄

SQL> comment on table cw3 is '測試';

Comment added

SQL> desc cw3;

Name   Type         Nullable Default Comments

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

NAME   VARCHAR2(20) Y                        

ID     NUMBER(5)    Y                        

AGE    CHAR(5)      Y                        

CARDID VARCHAR2(10) Y                        

SQL> select * from dba_tab_comments where table_name='CW3';

OWNER                          TABLE_NAME                     TABLE_TYPE  COMMENTS

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

SYSTEM                         CW3                            TABLE       測試

SQL>

1.10 分區表的管理

分區表的有點：

分區表的分區方法：

范圍分區：

hash分區

列表分區

復合分區（范圍+hash）（范圍+列表）

create table cw_part1(

 name varchar(20),

 id number(5),

 age char(5),

 cardid varchar(10))

partition by range(age)

(partition age_1 values less than (22),

partition age_2 values less than(24),

partition age_3 values less than(26))

as select name,id,age,cardid from cw2;

----分區表

SQL> select * from cw_part partition(age_3);

NAME                     ID AGE   CARDID

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

SQL> insert into cw_part(name,id,age,cardid) values('cw2',11,25,'232432');

1 row inserted

SQL> select * from cw_part partition(age_3);

NAME                     ID AGE   CARDID

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

cw2                      11 25    232432

SQL> select * from cw_part partition(age_1);

NAME                     ID AGE   CARDID

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

cw2                      11 20    232432

SQL> select * from cw_part partition(age_2);

NAME                     ID AGE   CARDID

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

SQL> select * from cw_part partition(age_3);

NAME                     ID AGE   CARDID

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

cw2                      11 25    232432

----分區表

1.11 常用數據字典

all_col_comments

user_col_comments

all_tab_comments

user_tab_comments

二 約束

2.1 非空約束

not null

--添加非空

SQL> alter table cw2 add(key varchar(2) not null);

alter table cw2 add(key varchar(2) not null)

ORA-01758: table must be empty to add mandatory (NOT NULL) column

SQL> select * from cw1;

NAME                              ID

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

cw                                 1

SQL> truncate cw1;

truncate cw1

ORA-03290: Invalid truncate command - missing CLUSTER or TABLE keyword

SQL> truncate table cw1;

Table truncated

---如果表為空，那么可以添加成功

SQL> alter table cw1 add(key varchar(2) not null);

Table altered

SQL>

---

2.2 主鍵約束

primary key

create table cw(name varchar constraint pk_name primary key,id number);

----添加主鍵

SQL> alter table cw2 modify(id number(5) primary key);

Table altered

SQL> select * from cw2;

NAME                     ID AGE   CARDID

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

cw1                       3 19    12345

cw1                       4 20    242345

cw1                       5 21    123322225

cw1                       6 22    1242234545

cw1                       7 23    1252342345

cw1                       8 24    124234245

cw1                       9 25    133223445

cw1                      10 26    32123345

8 rows selected

SQL> insert into cw2(id) values(5);

insert into cw2(id) values(5)

ORA-00001: unique constraint (SYSTEM.SYS_C006975) violated

SQL>

----

2.3 唯一性約束

unique

create table cw(name varchar 2(20),

id number

constraint unique_name unique(name)

);

-----唯一索引

SQL> create unique index unique_age on cw2(age);

Index created

SQL> insert into cw2(id,age) values(11,20);

insert into cw2(id,age) values(11,20)

ORA-00001: unique constraint (SYSTEM.UNIQUE_AGE) violated

SQL>

----

2.4 外鍵約束

foreign key

create table cw(

id number,

name varchar2(20)

constraint fk_cw foreign key(id) references dept(id)

);

2.5 約束管理

修改

alter table cw drop constraint  unique_name;

alter table cw add constraint  unique_name unique(name);

停止啟用

alter table cw disable constraint unique_name;

alter table cw enable constraint constraint_name;

-----約束停止

-----

三 索引

3.1 索引概述

索引的有點：
 加快查詢，減少Io操作，消除磁盤排序

索引種類：

唯一索引

位圖索引

散列索引

函數索引

3.2 創建索引

創建索引時，需要制定索引參數

create index index_name on table_name(field_name)

　　tablespace tablespace_name

　　pctfree 5

　　initrans 2

　　maxtrans 255

　　storage

　　(

　　minextents 1

　　maxextents 16382

　　pctincrease 0

　　);

tablespace 表空間 --指定建立對象的表空間 pctfree 5

--塊預留5%空間用于以后數據更新

initrans 2 --初始化事務槽數

maxtrans 255 --最大事務槽數

storage--下面是存儲參數

 initial 64K --初始化擴展區為64k next 1M

--下次擴展1m

minextents 1 --最小區數為1

maxextents 16382 --最大區數無限制 );

創建唯一索引

create unique index dept_unique_idx on dept(dept_no) tablespace idx_data;

創建位圖索引：

create bitmap index idx_bitm on cw(id) tablespace idx_data;

創建函數索引：
create index idx_fun on emp (upper(ename)) tablespace idx_data;

3.3 改變索引存儲參數

alter index unique_name

pctfree 30

storage(next 200k pctincrease 20);

3.4 重建索引

alter index unique_name rebuild tablespace indx;

----重建索引---

SQL> alter index unique_age rebuild;

Index altered

SQL>

-----

3.5 索引碎片整理

alter index cw_id_idx coalesce;

---碎片整理

SQL> alter index unique_age coalesce;

Index altered

SQL>

---

3.6 刪除索引

drop index hr.deptartments_name_idx;

需要注意，如果有外鍵，是無法刪除的，需要先禁止外鍵，然后再刪除。

drop table cw cascade constraints;

truncate cw stores;

alter table cw disable constraint fk_cw;

------

SQL> drop index unique_age;

Index dropped

SQL>

---

3.7 數據字典

dba_indexes

dba_ind_columns

dba_ind_expressions

v$object_usage

四 視圖

4.1 建立視圖

create view temp_cw as select * from cw;

可以創建制度

create view temp_cw as select * from cw

with read only;

4.2 視圖授權

grante create view to chenwei;

4.3 刪除視圖

drop view cw;

五 同義詞

同義詞優點：

  簡化SQL語句

  隱藏對象的名稱和所有者

  提供對象的公共訪問

分為：公有同義詞，私有同義詞

5.1 創建同義詞

create public synonym table_name from chenwei.cw;

5.2刪除同義詞

drop public synonym chenwei.cw;

六 序列

6.1 建立序列

create sequence seq_cw

increment by 10

start with 10

minvalue 10 nomaxvalue

查詢序列：

select seq_cw.nextval from dual;

訪問序列：

當前值： CURRVAL

 下一個：NEXTVAL

6.2 刪除序列

drop  sequence seq_cw

6.3 修改序列

alter sequence cw_seq maxvalue 5000 cycle;