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;

沒有任何資料列于選取ACCESS

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];

 

 

 

        

相关文章