為什么80%的碼農都做不了架構師？>>> ??

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 earlier state of 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 the structure 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 testSQL> select count(*) from hr.employees;COUNT(*) ----------107SQL> create synonym employees for hr.employees;Synonym createdSQL> select count(*) from employees;COUNT(*) ----------107SQL> conn sys/111 as sysdba; Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0 Connected as SYSSQL> select count(1) from employees;select count(1) from employeesORA-00942: 表或視圖不存在SQL> conn test/test; Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0 Connected as testSQL> drop synonym employees;Synonym droppedSQL> create public synonym employees for hr.employees;Synonym createdSQL> select count(*) from employees;COUNT(*) ----------107SQL> conn sys/111 as sysdba; Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0 Connected as SYSSQL> select count(*) from employees;COUNT(*) ----------107SQL>

轉載于:https://my.oschina.net/kursk/blog/67763

總結

以上是生活随笔為你收集整理的ORACLE SQL总结六：管理方案对象的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。