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

 

 

 

        

相关文章