标签归档：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 条评论

ORACLE 12C 支持multiple partitions同时操作

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

ORACLE 12C在分区维护方面有了不少的增强,在12C的beta版本中已经支持多分区的add/truncate/drop/merge操作,大大的提高了分区维护的效率.
数据库版本

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

在FF PDB中创建xff用户

SQL> alter session set container=ff;

Session altered.

SQL> create user xff identified by xifenfei;

User created.

SQL> grant dba to xff;

Grant succeeded.

SQL> conn xff/xifenfei@ff
Connected.
SQL> show con_name;

CON_NAME
------------------------------
FF

创建分区表RANGE PARTITIONS

SQL> CREATE TABLE t_xifenfei
  2  (name varchar2(100),time_id DATE)
  3  partition by range(time_id)
  4  (partition xff_2006 values less than (TO_DATE('01-01-2007','dd-MM-yyyy')),
  5  partition xff_2007 values less than (TO_DATE('01-01-2008','dd-MM-yyyy')),
  6  partition xff_2008 values less than (TO_DATE('01-01-2009','dd-MM-yyyy')),
  7  partition xff_2009 values less than (TO_DATE('01-01-2010','dd-MM-yyyy')));

Table created.

SQL> SET LONG 30
SQL> select PARTITION_NAME,HIGH_VALUE FROM USER_TAB_PARTITIONS where table_name='T_XIFENFEI';

PARTITION_NAME                 HIGH_VALUE
------------------------------ ------------------------------
XFF_2006                       TO_DATE(' 2007-01-01 00:00:00'
XFF_2007                       TO_DATE(' 2008-01-01 00:00:00'
XFF_2008                       TO_DATE(' 2009-01-01 00:00:00'
XFF_2009                       TO_DATE(' 2010-01-01 00:00:00'

ADD 多个分区

SQL> ALTER TABLE t_xifenfei ADD 
  2    PARTITION XFF_2010 VALUES LESS THAN (TO_DATE('01-01-2011','dd-MM-yyyy')),
  3    PARTITION XFF_2011 VALUES LESS THAN (TO_DATE('01-01-2012','dd-MM-yyyy')),
  4    PARTITION XFF_2012 VALUES LESS THAN (TO_DATE('01-01-2013','dd-MM-yyyy'));

Table altered.

SQL> select PARTITION_NAME,HIGH_VALUE FROM USER_TAB_PARTITIONS where table_name='T_XIFENFEI';

PARTITION_NAME                 HIGH_VALUE
------------------------------ ------------------------------
XFF_2006                       TO_DATE(' 2007-01-01 00:00:00'
XFF_2007                       TO_DATE(' 2008-01-01 00:00:00'
XFF_2008                       TO_DATE(' 2009-01-01 00:00:00'
XFF_2009                       TO_DATE(' 2010-01-01 00:00:00'
XFF_2010                       TO_DATE(' 2011-01-01 00:00:00'
XFF_2011                       TO_DATE(' 2012-01-01 00:00:00'
XFF_2012                       TO_DATE(' 2013-01-01 00:00:00'

7 rows selected.

Split多个分区

SQL> ALTER TABLE t_xifenfei  split PARTITION  XFF_2012 INTO
  2    (PARTITION XFF_2012_03 VALUES LESS THAN (TO_DATE('01-03-2012','dd-MM-yyyy')),
  3    PARTITION XFF_2012_06 VALUES LESS THAN (TO_DATE('01-06-2012','dd-MM-yyyy')),
  4    PARTITION XFF_2012_09 VALUES LESS THAN (TO_DATE('01-09-2012','dd-MM-yyyy')),
  5    PARTITION XFF_2012);

Table altered.

SQL>  select PARTITION_NAME,HIGH_VALUE FROM USER_TAB_PARTITIONS where table_name='T_XIFENFEI';

PARTITION_NAME                 HIGH_VALUE
------------------------------ ------------------------------
XFF_2006                       TO_DATE(' 2007-01-01 00:00:00'
XFF_2007                       TO_DATE(' 2008-01-01 00:00:00'
XFF_2008                       TO_DATE(' 2009-01-01 00:00:00'
XFF_2009                       TO_DATE(' 2010-01-01 00:00:00'
XFF_2010                       TO_DATE(' 2011-01-01 00:00:00'
XFF_2011                       TO_DATE(' 2012-01-01 00:00:00'
XFF_2012                       TO_DATE(' 2013-01-01 00:00:00'
XFF_2012_03                    TO_DATE(' 2012-03-01 00:00:00'
XFF_2012_06                    TO_DATE(' 2012-06-01 00:00:00'
XFF_2012_09                    TO_DATE(' 2012-09-01 00:00:00'

10 rows selected.

插入分区数据

SQL>INSERT INTO t_xifenfei VALUES('www.xifenfei.com',SYSDATE-100);

1 row created.

SQL> INSERT INTO t_xifenfei VALUES('www.xifenfei.com',SYSDATE-200);

1 row created.

SQL> INSERT INTO t_xifenfei VALUES('www.xifenfei.com',SYSDATE-300);

1 row created.

SQL>  INSERT INTO t_xifenfei VALUES('www.xifenfei.com',SYSDATE-10);

1 row created.

SQL> commit;

Commit complete.

SQL> col name for a20
SQL> select * from t_xifenfei;

NAME                 TIME_ID
-------------------- ---------
www.xifenfei.com     17-FEB-12
www.xifenfei.com     27-MAY-12
www.xifenfei.com     04-SEP-12
www.xifenfei.com     03-DEC-12

SQL>select * from t_xifenfei PARTITION(XFF_2012_03);

NAME                 TIME_ID
-------------------- ---------
www.xifenfei.com     17-FEB-12

SQL> select * from t_xifenfei PARTITION(XFF_2012_06);

NAME                 TIME_ID
-------------------- ---------
www.xifenfei.com     27-MAY-12

SQL>  select * from t_xifenfei PARTITION(XFF_2012_09);

no rows selected

SQL>  select * from t_xifenfei PARTITION(XFF_2012);

NAME                 TIME_ID
-------------------- ---------
www.xifenfei.com     04-SEP-12
www.xifenfei.com     03-DEC-12

TRUNCATE 多个分区

SQL> Alter table t_xifenfei truncate partitions XFF_2012_03, XFF_2012_06, XFF_2012_09;

Table truncated.

--剩下两条记录存在于XFF_2012中
SQL> select * from t_xifenfei;

NAME                 TIME_ID
-------------------- ---------
www.xifenfei.com     04-SEP-12
www.xifenfei.com     03-DEC-12

SQL> SELECT SUBOBJECT_NAME,object_id,data_object_id from user_objects where SUBOBJECT_NAME like 'XFF_2012_0%';

SUBOBJECT_NAME                  OBJECT_ID DATA_OBJECT_ID
------------------------------ ---------- --------------
XFF_2012_09                         90603          90603    <---为什么没有变
XFF_2012_06                         90602          90606
XFF_2012_03                         90601          90605

SQL> select PARTITION_NAME,HIGH_VALUE,SEGMENT_CREATED FROM USER_TAB_PARTITIONS where table_name='T_XIFENFEI';

PARTITION_NAME                 HIGH_VALUE                     SEGM
------------------------------ ------------------------------ ----
XFF_2006                       TO_DATE(' 2007-01-01 00:00:00' NO
XFF_2007                       TO_DATE(' 2008-01-01 00:00:00' NO
XFF_2008                       TO_DATE(' 2009-01-01 00:00:00' NO
XFF_2009                       TO_DATE(' 2010-01-01 00:00:00' NO
XFF_2010                       TO_DATE(' 2011-01-01 00:00:00' NO
XFF_2011                       TO_DATE(' 2012-01-01 00:00:00' NO
XFF_2012                       TO_DATE(' 2013-01-01 00:00:00' YES
XFF_2012_03                    TO_DATE(' 2012-03-01 00:00:00' YES
XFF_2012_06                    TO_DATE(' 2012-06-01 00:00:00' YES
XFF_2012_09                    TO_DATE(' 2012-09-01 00:00:00' NO  
--XFF_2012_09因为块延迟创建,没有segment导致truncate对应的dataobj#不变

10 rows selected.

DROP 多个分区

SQL>  Alter table t_xifenfei DROP  partitions XFF_2012_03, XFF_2012_06, XFF_2012_09;

Table altered.

SQL> select PARTITION_NAME,HIGH_VALUE FROM USER_TAB_PARTITIONS where table_name='T_XIFENFEI';

PARTITION_NAME                 HIGH_VALUE
------------------------------ ------------------------------
XFF_2006                       TO_DATE(' 2007-01-01 00:00:00'
XFF_2007                       TO_DATE(' 2008-01-01 00:00:00'
XFF_2008                       TO_DATE(' 2009-01-01 00:00:00'
XFF_2009                       TO_DATE(' 2010-01-01 00:00:00'
XFF_2010                       TO_DATE(' 2011-01-01 00:00:00'
XFF_2011                       TO_DATE(' 2012-01-01 00:00:00'
XFF_2012                       TO_DATE(' 2013-01-01 00:00:00'

7 rows selected.

MERGE 多分区

SQL> Alter table t_xifenfei merge partitions XFF_2006, XFF_2007, XFF_2008 into partition XFF_OLD;

Table altered.

SQL> select PARTITION_NAME,HIGH_VALUE FROM USER_TAB_PARTITIONS where table_name='T_XIFENFEI';

PARTITION_NAME                 HIGH_VALUE
------------------------------ ------------------------------
XFF_2009                       TO_DATE(' 2010-01-01 00:00:00'
XFF_2010                       TO_DATE(' 2011-01-01 00:00:00'
XFF_2011                       TO_DATE(' 2012-01-01 00:00:00'
XFF_2012                       TO_DATE(' 2013-01-01 00:00:00'
XFF_OLD                        TO_DATE(' 2009-01-01 00:00:00'

本测试是基于Range partitions进行,其实在ORACLE 12C中对于分区表的维护做了比较大的增强,上面试验的多分区操作,也支持List partitions和subpartitions.ddl一次性操作多个分区,给分区经常做维护的DBA来说,带来了不少的方便,省去了很多重复行工作.

发表在 ORACLE 12C | 标签为 ORACLE 12C | 评论关闭

Enterprise Manager Database Express 12c 欣赏

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

12C里面不再提供完整的Enterprise Manager,而是提供了Enterprise Manager Database Express 12c,功能在以前的EM基础之上有了很大的折扣,这里贴几张图出来供大家欣赏

以前的版本中,Enterprise Manager是通过dbconsole来控制的,现在的版本直接在监听中注册端口来实现,默认访问为:http://xifenfei:5500/em

[oracle@xifenfei ~]$ lsnrctl status

LSNRCTL for Linux: Version 12.1.0.0.2        on 13-DEC-2012 15:37:35

Copyright (c) 1991, 2012, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=xifenfei)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.0.2       
Start Date                12-DEC-2012 22:31:55
Uptime                    0 days 17 hr. 5 min. 39 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/12.1/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/xifenfei/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=xifenfei)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=xifenfei)(PORT=5500))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "ff" has 1 instance(s).
  Instance "xff", status READY, has 1 handler(s) for this service...
Service "xifenfei" has 1 instance(s).
  Instance "xff", status READY, has 1 handler(s) for this service...
Service "lx1" has 1 instance(s).
  Instance "xff", status READY, has 1 handler(s) for this service...
Service "lx2" has 1 instance(s).
  Instance "xff", status READY, has 1 handler(s) for this service...
Service "xffXDB" has 1 instance(s).
  Instance "xff", status READY, has 1 handler(s) for this service...
Service "xifenfei" has 1 instance(s).
  Instance "xifenfei", status READY, has 1 handler(s) for this service...
Service "xifenfeiXDB" has 1 instance(s).
  Instance "xifenfei", status READY, has 1 handler(s) for this service...
The command completed successfully

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

17813235971

QQ 咨询

标签归档：ORACLE 12C

ORACLE 12C Invisible Columns and Column Ordering

ORACLE 12C 支持multiple partitions同时操作

Enterprise Manager Database Express 12c 欣赏

文章分类

最近发表

标签归档：ORACLE 12C

ORACLE 12C Invisible Columns and Column Ordering

ORACLE 12C 支持multiple partitions同时操作

Enterprise Manager Database Express 12c 欣赏

标签云

文章分类

最近发表