一 oracle表和对象基础维护笔记

目前创新互联公司已为上1000+的企业提供了网站建设、域名、虚拟空间、成都网站托管、企业网站设计、平鲁网站维护等服务，公司将坚持客户导向、应用为本的策略，正道将秉承"和谐、参与、激情"的文化，与客户和合作伙伴齐心协力一起成长，共同发展。

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;

 

 

分享题目：oracle表和对象基础维护笔记
地址分享：http://ybzwz.com/article/ggjpos.html