1、SET UNUSED COLUMNS的若干问题
1.1 UNUSED后,所有的索引、约束和静态定义都被移除。
All constraints, indexes, and statistics defined on the column are also removed.
2、external table 外部表的特点
Oracle Database allows you read-only access to data in external tables.
You can select, join, or sort external table data. You can also create views and synonyms for external tables. However,no DML operations (UPDATE, INSERT, or DELETE) are possible, and no indexes can be created, on external tables.
3、FLASHBACK TABLE
3.1 Use the FLASHBACK TABLE statement to restore an earlierstateof a table in the event of human or application error. The time in the past to which the table can be flashed back is dependent on the amount of undo data in the system. Also, Oracle Database cannot restore a table to an earlier state across any DDL operations that change thestructure of the table.
3.2 You cannot roll back a FLASHBACK TABLE statement. However, you can issue another FLASHBACK TABLE statement and specify a time just prior to the current time. Therefore, it is advisable to record the current SCN before issuing a FLASHBACK TABLE clause.
4、index的其他问题
4.1 当指定主键后,不能再在主键上建索引,因为ORACLE 已经自动建了索引。即不能在一个列上建多个索引。
例子如下,table ord 的主键是ord_no
SQL> create index ord_idx on ord(ord_no);
create index ord_idx on ord(ord_no)
*
ERROR at line 1:
ORA-01408: such column list already indexed
5、数据库非正常关机中的sequence
The database might skip sequence numbers if you choose to cache a set of sequence numbers. For example, when an instance abnormally shuts down (for example, when an instance failure occurs or a SHUTDOWN ABORT statement is issued), sequence numbers that have been cached but not used are lost. Also, sequence numbers that have been used but not saved are lost as well. The database might also skip cached sequence numbers after an export and import
sequence numbers that have been used but not saved are lost as well.
那些已经被使用但还没有被保存的序列数字也会丢失。(问题:这里的保存怎么理解)
6、private synonym 和public synonym 区别
private synonym:只有有该对象访问权限的用户才能访问这个对象的synonym
public synonym:所有用户都能访问该synonym
示例:
SQL> conn test/test;
Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0
Connected as test
SQL> select count(*) from hr.employees;
COUNT(*)
----------
107
SQL> create synonym employees for hr.employees;
Synonym created
SQL> select count(*) from employees;
COUNT(*)
----------
107
SQL> conn sys/111 as sysdba;
Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0
Connected as SYS
SQL> select count(1) from employees;
select count(1) from employees
ORA-00942: 表或视图不存在
SQL> conn test/test;
Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0
Connected as test
SQL> drop synonym employees;
Synonym dropped
SQL> create public synonym employees for hr.employees;
Synonym created
SQL> select count(*) from employees;
COUNT(*)
----------
107
SQL> conn sys/111 as sysdba;
Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0
Connected as SYS
SQL> select count(*) from employees;
COUNT(*)
----------
107
SQL>