列增加好后，
alter table scott.tt modify(deptno invisible);
alter table scott.tt modify(deptno visible);
10g
rename ee4 to e41
create table e4 as select empno,ename,job,hirdate
修改基表，不推荐使用
alter table emp disable constraint pk_name;
alter table emp enable constraint pk_name;
alter table emp disable constraint pk_name cascade; 禁用级联的，禁用后连个都会变为disable的，启用的话，首先启用主依赖，在启用次依赖
alter table dept drop (deptno); 删除出错，由于存在级联
alter table dept drop (deptno) cascade constraints; 这样删除就不会出错
SQL> alter table dept drop (deptno);
alter table dept drop (deptno)
*
ERROR at line 1:
ORA-12992: cannot drop parent key column
SQL> alter table dept drop (deptno) cascade constraints;
Table altered.
11g、12c只读
SQL> alter table emp read only;
Table altered.
SQL> update emp set sal=sal+1;
update emp set sal=sal+1
*
ERROR at line 1:
ORA-12081: update operation not allowed on table "SCOTT"."EMP"
SQL> alter table emp read write;
Table altered.
约束与索引的名字分开
SQL> create table a1(id number primary key using index(create unique index
2 a1_id_i on a1(id)),name varchar2(2));
Table created.
SQL> insert into a1 values(1,'A');
1 row created.
SQL> commit;
Commit complete.
SQL> alter table a1 move;
Table altered.
SQL> select index_name,status from user_indexes;
INDEX_NAME STATUS
------------------------------ --------
A1_ID_I UNUSABLE
BIN$/o6ox8o9iMjgQ2YAqMDn3Q==$0 VALID
EMP_ENAME_I VALID
PK_EMP VALID
PK_DEPT VALID
SQL> insert into a1 values(2,'a');
insert into a1 values(2,'a')
*
ERROR at line 1:
ORA-01502: index 'SCOTT.A1_ID_I' or partition of such index is in unusable
state
SQL>alter index a1_id_i rebuild
Index altered.
flashback闪回 不适合于sys用户的
drop table a purge;
临时表
事务级别的 事务结束数据不在了
会话级别的 会话结束数据不再了
create global temporary table cart on commit delete rows;
外部表
文本文件存储行
二进制文件存储
不支持DML操作
datapump数据加载卸载
SQL> create directory ext as '/tmp';
Directory created.
SQL> grant read,write on directory ext to scott;
Grant succeeded.
建立脚本
create table fs(id number,name varchar2(10),loc varchar2(11))
organization external
(
type oracle_loader
default directory ext
access parameters(
records delimited by newline
fields terminated by ',' MISSING FIELD VALUES ARE NULL
(id,name,loc))
location('a.txt')
)
reject limit unlimited
/
SQL> select * from fs;
ID NAME LOC
---------- ---------- -----------
1 a f
2 f g
3 f
4 h
SQL> ho echo "6,u,i">>/tmp/a.txt
SQL> select * from fs;
ID NAME LOC
---------- ---------- -----------
1 a f
2 f g
3 f
4 h
6 u i
SQL> ho sed '2,4d' -i /tmp/a.txt
SQL> select * from fs;
ID NAME LOC
---------- ---------- -----------
1 a f
6 u i
**二进制文件平台之间迁移
***先导出文件，得到文本文件，然后在新的平台建立外部表执行导入操作。
数据字典
数据字典下面有基表，动态、静态
由基表和可以访问的视图构成
user/all/dba
v$
user_objects 自已拥有的所有对象
all_objects 有权限访问的所有对象
select * from dictionary
select * from dict
dba_ all_ user_ dba
all_ user_ 普通用户
user_tables all_tables
user_tab_columns all_tab_columns
index
user_indexes user_ind_columns
all_indexs all_ind_columns
constraint
user_constraints user_cons_columns
all_ all_
view
user_views all_views
sequence
user_sequence all_sequences
synonym
user_synonyms all_synonyms
directory user_directories all_directories
注释
comments on table |column is '......';
user_tab_comments
user_col_comments
SQL> comment on table dept is 'deptment table';
Comment created.
SQL> select * from user_tab_comments;
TABLE_NAME TABLE_TYPE
------------------------------ -----------
COMMENTS
--------------------------------------------------------------------------------
SALGRADE TABLE
FS TABLE
FAS TABLE
TABLE_NAME TABLE_TYPE
-- |