create table user(
id number(3),
username varchar2(10),
password varchar2(10),
cid number(3),
sex varcher(4)
);
create table card(
id number(3),
name varchar2(10)
);
--添加主键
alter table user add constraint UAER_PK primary key(id);
--添加外键
alter table user add constraint UAER_FK foreign key(cid) references card(id);
--添加检查约束
alter table user add constraint UAER_CHK check (sex in ('boy','girl'));
---------------
\\操作列
alter table tablename add (columnname datatype[default value][null/not null],......);--增加列
alter table tablename modify(columnname datatype[default value][null/not null],......);--修改列
alter table tablename drop(columnname);--删除列
alter table tablename rename column col_old_name to col_new_name;--修改列名
--添加列
alter table user add(aa varchar2(10) default 'aaaa' not null);
--修改列
alter table user modify(username varchar2(1000));
--删除列
alter table user drop(password);
--修改列名
alter table user rename column username to username_new;
----------------
\\编译指定的对象:
过程:
alter procedure procedure_name compile;
函数:
alter function funtion_name compile;
包:
alter package package_name compile;
包体:
alter package package_name compile body;
----------------
\\把索引移动到T_IDX索引表空间
alter index USER_PK rebuild tablespace t_idx;
\\创建路径
CREATE OR REPLACE DIRECTORY DIR_CUST AS '/oracle/aaa';
\\把表移动到TB_T表空间
alter table user move tablespace TB_T
-----------------------------------------------------------
\\主外键生效失效
alter table USER disable constraint UAER_PK ;
alter table USER enable constraint UAER_PK ;
\\触发器生效失效
alter table USER disable all triggers;
alter table USER enable all triggers;
\\解锁
alter user zzz account unlock;