惜分飞

ORACLE 12C可以通过expdp导出view数据

发表于 2012 年 12 月 20 日由惜分飞

有时候,多么的希望ORACLE能够导出某个视图中的数据,然后通过这个视图来迁移需要的数据,现在ORACLE 12C通过expdp的views_as_tables来实现了该功能,把视图当作一个普通表从而导出数据,导入的时候直接和一个正常表一样,通过视图的导出,表的导入来实现相关需求
准备测试环境

SQL> SELECT * FROM V$VERSION;

BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.0.2 - 64bit                         0
PL/SQL Release 12.1.0.0.2                                                                 0
CORE    12.1.0.0.2                                                                        0
TNS for Linux: Version 12.1.0.0.2                                                         0
NLSRTL Version 12.1.0.0.2                                

SQL> create table t_lx as select * from v$log; 

Table created.

SQL> create table t_xl as select * from v$logfile;

Table created.

SQL> create view v_xifenfei  as
  2   SELECT thread#,
  3           a.sequence#,
  4           a.group#,
  5           TO_CHAR (first_change#, '9999999999999999') "SCN",
  6           a.status,
  7           MEMBER
  8      FROM t_lx a, t_xl b
  9     WHERE a.group# = B.GROUP#
 10  ORDER BY a.sequence# DESC;

View created.

SQL> col member for a50    
SQL> set lines 134
SQL> select * from v_xifenfei;

   THREAD#  SEQUENCE#     GROUP# SCN               STATUS           MEMBER
---------- ---------- ---------- ----------------- ---------------- -------------------------------------------
         1         30          3            391892 CURRENT          /u01/app/oracle/oradata/xifenfei/redo03.log
         1         29          2            377363 INACTIVE         /u01/app/oracle/oradata/xifenfei/redo02.log
         1         28          1            374892 INACTIVE         /u01/app/oracle/oradata/xifenfei/redo01.log

expdp结合VIEWS_AS_TABLES导出视图

[oracle@xifenfei ~]$ expdp xff/xifenfei views_as_tables=v_xifenfei directory=data_pump_dir dumpfile=xifenfei.dmp

Export: Release 12.1.0.0.2        on Sun Dec 16 07:56:48 2012

Copyright (c) 1982, 2012, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.0.2 - 64bit     
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "XFF"."SYS_EXPORT_TABLE_01":xff/******** views_as_tables=v_xifenfei directory=data_pump_dir
 dumpfile=xifenfei.dmp 
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE_DATA
Total estimation using BLOCKS method: 16 KB
Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE
. . exported "XFF"."V_XIFENFEI"                          7.390 KB       3 rows
Master table "XFF"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for XFF.SYS_EXPORT_TABLE_01 is:
  /u01/app/oracle/admin/xifenfei/dpdump/xifenfei.dmp
Job "XFF"."SYS_EXPORT_TABLE_01" successfully completed at Sun Dec 16 07:58:17 2012 elapsed 0 00:00:56

impdp导入数据

[oracle@xifenfei ~]$ impdp xff/xifenfei remap_table=v_xifenfei:v_xff directory=data_pump_dir dumpfile=xifenfei.dmp

Import: Release 12.1.0.0.2        on Sun Dec 16 08:06:06 2012

Copyright (c) 1982, 2012, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.0.2 - 64bit     
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "XFF"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "XFF"."SYS_IMPORT_FULL_01":xff/******** remap_table=v_xifenfei:v_xff directory=data_pump_dir
 dumpfile=xifenfei.dmp 
Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE
Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE_DATA
. . imported "XFF"."V_XFF"                               7.390 KB       3 rows
Job "XFF"."SYS_IMPORT_FULL_01" successfully completed at Sun Dec 16 08:06:20 2012 elapsed 0 00:00:10

验证数据

SQL> COL OBJECT_NAME FOR A20
SQL> select object_type,object_name from user_objectS where object_name like 'V_X%';

OBJECT_TYPE             OBJECT_NAME
----------------------- --------------------
VIEW                    V_XIFENFEI
TABLE                   V_XFF

SQL> col member for a50
SQL> set lines 134
SQL> select * from v_XFF;

   THREAD#  SEQUENCE#     GROUP# SCN               STATUS           MEMBER
---------- ---------- ---------- ----------------- ---------------- --------------------------------------------
         1         30          3            391892 CURRENT          /u01/app/oracle/oradata/xifenfei/redo03.log
         1         29          2            377363 INACTIVE         /u01/app/oracle/oradata/xifenfei/redo02.log
         1         28          1            374892 INACTIVE         /u01/app/oracle/oradata/xifenfei/redo01.log

通过测试证明在12C中ORACLE的expdp/impdp可以实现导出视图数据,进入导入到[其他库]其他表中

发表在 ORACLE 12C | 标签为 ORACLE 12C | 3 条评论

ORCLE 12C 增加列,无默认值

发表于 2012 年 12 月 19 日由惜分飞

对11gR2比较熟悉的朋友应该比较清楚,在该版本中引入了一个新的特性,能够快速的增加一个新列,具体见Oracle 11g增加列,并带默认值的新特性,但是这个功能在该版本中总有个不足,需要设置默认值,在有些情况下,有些列就是不需要默认值,在12C的版本中,解决了这个鸡肋,能够快速增加一个列而且可以是不指定默认值(默认值为NULL)
数据库12C版本

SQL> select * from v$version;

BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.0.2 - 64bit                         0
PL/SQL Release 12.1.0.0.2                                                                 0
CORE    12.1.0.0.2                                                                        0
TNS for Linux: Version 12.1.0.0.2                                                         0
NLSRTL Version 12.1.0.0.2                                                                 0

创建模拟表

SQL> create table t_xifenfei(id number,name varchar2(20));

Table created.

SQL> desc t_xifenfei
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER
 NAME                                               VARCHAR2(20)

SQL> insert into t_xifenfei values (1,'www.xifenfei.com');

1 row created.

SQL> insert into t_xifenfei values (2,'www.xifenfei.com');

1 row created.

SQL> insert into t_xifenfei values (3,'www.xifenfei.com');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from t_xifenfei;

        ID NAME
---------- --------------------
         1 www.xifenfei.com
         2 www.xifenfei.com
         3 www.xifenfei.com

第一次dump block

SQL> select   rowid, 
  2   dbms_rowid.rowid_relative_fno(rowid)rel_fno,
  3   dbms_rowid.rowid_block_number(rowid)blockno,
  4   dbms_rowid.rowid_row_number(rowid) rowno
  5   from t_xifenfei ;

ROWID                 REL_FNO    BLOCKNO      ROWNO
------------------ ---------- ---------- ----------
AAAEy3AAEAAAAGGAAA          4        390          0
AAAEy3AAEAAAAGGAAB          4        390          1
AAAEy3AAEAAAAGGAAC          4        390          2

SQL> alter system dump datafile 4 block 390;

System altered.

--dump block 
block_row_dump:
tab 0, row 0, @0x1f81
tl: 23 fb: --H-FL-- lb: 0x1  cc: 2
col  0: [ 2]  c1 02
col  1: [16]  77 77 77 2e 78 69 66 65 6e 66 65 69 2e 63 6f 6d
tab 0, row 1, @0x1f6a
tl: 23 fb: --H-FL-- lb: 0x1  cc: 2
col  0: [ 2]  c1 03
col  1: [16]  77 77 77 2e 78 69 66 65 6e 66 65 69 2e 63 6f 6d
tab 0, row 2, @0x1f53
tl: 23 fb: --H-FL-- lb: 0x1  cc: 2
col  0: [ 2]  c1 04
col  1: [16]  77 77 77 2e 78 69 66 65 6e 66 65 69 2e 63 6f 6d
end_of_block_dump

增加不含默认值列

SQL> alter table t_xifenfei add c_xff varchar2(100);

Table altered.

SQL> desc t_xifenfei
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER
 NAME                                               VARCHAR2(20)
 C_XFF                                              VARCHAR2(100)
--证明增加列无默认值

SQL> insert into t_xifenfei values(4,'www.xifenfei.com','www.orasos.com');

1 row created.

SQL> commit;

Commit complete.

第二次dump block

SQL> alter system checkpoint;

System altered.

SQL>  alter system dump datafile 4 block 390;

System altered.

--block dump 
block_row_dump:
tab 0, row 0, @0x1f81
tl: 23 fb: --H-FL-- lb: 0x1  cc: 2
col  0: [ 2]  c1 02
col  1: [16]  77 77 77 2e 78 69 66 65 6e 66 65 69 2e 63 6f 6d
tab 0, row 1, @0x1f6a
tl: 23 fb: --H-FL-- lb: 0x1  cc: 2
col  0: [ 2]  c1 03
col  1: [16]  77 77 77 2e 78 69 66 65 6e 66 65 69 2e 63 6f 6d
tab 0, row 2, @0x1f53
tl: 23 fb: --H-FL-- lb: 0x1  cc: 2
col  0: [ 2]  c1 04
col  1: [16]  77 77 77 2e 78 69 66 65 6e 66 65 69 2e 63 6f 6d
tab 0, row 3, @0x1f2d
tl: 38 fb: --H-FL-- lb: 0x2  cc: 3
col  0: [ 2]  c1 05
col  1: [16]  77 77 77 2e 78 69 66 65 6e 66 65 69 2e 63 6f 6d
col  2: [14]  77 77 77 2e 6f 72 61 73 6f 73 2e 63 6f 6d
end_of_block_dump

从这里可以明显的看出来,前面的三条记录只有2列,但是四条记录有3列,证明使用了11gR2的新特性,这里可以使用null的默认值,证明比以往版本新特性增强.

发表在 ORACLE 12C | 标签为 ORACLE 12C | 一条评论

ORACLE 12C Invisible Columns and Column Ordering

发表于 2012 年 12 月 16 日由惜分飞

在ORACLE 12C中有了Invisible Columns的概念,就是在表中真实的存在该列,但是通过设置Invisible导致该列不可显示.官方说明:

The property of whether a column is visible can be controlled by the user. 
Invisible columns are not seen unless specified explicitly in the SELECT list.
Any generic access of a table (such as a SELECT * FROM table or a DESCRIBE) 
  will not show invisible columns.

数据库版本

SQL> select * from v$version;

BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.0.2 - 64bit                         0
PL/SQL Release 12.1.0.0.2                                                                 0
CORE    12.1.0.0.2                                                                        0
TNS for Linux: Version 12.1.0.0.2                                                         0
NLSRTL Version 12.1.0.0.2                                                                 0

创建含INVISIBLE列表

SQL> CREATE TABLE t_xifenfei (a number, b number INVISIBLE, c number);

Table created.

SQL> desc t_xifenfei
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ---------------------------
 A                                                              NUMBER
 C                                                              NUMBER

SQL>  select column_name,HIDDEN_COLUMN,COLUMN_ID from user_TAB_COLs  where TABLE_NAME='T_XIFENFEI';

COLUMN_NAM HID  COLUMN_ID
---------- --- ----------
A          NO           1
B          YES
C          NO           2

通过观察可以发现INVISIBLE列在一般的查询中不显示,在USER_TAB_COLS的视图中显示:HIDDEN_COLUMN为YES而且COLUMN_ID为空

设置INVISIBLE列为VISIBLE

SQL> ALTER TABLE t_xifenfei MODIFY (b VISIBLE);

Table altered.

SQL> desc t_xifenfei
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 A                                                  NUMBER
 C                                                  NUMBER
 B                                                  NUMBER

SQL> select column_name,HIDDEN_COLUMN,COLUMN_ID from user_TAB_COLs  where TABLE_NAME='T_XIFENFEI';

COLUMN_NAM HID  COLUMN_ID
---------- --- ----------
A          NO           1
B          NO           3
C          NO           2

当设置为VISIBLE时HIDDEN_COLUMN为YES而且COLUMN_ID为递增值

修改列展示顺序(Column Ordering)

SQL> ALTER TABLE t_xifenfei MODIFY (a invisible);

Table altered.

SQL> ALTER TABLE t_xifenfei MODIFY (a visible);

Table altered.

SQL> desc t_xifenfei
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 C                                                  NUMBER
 B                                                  NUMBER
 A                                                  NUMBER

SQL> select column_name,HIDDEN_COLUMN,COLUMN_ID from user_TAB_COLs  where TABLE_NAME='T_XIFENFEI';

COLUMN_NAM HID  COLUMN_ID
---------- --- ----------
A          NO           3
B          NO           2
C          NO           1

通过INVISIBLE和VISIBLE相关操作,实现A列从头移尾,实现列的Column Ordering效果.
补充说明:在以前的blog中,提供了修改col$基表的方法(通过修改col$.col#改变列展示顺序)来实现列的顺序修改,相对于这种方法来说,修改数据字典的方法风险太大,需要非常谨慎,而且不被ORACLE SUPPORT

发表在 ORACLE 12C | 标签为 ORACLE 12C | 3 条评论

17813235971

QQ 咨询

ORACLE 12C可以通过expdp导出view数据

ORCLE 12C 增加列,无默认值

ORACLE 12C Invisible Columns and Column Ordering

文章分类

最近发表

ORACLE 12C可以通过expdp导出view数据

ORCLE 12C 增加列,无默认值

ORACLE 12C Invisible Columns and Column Ordering

标签云

文章分类

最近发表