数据库对象日常操作

1.用户

1.1基本语句

SYS用户执行：

  

--查看所有用户信息

select * from dba_users;

Select username,password,account_status,default_tablespace,temporary_tablespace,profile from dba_users;

 

--查询数据库dcs_sys用户下的所有对象

select * from all_objects where owner='DCS_SYS';

select owner,object_name,object_type,status from all_objects where owner='DCS_SYS';

#状态status: INVALID无效，VALID有效

 

-- 创建用户

create user test01 identified by test01;  #默认是user表空间

-- 授权

grant connect,resource to test01;

-- 撤销权限

revoke connect from test01;

 

-- 修改密码 

alter user test01 identified by 123456; 

--解锁用户

alter user test account unlock;

-- 删除用户，加上cascade则将用户连同其创建的东西全部删除

drop user test01 cascade;

 

--当前登录用户执行
select * from all_users;  查看你能管理的所有用户！
select * from user_users; 查看当前用户信息 ！

 

 

1.2用户权限

1） 系统权限–对用户而言

connect:拥有Connect权限的用户只可以登录Oracle，不可以创建实体，不可以创建数据库结构。

resource:RESOURCE:拥有Resource权限的用户只可以创建实体，不可以创建数据库结构。

dba:拥有全部特权，是系统最高权限，只有DBA才可以创建数据库结构。

2) 实体权限 –针对表或视图而言的

select, update, insert, alter, index, delete, all //all包括所有权限，execute //执行存储过程权限

 

1.查询用户拥有的系统权限

SQL> SELECT GRANTEE,PRIVILEGE FROM DBA_SYS_PRIVS WHERE GRANTEE = 'SCOTT';（dba权限）

 

2.查询用户拥有的对象权限

SQL> SELECT GRANTEE,TABLE_NAME,PRIVILEGE FROM DBA_TAB_PRIVS WHERE GRANTEE = 'SCOTT';（dba权限）

 

3.查询用户拥有的角色

SQL> SELECT GRANTEE,GRANTED_ROLE FROM DBA_ROLE_PRIVS WHERE GRANTEE ='SCOTT';（dba权限）

 

4.查看用户角色

SELECT * FROM USER_ROLE_PRIVS;

 

5.查看当前用户权限：

select * from session_privs;

 

6.查看所有用户所拥有的角色

SELECT * FROM DBA_ROLE_PRIVS;（dba权限）

 

7.查看所有角色

select * from dba_roles;（dba权限）

 

1.3用户会话

1、查询用户会话信息：
SQL> select username, sid, serial#, machine from v$session;

SQL> select * from v$session;

STATUS：这列用来判断session状态是：

  Achtive：正执行SQL语句(waiting for/using a resource)

  Inactive：等待操作(即等待需要执行的SQL语句)

  Killed：被标注为删除

2、删除用户会话信息：
SQL> Alter system kill session 'sid, serial#';

在ORACLE数据库杀掉会话进程有三种方式：

1： ALTER SYSTEM KILL SESSION

关于KILL SESSION Clause ，如下官方文档描述所示，alter system kill session实际上不是真正的杀死会话，它只是将会话标记为终止。等待PMON进程来清除会话。

可以使用ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE 来快速回滚事物、释放会话的相关锁、立即返回当前会话的控制权。

2： ALTER SYSTEM DISCONNECT SESSION

ALTER SYSTEM DISCONNECT SESSION 杀掉专用服务器(DEDICATED SERVER)或共享服务器的连接会话，它等价于从操作系统杀掉进程。它有两个选项POST_TRANSACTION和IMMEDIATE， 其中POST_TRANSACTION表示等待事务完成后断开会话，IMMEDIATE表示中断会话，立即回滚事务。

SQL> ALTER SYSTEM DISCONNECT SESSION 'sid,serial#' POST_TRANSACTION;

SQL> ALTER SYSTEM DISCONNECT SESSION 'sid,serial#' IMMEDIATE;

3： KILL -9 SPID （Linux） 或 orakill ORACLE_SID spid　（Windows）

可以使用下面SQL语句找到对应的操作系统进程SPID，然后杀掉。当然杀掉操作系统进程是一件危险的事情，尤其不要误杀。所以在执行前，一定要谨慎确认。

SELECT s.inst_id,

       s.sid,

       s.serial#,

       p.spid,

       s.username,

       s.program

FROM   gv$session s

       JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id

WHERE  s.type != 'BACKGROUND';

1.4数据库最大连接数

select count(*) from v$process --当前的连接数

select value from v$parameter where name = 'processes' --数据库允许的最大连接数

*修改最大连接数:

alter system set processes = 1000 scope = spfile

(需要重启数据库才能实现连接数的修改)

 

2. 表

 

 

 

-- 查询当前用户下有哪些表
select * from user_tables;
select table_name,tablespace_name,table_lock from user_tables;

 

-- 查询当前用户下可以访问哪些表

SELECT * FROM all_tables

-- 查询当前数据库所有的表， 需要你有 DBA 的权限
SELECT * FROM dba_tables

 

2.1表结构操作

--查看当前用户下表结构

select * from user_tab_columns;

1、创建表

     create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..)

     根据已有的表创建新表：

     A：select * into table_new from table_old (使用旧表创建新表)

     B：create table tab_new as select col1,col2… from tab_old definition only<仅适用于Oracle>

--查询建表语句

SELECT DBMS_METADATA.GET_DDL('TABLE','table_name') FROM DUAL ;

2、删除表

     drop table tabname

    --删除表及关联

    drop table tabname cascade constraints;

3、重命名表

     说明：alter table 表名 rename to 新表名

        eg：alter table tablename rename to newtablename

4、增加字段

     说明：alter table 表名 add (字段名 字段类型 默认值 是否为空);

        例：alter table tablename add (ID int);

       eg：alter table tablename add (ID varchar2(30) default '空' not null);

5、修改字段（不能有数据）

     说明：alter table 表名 modify (字段名 字段类型 默认值 是否为空);

        eg：alter table tablename modify (ID number(4));

6、重命名字段（不能有数据）

     说明：alter table 表名 rename column 列名 to 新列名 （其中：column是关键字）

        eg：alter table tablename rename column ID to newID;

7、删除字段

     说明：alter table 表名 drop column 字段名;

        eg：alter table tablename drop column ID;

8、添加主键

     alter table tabname add primary key(col)

9、删除主键

     alter table tabname drop primary key(col)

10、创建索引

     create [unique] index idxname on tabname(col….)

11、删除索引

     drop index idxname

     注：索引是不可更改的，想更改必须删除重新建。

12、创建视图

     create view viewname as select statement

13、删除视图

     drop view viewname

 

2.2表数据操作

1、数据查询

     select <列名> from <表名> [where <查询条件表达试>] [order by <排序的列名>[asc或desc]]

2、插入数据

     insert into 表名 values(所有列的值);

     insert into test values(1,'zhangsan',20);

     insert into 表名(列) values(对应的值);

     insert into test(id,name) values(2,'lisi');

插入空值

insert into student (xh,xm,sex,birthday) values(‘021’,’BLYK’,’男’,null);

 

3、更新数据

     update 表 set 列=新的值 [where 条件] -->更新满足条件的记录

     update test set name='zhangsan2' where name='zhangsan'

     update 表 set 列=新的值 -->更新所有的数据

     update test set age =20;

4、删除数据

• delete from 表名 where 条件 -->删除满足条件的记录

     delete from test where id = 1;

     delete from test -->删除所有

     commit; -->提交数据

     rollback; -->回滚数据

     delete方式可以恢复删除的数据，但是提交了，就没办法了 delete删除的时候，会记录日志 -->删除会很慢很慢

• truncate table 表名

     删除所有数据，不会影响表结构，不会记录日志，数据不能恢复 -->删除很快

• drop table 表名

     删除所有数据，包括表结构一并删除，不会记录日志，数据不能恢复-->删除很快

2.3表结构/数据复制

• 表数据复制（able2已经存在，表结构和table1一样）

     insert into table1 (select * from table2);

• 存在一个表B和表A的表结构不一致，将A中的数据复制给B表

insert into B (x1,x2,x3...) select x1,x2,x3... from A;

• 复制表结构(只复制表结构，不复制其他表对象，如主外键等)

     create table table1 select * from table2 where 1>1;

• 复制表结构，如果目标表table1不存在，使用下面语句

create table table1 as select * from table2 where 1>1;

• 复制表结构和数据(只复制表结构和数据，不复制其他表对象，如主外键等)

     create table table1 select * from table2;

• 复制表结构和数据，如果目标表table1不存在，使用下面语句

create table table1 as select * from table2

• 复制指定字段

     create table table1 as select id, name from table2 where 1>1;

 

3. 约束

--查询约束

1.USER_CONSTRAINTS

2.USER_CONS_COLUMNS

SQL> select constraint_name,table_name,column_name from user_cons_columns where table_name='T1';

--查看当前用户约束，重点字段信息

select constraint_name,

       constraint_type,

       table_name,

       status,

       validated,

       index_owner,

       index_name

  from user_constraints;

 

SQL> select * from USER_CONSTRAINTS where table_name='T1';

constraint_type 中C对应CHECK，P->primary key, r->FOREIGN KEY , U-->UNIQUE

 

唯一约束

唯一约束

要求该列唯一，允许为空

1.唯一约束 ( unique )

      --例如1：

       create table test19(

              id number ,

              name varchar2(30) ,

              address varchar2(30) ,

              primary key(id) ,

              unique(address)

       );

 

      ---例如2：

       create table test20(

              id number primary key ,

              name varchar2(30) ,

              address varchar2(30) unique

       );

 

      --例如3：

       create table test21(

              id number primary key ,

              name varchar2(30) ,

              address varchar2(30)

       );

       --给建好的表加上唯一约束

       ALTER TABLE test21 ADD unique(address);

 

主键约束

　　主键是定位表中单个行的方式，可唯一确定表中的某一行，关系型数据库要求所有表都应该有主键，不过Oracle没有遵循此范例要求，Oracle中的表可以没有主键（这种情况不多见）。关于主键有几个需要注意的点：

1. 键列必须必须具有唯一性，且不能为空，其实主键约束 相当于 UNIQUE+NOT NULL
2. 一个表只允许有一个主键
3. 主键所在列必须具有索引（主键的唯一约束通过索引来实现），如果不存在，将会在索引添加的时候自动创建

    1.主键约束 ( primary key )

 

     --例如1：

         create table test(

                c number(10) primary key

        );

 

     --例如2：

        create table test1(

             c number(10) constraint pk_c primary key       

        );

 

       --例如3：

         create table test2(

                c number(10) ,

                primary key(c)

         );

 

       --例如4：

         create table test3(

                c number(10),

                c1 number(10),

                primary key (c,c1)

       );

 

       --例如5：

         create table test4(

                c number(10) ,

                constraint pk_test4_c primary key (c)

         );

 

 

2.给建好的表创建主键：

       --例如6：

         create table test5(

                c number(10)

         );      

        alter table test5 add primary key (c);

       

 

3.给建好的表添加主键：不使用默认主键名，自定义主键名

     --例如7：

       create table test6(

             c number(10)

       );     

     alter table test6 add constraint pk_test6_c primary key(c);

 

 

 

　　

外键约束

外键约束定义在具有父子关系的子表中，外键约束使得子表中的列对应父表的主键列，用以维护数据库的完整性。不过出于性能和后期的业务系统的扩展的考虑，很多时候，外键约束仅出现在数据库的设计中，实际会放在业务程序中进行处理。外键约束注意以下几点：

1. 　　外键约束的子表中的列和对应父表中的列数据类型必须相同，列名可以不同
2. 　　对应的父表列必须存在主键约束（PRIMARY KEY）或唯一约束（UNIQUE）
3. 　　外键约束列允许NULL值，对应的行就成了孤行了

　　其实很多时候不使用外键，很多人认为会让删除操作比较麻烦，比如要删除父表中的某条数据，但某个子表中又有对该条数据的引用，这时就会导致删除失败。

 

1.外键约束 ( foreign key )

 

     --例如1：Fk 使用“列级约束”来进行建表：

        create table test8(

                a1 number(10) primary key

       );

       

       create table test9(

              b1 number(10) primary key,

              b2 number(10) references test8(a1)

       );

 

     --例如2：Fk 使用“表级约束”来进行建表：

        Create table test10 (

                a1 number(10) primary key

       );

       

       Create table test11(

                b number(10) primary key,

                b2 number(10),

                foreign key(b2)references test10(a1)

       );

 

     --例如3：

       Create table test12 (

          a1 number(10) primary key

       );

       

       Create table test13(

           b number(10) primary key,

           b2 number(10),

           foreign key(b2)references test12(a1)

       );

 

     --例如4：

       Create table test14 (

              a1 number(10)

       );

       alter table test14 add constraint pk_test14_c primary key(a1);

 

       Create table test16(

              b number(10),

              b2 number(10)

       );

       alter table test16 add constraint pk_test16_c foreign key(b) references test14(a1);

 

2.级联删除：

     --例如一：（如果删除父表中的某条记录，子表相应记录也被删除）

       create table test17(

              id number primary key

       );

       插入操作：insert into test17(id)values(1);

 

       create table test18(

            id number primary key,

          p_id number references test17(id) on delete cascade

       );

 

       插入操作：insert into test18 values(1,1);

 

       删除操作：delete  from test17 where id=1;--注意：发现子表的数据已经没有了

 

     --例如二：（如果删除父表某条记录，子表相应记录被置空）

       create table parent(

             id number primary key

       );

       insert into parent values (1);

       

       create table chile(

              id number primary key,

              p_id number references parent(id) on delete set null

       );

       插入操作：insert into chile values(1,1);

       删除操作：delete from parent where id=1;

       --注意：删除父表时子表对应列为空

 

3. Foreign Key 的可选参数 ON DELETE CASCADE

      在创建 Foreign Key 时可以加可选参数:

      ON DELETE CASCADE 它的含义是如果删除外键主表里的内容，子表里相关的内容将一起被删除.

      如果没有 ON DELETE CASCADE 参数，子表里有内容，父表里的主关键字记录不能被删除掉.

 

检查约束

1.检查约束 ( check )

　　某列取值范围限制、格式限制等

 

2.检查只能是男或者女    

       create table test29(         

              id number primary key,         

              sex varchar2(2) check(sex in ('男,女'))  

       );    

 

       create table test30(         

              id number primary key,         

              sex varchar2(2) check(sex ='男' or sex='女')  

       );    

 

       create table test31(         

             id number primary key,         

             sex varchar2(2)  

       );  

 

       alter table test31 add constraint chkk check (sex ='男' or sex='女');  

       alter table test31 add constraint chkk check (sex in('男','女'))；  

 

3.在一个范围中间

       create table test32(       

              id number primary key,       

          age number check(age>0 and age<120)

       );

 

       create table test33(       

              id number primary key,       

          age number check(age between 12 and 30)

       );

 

       create table test34(       

              id number primary key ,       

              age number

       );

 

       alter table test34 add constraint ch_test34 check(age>0 and age<120);

       alter table test34 add constraint ch_test34 check(age between 12 and 30);  

 

4.长度大于某个值

       create table test35(       

              id number primary key,       

              password varchar2(10) check(length(password)=6)

       );

       

       create table test36(       

              id number primary key ,       

          password varchar2(20)

       );

       alter table test36 add constraint check_test36 check(length(password)=6);

 

5.数大于某个值

       create table test37(      

              id number(10)primary key ,      

              no number(10) check(no>1)

       );

       

       create table test38(       

              id number(10) primary key,      

              no number(10)

       );

       alter table test38 add constraint ch_test38 check(no>1);

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

 

6.只能是8位字符,前两位是 0 ,3~4位为数字,第 5 位为"_"下划线,6~8位为字母

        create table test39(       

               id number(10) primary key,       

               password varchar2(20) check((password like '00[0-9][0-9]/_[a-z,A-Z][a-z,A-Z][a-z,A-Z]%' escape '/')and(length(password)=8) )

       );

       insert into test39 values (1,'0011_aaa');

 

       create table test40(       

            id number(10) primary key ,       

            password varchar2(10)check((password like '00[0-9][0-9][_][a-z,A-Z][a-z,A-Z][a-z,A-Z]%')and(length(password)=8) ));

       );

       alter table test40 modify password varchar2(10)check((password like '00[0-9][0-9][_][a-z,A-Z][a-z,A-Z][a-z,A-Z]%')and(length(password)>1)

       insert into test40 values(1,'0012_abc');

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

 

7.电子邮箱要含有@符号check(字段 like '%@%')

       create table test41(       

              id number(10) primary key,       

              email varchar2(10) check (email like '%@%')

        );

        insert into test41 values(1,'12@126.com');

 

8.SQL中用check约束一列的首字母为's'check(字段 like 's%')

        create table test42(       

            id number(10) primary key ,       

            name varchar2(10) check(name like 's%')

         );

         insert into test42 values(1,'sname');

 

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

9.检查约束前3位和后8位均为数字字符:check(字段 like '[0-9][0-9][0-9]%[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]')

      create table test43(       

             id number(10) primary key,       

             no varchar2(10)check(no like '[0-9][0-9][0-9]%[0-9][0-9][0-9][0-9][0-9]')

     );

     insert into test43 values(1,'12345678');

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

 

10.如何建立检查身份证的约束,身份证是18位,最后一位还有可能是X

     create table test44(       

       id number(10) primary key,       

       no values(18) check( length(no)=18 and right(no,17)like '[0-9]' or right (no,17) like 'x' )

     );

insert into test44 values (1,'12345678912345678x');

 

select 身份证号 from 表名where len(身份证号) = 18 and (right(身份证号,17) like  '[0-9]'or right(身份证号,17) like 'x')

 

11.如何设置区号由0-9之间的数字组成CONSTRAINT  

       quhao CHECK (quhao  LIKE '[0-9][0-9][0-9]'

    or quhao LIKE '[0-9][0-9][0-9][0-9]'

    or quhao LIKE '[0-9][0-9][0-9][0-9][0-9]'));

    

解释：quhao LIKE '[0-9]...[0-9]'的号码由表示n位从0到9中的数组成。

      quhao  LIKE '[0-9][0-9][0-9]' 表示3位的区号，如北京010;

      quhao LIKE '[0-9][0-9][0-9][0-9]'表示4位的区号，如三门峡0398；

      quhao LIKE '[0-9][0-9][0-9][0-9][0-9]'表示5位的区号，如香港00852

      

12.最后回复时间 TLastClickT    发贴时间 TTime最后回复时间 必须晚于 发贴时间  并且小于等于当前时间

      使用GetDate()函数获取当前时间

      设计表在TLastClickT上右击选择约束,新建,

      填入([TLastClickT] > [TTime] and [TLastClickT] < GetDate())

      或者TiastReply（回帖时间）大于Ttime（发帖时间）

      在创表的同时创建表的时候应该还没有回帖吧，为什么要用默认值？

      可以添加一个约束

      alter table topic alter column add check(TlastReply is null or TlastReply > Ttime)

 

13.定义前八位为数字或者 -一共是15位，为CHAR型

     alter table 表名add constraint chk check(字段 like'[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%'),    

     constraint chklen check(len(字段)=15)    

 

14.如何限制varchar字段不允许出现单引号的检查约束 ！！！

     设表为TALBENAME,不能有引号的字段为FIELDNAME 则：

     ALTER TABLE tablename ADD CONSTRAINT CK_fieldname CHECK (not fieldname like '%''%')

 

15.在表中的某列中通过检查约束，让其有某些固定的值

     check(sid like 'bd[0-9][0-9][0-9][0-9][0-9][0-9]')

     add const ck_num check(num like '[1][2] [4][_] [0-9][0-9] [0-9][a-z]')

 

16.如何限制varchar字段不允许出现字符串的检查约束 ！！！

     设表名为TABLENAME,VARCHAR类型的字段为VAR_FIELD.则有：

     ALTER TABLE [dbo].[TABLENAME]

     ADD CONSTRAINT [CK_TABLENAME]

     CHECK (isnumeric([VAR_FIELD]) = 1)

     这样，在VAR_FIELD只要出现字段一旦出现非数字内容就会报告错误。

 

17.电话号码的格式必须为xxxx-xxxxxxxx或手机号11位

     alter 表名 add constraint ck_字段名 check （字段 like '[0-9][0-9][0-9][0-9]_[0-9]......'

     or length(字段)=11）

 

18.身份证号是18位且唯一的

     alter 表名 add constraint ck_字段名 check （len(字段名)=18 ），

     constraint uk_字段名 unique（字段名）

 

约束状态

　　很多时候由于业务需要，比如我们有大量的历史数据，需要和现有数据合并，当前表存在数据库约束（如非空约束），而这些历史数据又包含违背非空约束的数据行，为了避免导入时由于违反约束而导入失败，我们通过调整约束状态来达到目的。

数据库约束有两类状态

　　启用/禁用（enable/disable）：是否对新变更的数据启用约束验证

　　验证/非验证 (validate/novalidate) ：是否对表中已客观存在的数据进行约束验证

这两类四种状态从语法角度讲可以随意组合，默认是 enable validate

下面我们来看着四类组合会分别出现什么样的效果：

enable validate : 默认的约束组合状态，无法添加违反约束的数据行，数据表中也不能存在违反约束的数据行；

enable novalidate : 无法添加违反约束的数据行，但对已存在的违反约束的数据行不做验证；

disable validate : 可以添加违反约束的数据行，但对已存在的违反约束的数据行会做约束验证（从描述中可以看出来，这本来就是一种相互矛盾的约束组合，只不过是语法上支持这种组合罢了，造成的结果就是会导致DML失败）

disable novalidate : 可以添加违法约束的数据行，对已存在的违反约束的数据行也不做验证。

拿上面的例子来说，我们需要上传大量违反非空约束的历史数据（从业务角度讲这些数据不会造成系统功能异常），可以临时将约束状态转为 disable novalidate，以保证这些不合要求的数据导入表中

SQL> alter table emp modify constraint emp_ename_nn disable novalidate;

在数据导入完成之后，我们再将约束状态转为enable novalidate 以确保之后添加的数据不会再违反约束

SQL> alter table emp modify constraint emp_ename_nn enable novalidate;

约束维护

1 增加约束

（1） 如果增加UNIQUE、PRIMARY KEY、FOREIGN KEY 和CKECK 必须使用ALTER TABLE语句的ADD子句;

（2） 如果增加NOT NULL约束，那么必须使用ALTER TABLE语句的MODIFY子句，如：

ALTER TABLE table_name ADD [CONSTRAINT constraint_name]

constraint_type (column,...)

ALTER TABLE table_name MODIFY column

[CONSTRAINT constraint_name] NOT NULL;

2 修改约束名

在同一个方案中，约束名必须惟一,并且约束名也不能与其他对象同名。当用IMPDP工具或者IMP工具导入其他对象时，如发现有同名的对象，将会出错

语法：

ALTER TABLE table_name RENAME CONSTRAINT old_constraint_name

TO new_constraint_name;

例：

ALTER TABLE emp01 RENAME CONSTRAINT SYS_C005028

TO ck_emp01_salary;

 

SQL> alter table t1 rename constraint PK_T1_ID to new pk01_t1_id;

alter table t1 rename constraint PK_T1_ID to new pk01_t1_id

*

ERROR at line 1: --------主键无法更改名字

 

ORA-23290: This operation may not be combined with any other operation

SQL> alter table t2 rename constraint fk_t2_id to fk01_t2_id;

SQL> select constraint_name,table_name from user_constraints where table_name='T2';

CONSTRAINT_NAME TABLE_NAME

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

FK01_T2_ID T2 -------------外键可以更改名字

 

SQL> alter table t1 add constraint un_t1_qq unique(qq);

SQL> select constraint_name,table_name from user_constraints where table_name='T1';

CONSTRAINT_NAME TABLE_NAME

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

PK_T1_ID T1

UN_T1_QQ T1

SQL> alter table t1 rename constraint un_t1_qq to un01_t1_qq;

SQL> select constraint_name,table_name from user_constraints where table_name='T1';

CONSTRAINT_NAME TABLE_NAME

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

PK_T1_ID T1

UN01_T1_QQ T1

 

3 禁止约束

禁止约束指使约束临时失效。当禁止了约束之后，约束规则将不再生效。在使用SQL*LOADER或INSERT装载数据之前，为了加快数据装载速度，应该首先禁止约束，然后装载数据。

语法：

ALTER TABLE table_name

DISABLE CONSTRAINT constaint_name [CASCAED];--CASCAED用于指定级联禁止从表的外部键

SQL> insert into t2 values(2,2);

insert into t2 values(2,2)

*

ERROR at line 1:

ORA-02291: integrity constraint (SYS.FK01_T2_ID) violated - parent key not

found

SQL> alter table t2 disable constraint fk01_t2_id;

Table altered.

SQL> insert into t2 values(2,2);

1 row created.

#动态SQL批量修改

select 'alter table ' || table_name || ' disable constraint ' || constraint_name  || ';' from user_constraints where constraint_type='R';

 

4 激活约束

语法：

ALTER TABLE table_name ENABLE CONSTRAINT constraint_name;

例：

SQL> alter table t2 enable constraint fk01_t2_id;

alter table t2 enable constraint fk01_t2_id

*

ERROR at line 1: ---外键激活失败，原因是在外键表中含有主键表中没有的数据

ORA-02298: cannot validate (SYS.FK01_T2_ID) - parent keys not found

SQL> delete t2 where id=2; -------删除数据

SQL> alter table t2 enable constraint fk01_t2_id; ----激活成功

 

5 删除约束

当删除特定表的主键约束时，如果该表具有相关的从表，那么在删除主键约束时必须带有CASCAED选项

语法：

ALTER TABLE table_name DROP

CONSTRAINT constraint_name |PRIMARY KEY

例一：（删除唯一性约束）

SQL> select constraint_name,table_name from user_constraints where table_name='T1';

CONSTRAINT_NAME TABLE_NAME

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

PK_T1_ID T1

UN01_T1_QQ T1

SQL> alter table t1 drop constraint un01_t1_qq;

Table altered.

SQL> select constraint_name,table_name from user_constraints where table_name='T1';

CONSTRAINT_NAME TABLE_NAME

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

PK_T1_ID T1

例二：（删除主键约束，级联删除外键约束）

SQL> alter table t1 drop primary key cascade;

Table altered.

SQL> select constraint_name,table_name from user_constraints where table_name='T2';

no rows selected

SQL> select constraint_name,table_name from user_constraints where table_name='T1';

no rows selected

 

 

4. index索引

4.1索引的分类

在关系数据库中，每一行都由一个行唯一标识RowID。RowID包括该行所在的文件、在文件中的块数和块中的行号。索引中包含一个索引条目，每一个索引条目都有一个键值和一个RowID，其中键值可以是一列或者多列的组合。
　　（一）索引按存储方法分类，可以分为2类：B*树索引和位图索引。
　　（1）B*树索引的存储结构类似书的索引结构，有分支和叶两种类型的存储数据块，分支块相当于书的大目录，叶块相当于索引到的具体的书页。Oracle用B*树机制存储索引条目，以保证用最短路径访问键值。默认情况下大多使用B*树索引，该索引就是通常所见的唯一索引、逆序索引。
　　（2）位图索引存储主要用于节省空间，减少oracle对数据块的访问。它采用位图偏移方式来与表的行ID号对应，采用位图索引一般是重复值太多的表字段。位图索引之所以在实际密集型OLTP（联机事物处理）中用的比较少，是因为OLTP会对表进行大量的删除、修改、新建操作。Oracle每次进行操作都会对要操作的数据块加锁。以防止多人操作容易产生的数据库锁等待甚至死锁现象。在OLAP（联机分析处理）中应用位图有优势，因为OLAP中大部分是对数据库的查询操作，而且一般采用数据仓库技术，所以大量数据采用位图索引节省空间比较明显。当创建表的命令中包含有唯一性关键字时，不能创建位图索引，创建全局分区索引时也不能用位图索引。
　　（二）索引按功能和索引对象分还有以下类型。
　　（1）唯一索引意味着不会有两行记录相同的索引键值。唯一索引表中的记录没有RowID，不能再对其建立其他索引。在oracle10g中，要建立唯一索引，必须在表中设置主关键字，建立了唯一索引的表只按照该唯一索引结构排序。
　　（2）非唯一索引不对索引列的值进行唯一性限制。
　　（3）分区索引是指索引可以分散地存在于多个不同的表空间中，其优点是可以提高数据查询的效率。
　　（4）未排序索引也称为正向索引。Oracle10g数据库中的行是按升序排序的，创建索引时不必指定对其排序而使用默认的顺序。
　　（5）逆序索引也称反向索引。该索引同样保持列按顺序排列，但是颠倒已索引的每列的字节。
　　（6）基于函数的索引是指索引中的一列或者多列是一个函数或者表达式，索引根据函数或表达式计算索引列的值。可以将基于函数的索引建立创建成位图索引。
　　另外，按照索引所包含的列数可以把索引分为单列索引和复合索引。索引列只有一列的索引为单列索引，对多列同时索引称为复合索引。

 

4.2查询/创建索引

--查看当前用户下所有索引

select * from user_indexes;

select index_name,index_type,table_name,tablespace_name,status from user_indexes;

# 索引状态status: VALID 索引有效；UNUSABLE 索引不可用

 

 

--查看该表的所有索引

select * from user_indexes where table_name = 'TS_GATE'

 

--查看该表的所有索引列

select * from user_ind_columns where table_name= 'TS_GATE';

 

1.创建索引语法

CREATE [UNIQUE] | [BITMAP] INDEX index_name  --unique表示唯一索引ON table_name([column1 [ASC|DESC],column2    --bitmap，创建位图索引

[ASC|DESC],…] | [express])

[TABLESPACE tablespace_name]

[PCTFREE n1]                                 --指定索引在数据块中空闲空间

[STORAGE (INITIAL n2)]

[NOLOGGING]                                  --表示创建和重建索引时允许对表做DML操作，默认情况下不应该使用

[NOLINE]

[NOSORT];                                    --表示创建索引时不进行排序，默认不适用，如果数据已经是按照该索引顺序排列的可以使用

如果不加创建索引的类型，默认是创建非唯一索引

 

创建不唯一索引

create index emp_ename on employees(ename)

tablespace users

storage(......)

pctfree 0;

创建唯一索引

create unique index emp_email on employees(email)

tablespace users;

创建位图索引

create bitmap index emp_sex on employees(sex)

tablespace users;

创建反序索引

create unique index order_reinx on orders(order_num,order_date)

tablespace users

reverse;

创建函数索引(函数索引即可以是普通的B树索引，也可以是位图索引)

create index emp_substr_empno

on employees(substr(empno,1,2))

tablespace users;

 

2.修改索引

1）重命名索引

alter index index_sno rename to bitmap_index;

2)合并索引（表使用一段时间后在索引中会产生碎片，此时索引效率会降低，可以选择重建索引或者合并索引,合并索引方式更好些，无需额外存储空间，代价较低）

alter index index_sno coalesce;

3)重建索引

　　方式一：删除原来的索引，重新建立索引

　　方式二：alter index index_sno rebuild;

3.删除索引

drop index index_sno;

4.查看索引

select index_name,index-type, tablespace_name, uniqueness from all_indexes where table_name ='tablename';

 

 -- eg:    create index index_sno on student('name');select * from all_indexes where table_name='student';

 

4.3重建索引

一、重建索引的前提

1、表上频繁发生update,delete操作；
2、表上发生了alter table ..move操作（move操作导致了rowid变化）

 

二、重建索引的方式

 

1 、直接重建：

举例：

alter index indexname rebuild;

alter index indexname rebuild online;
说明：第二种方式比较快，可以在24*7环境中实现，建议使用此方式。

2、drop 原来的索引，然后再创建索引;

举例：
删除索引：drop index IX_PM_USERGROUP;
创建索引：create index IX_PM_USERGROUP on T_PM_USER (fgroupid);

说明：此方式耗时间，无法在24*7环境中实现，不建议使用。

 

注意点：

1、 执行rebuild操作时，需要检查表空间是否足够；
2、虽然说rebuild online操作允许dml操作，但是还是建议在业务不繁忙时间段进行；

Rebuild操作会产生大量redo log ；

5. Sequence序列

5.1定义

在Oracle数据库中，序列(SEQUENCE)其实是序列号生成器，可以为表中的行自动生成序列号，产生一组等间隔的数值(类型为数字)。其主要的用途是生成表的主键值，可以在插入语句中引用，也可以通过查询检查当前值，或使序列增至下一个值。

 

创建序列需要CREATE SEQUENCE系统权限。

序列的创建语法如下：

CREATE SEQUENCE 序列名 [INCREMENT BY n] [START WITH n] [{MAXVALUE/ MINVALUE n|NOMAXVALUE}] [] [{CACHE n|NOCACHE}];

 

参数说明如下：
INCREMENT BY 用于定义序列的步长，如果省略，则默认为1，如果出现负值，则代表序列的值是按照此步长递减的。

START WITH 定义序列的初始值(即产生的第一个值)，默认为1。

MAXVALUE 定义序列生成器能产生的最大值。选项NOMAXVALUE是默认选项，代表没有最大值定义，这时对于递增序列，系统能够产生的最大值是10的27次方;对于递减序列，最大值是-1。

MINVALUE定义序列生成器能产生的最小值。选项NOMAXVALUE是默认选项，代表没有最小值定义，这时对于递减序列，系统能够产生的最小值是?10的26次方;对于递增序列，最小值是1。

CYCLE和NOCYCLE 表示当序列生成器的值达到限制值后是否循环。CYCLE代表循环，NOCYCLE代表不循环。如果循环，则当递增序列达到最大值时，循环到最小值;对于递减序列达到最小值时，循环到最大值。如果不循环，达到限制值后，继续产生新值就会发生错误。

CACHE(缓冲)定义存放序列的内存块的大小，默认为20。NOCACHE表示不对序列进行内存缓冲。对序列进行内存缓冲，可以改善序列的性能。

5.2 查询/创建使用

--查询用户下的所有序列

select * from user_squences

 

1、Create Sequence

    首先要有CREATE SEQUENCE或者CREATE ANY SEQUENCE权限。

 

    创建语句如下：

 

CREATE SEQUENCE seqTest

INCREMENT BY 1 -- 每次加几个

START WITH 1 -- 从1开始计数

NOMAXvalue -- 不设置最大值

NOCYCLE -- 一直累加，不循环

CACHE 10; --设置缓存cache个序列，如果系统down掉了或者其它情况将会导致序列不连续，也可以设置为---------NOCACHE

 

2、得到Sequence值

 

定义好sequence后，你就可以用currVal，nextVal取得值。

    CurrVal：返回 sequence的当前值

    NextVal：增加sequence的值，然后返回 增加后sequence值

  得到值语句如下：

SELECT Sequence名称.CurrVal FROM DUAL;

 

  如得到上边创建Sequence值的语句为：

select seqtest.currval from dual

 

在Sql语句中可以使用sequence的地方：

    - 不包含子查询、snapshot、VIEW的 SELECT 语句

    - INSERT语句的子查询中

    - INSERT语句的values中

    - UPDATE 的 SET中

 

如在插入语句中

 insert into 表名(id,name)values(seqtest.Nextval,'sequence 插入测试');

 

 注：

    - 第一次NEXTVAL返回的是初始值；随后的NEXTVAL会自动增加你定义的INCREMENT BY值，然后返回增加后的值。

 

      CURRVAL 总是返回当前SEQUENCE的值，但是在第一次NEXTVAL初始化之后才能使用CURRVAL，否则会出错。

 

       一次NEXTVAL会增加一次 SEQUENCE的值，所以如果你在同一个语句里面使用多个NEXTVAL，其值就是不一样的。

    - 如果指定CACHE值，ORACLE就可以预先在内存里面放置一些sequence，这样存取的快些。cache里面的取完后，oracle自动再取一组 到cache。 使用cache或许会跳号， 比如数据库突然不正常down掉（shutdown abort),cache中的sequence就会丢失. 所以可以在create sequence的时候用nocache防止这种情况。

 

3、Alter Sequence

    拥有ALTER ANY SEQUENCE 权限才能改动sequence. 可以alter除start至以外的所有sequence参数.如果想要改变start值，必须 drop sequence 再 re-create。

 例：

alter sequence SEQTEST maxvalue 9999999;

    另： SEQUENCE_CACHE_ENTRIES参数，设置能同时被cache的sequence数目。

 

 4、Drop Sequence

DROP SEQUENCE seqTest;

 

 5、一个例子

 

 复制代码

create sequence SEQ_ID

minvalue 1

maxvalue 99999999

start with 1

increment by 1

nocache

order;

 

建解发器代码为：

 

create or replace trigger tri_test_id

  before insert on S_Depart   --S_Depart 是表名

  for each row

declare

  nextid number;

begin

  IF :new.DepartId IS NULLor :new.DepartId=0 THEN --DepartId是列名

    select SEQ_ID.nextval --SEQ_ID正是刚才创建的

    into nextid

    from sys.dual;

    :new.DepartId:=nextid;

  end if;

end tri_test_id;

转载于:https://www.cnblogs.com/lisonglin/p/10136861.html