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;

本文出自 “痞子厨子戏子” 博客，请务必保留此出处http://chenwei.blog.51cto.com/32183/1695746

原文：http://chenwei.blog.51cto.com/32183/1695746