ACCESS(Les13 Managing Schema Objects)[20171220]

目的:

    增加约束

    创设索引

    使用CREATE TABLE语句创建索引

    成立函数索引

    删除列和将列置为UNUSED

    平台FLASHBACK闪回操作

    成立临时表

    成立和运用外部表

    

ALTER TABLE语句

    -增加列

    -修改已存在的列

    -定义列的默许值

    -删除列

    

    语法

        ALTER TABLE table_name ADD (column datatype [DEFAULT expr]
[,column datatype] . . .);

        ALTER TABLE table_name MODIFY (column datatype [DEFAULT expr]
[,column datatype] . . .);

        ALTER TABLE table_name DROP (column);

        ALTER TABLE table_name SET UNUSED column_name;

        ALTER TABLE table_name DROP UNUSED COLUMNS;        

        ALTER TABLE table_name READ ONLY;

        ALTER TABLE table_name READ WRITE;

 

 

        

        增加列

            -新增列无法指定列的浮现地点。新列成为终极一列

        注意:扩张新列NOT NULL约束时,假诺表中已有材料NOT
NULL约束将破产。

    

        修改列

            -可以对列的数据类型、大小和默许值举办转移。

            -知足以下规则,可以减掉列的拉长率

                -该列仅包蕴空值

                -空表

                -减弱的列宽不小于该列中的现有值

           
-假设该列子包括空值,则可以变更数据类型。char-to-varchar2有多少也得以转换。

            -对列的默许值更改只会影响一而再的插入。

       

        删除列

            -该列包罗或不带有数据

            -使用ALTER TABLE 语句,两遍只好删除一列

            -表格中必须至里胥留一列

            -列被删除后,无法复苏

           
-若是列是约束的一部分照旧是索引键上的栏(测试主键和外键),则不可能删除列,除非添加了cascade级联选项

           
-如果列上有雅量的数码,删除列须要一些岁月。那种景况下最好将其安装为UNUSED状态,并在系统上的用户较少时将其遗弃,以幸免扩充锁定。

       
注意:某些列永远不可以被去除,例如结合分区表分区键的列或结成索引协会表的主键一部分的列。

            

        SET UNUSED

            -SET
UNUSED将一列或多列标记为未拔取,以便在系统资源要求较低时可将其除去。它并不会从实际上空间举行删除,因而响应时间比执行DROP子句更快。列被标记为未使用将来,将无权访问该列。

            -SET UNUSED音讯存储于USER_UNUSED_COL_TABS数据字典中。

        注意:SET UNUSED的标准化与DROP相似

 

    约束

        -可以增加、删除、启用/关闭约束,但无法更改其布局。

        -增加NOT NULL约束,必须接纳ALTER TABLE MODIFY语句。

      注意:只好在空表的列上扩展NOT NULL约束照旧列上每一行都有值。      
 

        

        语法

            ALTER TABLE <table_name > ADD [CONSTRAINT
<constraint_name>] TYPE (<column_name>);

            

        -ON DELETE CASCADE级联删除

           
允许子表引用的父键数据被删去,但不被更新。当父表中键值删除时,子表中看重值的保有行也被剔除。在FOREIGN
KEY外键约束中指定ON DELELTE CASCADE选项。

        -ON DELETE SET NULL级联置空

            当父表中键值删除时,子表中看重值置空。

        

        延迟约束

            -DEFERRABLE or NOT DEFERRABLE

            -INITIALLY DEFERRED or INITIALLY IMMEDIATE

           
推迟约束检查,直达事务为止时才举办约束检查。如果违反约束,则整个业务将回滚。

            

            ALTER TABLE <table_name> ADD CONSTRAINT
<constraint_name> PRIMARY KEY (column_name) DEFERRABLE INITIALLY
DEFERRED;

            SET CONSTRAINTS <constraint_name> IMMEDIATE;

            ALTER SESSION SET CONSTRAINTS=IMMEDIATE;

 

        删除约束

            ALTER TABLE <table_name> DROP CONSTRAINT
<constraint_name>;

            ALTER TABLE <table_name> DROP PRIMARY KEY|UNIQUE
(column) | CONSTRAINT <constraint_name> [CASCADE];

 

        关闭约束

            ALTER TABLE <table_name> DISABLE CONSTRAINT
<constraint_name> [CASCADE];

           注意:关闭唯一性约束和主键约束时,相关的索引会举办删除。

        启用约束

            ALTER TABLE <table_name> ENABLE CONSTRAINT
<constraint_name> ;

            注意:启用主键约束时,看重此主键的外键约束不会级联启用。

        级联约束

            ALTER TABLE <table_name> DROP COLUMN
<column_name> CASCADE CONSTRAINTS;->若是列上有引用使用cascade
constraints选项可以级联删除列。

 

    索引

        索引类型:

            唯一性索引:PRIMARY KEY和UNIQUE约束会活动创立唯一性索引。

            非唯一性索引:用户自定义成立索引。CREATE INDEX/CREATE TABLE

            

           
注意:创立唯一性索引时,提出创造一个唯一性约束,因为它会隐式的创造一个唯一性索引。

        

        重用Index

            ALTER TABLE <table_name> ADD PRIMARY KEY
(column_name) USING INDEX <index_name>;

    

        基于函数的目录

            -索引上是表明式可以是:列、约束、SQL 函数和用户自定义函数。

           
注意:使用函数索引必须将参数QUERY_REWRITE_ENABLED设定为TRUE

            CREATE INDEX <index_name> ON
table_name(UPPER(column_name));

            

            select * from ocp_047_base_function where
upper(col)=upper(‘name’);

            select * from table(dbms_xplan.display_cursor());

        删除索引

            DROP INDEX <index_name>;

            

    删除表

        DROP TABLE <table_name> [PURGE];

        Oracle
10g引入了一个删除表的新职能。删除表时,数据库不会马上放飞与该表关联的空中。而是将表重命名放入回收站中,假诺发现错误地删除,则足以选拔FLASHBACK
TABLE语句復苏该表。倘若想登时释放空间可以行使DROP TABLE [PRUGE]语句。

        注意:SYS(AS SYSDBA)账户删除的表不会放入回收站中。

   

FLASHBACK TABLE闪回

    FLASHBACK TABLE [schema .] table [, [schema.] table …] TO {
TIMESTAMP | SCN } expr [ { ENABLE | DISABLE} TRIGGERS];

        -Oracle 10g提供的新特色DDL命令,FLASHBACK TABLE

        -将表復苏到较早的日子点或预防意外删除和修改表

        -FLASHBACK TABLE
是一种自助修复工具,用于恢复生机表中的多寡以及索引或视图

        -数据库联机时做到,只需回滚指定的表。

       
-与观念复苏机制比较,此作用提供了易用性、可用性和更快过来等优势。

        -默认处境下拥有关乎的表都禁用触发器,也得以指定ENABLE
TRIGGERS来启用。

 

    SQL>  show recyclebin

ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME



EMP#             BIN$X5JCv1b5JHDgU+sCHqzk8Q==$0
TABLE        2017-12-05:13:39:38

EMP$_#           BIN$X5JCv1b+JHDgU+sCHqzk8Q==$0
TABLE        2017-12-05:13:40:12

EmP              BIN$X5JCv1cDJHDgU+sCHqzk8Q==$0
TABLE        2017-12-05:13:40:21

INSERT_TAB       BIN$X6NQR/0SAVbgU+sCHqzqQg==$0
TABLE        2017-12-06:10:39:32

INSERT_TAB       BIN$X6NQR/0MAVbgU+sCHqzqQg==$0
TABLE        2017-12-06:10:20:06

    SQL> desc recyclebin

Name                                      Null?    Type



OBJECT_NAME                               NOT NULL VARCHAR2(30)

ORIGINAL_NAME                                      VARCHAR2(32)

OPERATION                                          VARCHAR2(9)

TYPE                                               VARCHAR2(25)

TS_NAME                                            VARCHAR2(30)

CREATETIME                                         VARCHAR2(19)

DROPTIME                                           VARCHAR2(19)

DROPSCN                                            NUMBER

PARTITION_NAME                                     VARCHAR2(32)

CAN_UNDROP                                         VARCHAR2(3)

CAN_PURGE                                          VARCHAR2(3)

RELATED                                   NOT NULL NUMBER

BASE_OBJECT                               NOT NULL NUMBER

PURGE_OBJECT                              NOT NULL NUMBER

SPACE                                              NUMBER

SQL> select original_name,operation,droptime from recyclebin;

ORIGINAL_NAME                    OPERATION DROPTIME


EMP#                             DROP      2017-12-05:13:39:38

EMP$_#                           DROP      2017-12-05:13:40:12

EmP                              DROP      2017-12-05:13:40:21

TAB_04                           DROP      2017-12-06:10:00:19

T01                              DROP      2017-12-20:17:11:48

INSERT_TAB                       DROP      2017-12-06:10:39:32

OCP_047_BASE_FUNCTION_V0         DROP      2017-12-20:17:05:53

OCP_047_BASE_FUNCTION            DROP      2017-12-20:17:05:53

INSERT_TAB                       DROP      2017-12-06:10:20:06

        

        闪回表

            FLASHBACK TABLE <table_name> TO BEFORE DROP;

    临时表

        CREATE GLOBAL TEMPORARY TABLE <table_name> ON COMMIT
[PRESERVE | DELETE ] ROWS;

        -PRESERVE ROWS :保留至会话截止

        -DELETE ROWS:保留至作业甘休

 

        14:17:38 SQL> create global temporary table temp_t0

        14:18:23   2  on commit preserve rows as select * from
employees where rownum<20;

已确立表格.

        14:19:21 SQL> select count(*) from temp_t0;

  COUNT(*)                                                                                                    

———-                                                                                                    

        19                                                                                                    

14:19:25 SQL> commit;

確認已毕.

14:19:29 SQL> select count(*) from temp_t0;

  COUNT(*)                                                                                                    

———-                                                                                                    

        19                                                          

        14:20:27 SQL> create global temporary table temp_t0 on
commit

       14:28:25   2  delete rows as select * from employees where
rownum<20;

已创设表格.

14:28:44 SQL> select * from temp_t0;

沒有任何資料列被選取

14:28:51 SQL> select count(*) from temp_t0;

  COUNT(*)


         0

14:29:06 SQL> insert into temp_t0  select * from employees;

已成立 107 個資料列.

14:29:23 SQL> select count(*) from temp_t0;

  COUNT(*)


       107

14:29:33 SQL> commit;

確認完成.

14:29:36 SQL> select count(*) from temp_t0;

  COUNT(*)


         0

14:29:37 SQL>

    外部表

       
外部表是一个只读表,元数据存储在数据库中,但其数额存储在数据库之外。外部表可以被认为是一个视图,可以对外表数据开展SQL查询,而不须要将表面数据加载到数据库中。

        外部表不能展开DML操作、建立目录。但足以拔取CREATE TABLE AS
SELECT加载数据到数据库中。

        外部表提供了四个访问驱动程序:ORACLE_LOADER和ORACLE_DATAPUMP

            -ORACLE_LOADER将表面数据映射到数据库

            -ORACLE_DATAPUMP将数据库资料卸载到表面文件

 

       外部表的成立

                -创建目录提须要外部表 CREATE [OR REPLACE] DIRECTORY
<directory_name> AS ‘directory_path’;

                -授权目录给接纳账户 GRANT READ ON DIRECTORY
<directory_name> TO schema_name;

 

        ORACLE_DATAPUMP

                CREATE TABLE extract_emps

                    ORGANIZATION EXTERNAL (TYPE ORACLE_DATAPUMP

                                                                   
DEFAULT DIRECTORY …

                                                                   
ACCESS PARAMETERS (…)

                                                                   
LOCATION (…)

                                                                   
PARALLEL 4

                                                                   
REJECT LIMIT UNLIMITED

                    AS

                    SELECT * FROM …;

 

   create table extract_datapump

   (last_name,employee_id,salary)

    organization external    

    (type oracle_datapump

    default directory expdp_path

    location(’emp_01.bat’,’emp_02.bat’))

    parallel

    as

    select last_name,employee_id,salary from employees

    ;

                    [oracle@xxxxx bak]$ strings emp_01.bat|tail

      Perkins

      Bell

      Everett

 

 

        ORACLE_LOADER

                CREATE TABLE extract_loader ( –表名称

                    fname char(25), –字段名

                    lname char(25)) –字段名

                ORGANIZATION EXTERNAL –外部表

                    (TYPE ORACLE_LOADER  –外部表驱动器

                    DEFAULT DIRECTORY <directory_name> –默许目录

                    ACCESS PARAMETERS –标识访问参数

                        (RECORDS DELIMITED BY NEWLINE 

                        NOBADFILE –不爆发故障文件

                        NOLOGFILE –不爆发日志文件

                        FIELDS TERMINATED BY ‘,’ –区分符

                        (fname POSITION (1:20) CHAR,
–定义文件类型对应值

                         lname POSITION (22:41) CHAR))

                        LOCATION (‘file_name’))–加载文件名称

                    PARALLEL 5 –并行度

                    REJECT LIMIT 200; –限制错误的个数及甘休查询

                

        

           外部表创建语法

                CREATE TABLE <table_name>

                    (<col_name> <datatype>, … )

                    ORGANIZATION EXTERNAL

                        (TYPE <access_driver_type>

                        DEFAULT DIRECTORY <directory_name>

                        ACCESS PARAMETERS

                            ( … ) )

                        LOCATION (‘<localtion_specifier>’) )

                REJECT LIMIT [ 0 | <number> | UNLIMITED];

 

 

 

        

相关文章