标签云
asm 恢复 asm恢复 bbed bootstrap$ dul In Memory kcbzib_kcrsds_1 kccpb_sanity_check_2 kfed MySQL恢复 ORA-00312 ORA-00607 ORA-00704 ORA-01110 ORA-01555 ORA-01578 ORA-08103 ORA-600 2662 ORA-600 2663 ORA-600 3020 ORA-600 4000 ORA-600 4137 ORA-600 4193 ORA-600 4194 ORA-600 16703 ORA-600 kcbzib_kcrsds_1 ORA-600 KCLCHKBLK_4 ORA-15042 ORA-15196 ORACLE 12C oracle dul ORACLE PATCH Oracle Recovery Tools oracle加密恢复 oracle勒索 oracle勒索恢复 oracle异常恢复 ORACLE恢复 Oracle 恢复 ORACLE数据库恢复 oracle 比特币 OSD-04016 YOUR FILES ARE ENCRYPTED 勒索恢复 比特币加密文章分类
- Others (2)
- 中间件 (2)
- WebLogic (2)
- 操作系统 (100)
- 数据库 (1,598)
- DB2 (22)
- MySQL (70)
- Oracle (1,463)
- Data Guard (49)
- EXADATA (7)
- GoldenGate (21)
- ORA-xxxxx (158)
- ORACLE 12C (72)
- ORACLE 18C (6)
- ORACLE 19C (13)
- ORACLE 21C (3)
- Oracle ASM (65)
- Oracle Bug (7)
- Oracle RAC (47)
- Oracle 安全 (6)
- Oracle 开发 (27)
- Oracle 监听 (27)
- Oracle备份恢复 (530)
- Oracle安装升级 (84)
- Oracle性能优化 (62)
- 专题索引 (5)
- 勒索恢复 (75)
- PostgreSQL (18)
- PostgreSQL恢复 (6)
- SQL Server (27)
- SQL Server恢复 (8)
- TimesTen (7)
- 达梦数据库 (2)
- 生活娱乐 (2)
- 至理名言 (11)
- 虚拟化 (2)
- VMware (2)
- 软件开发 (36)
- Asp.Net (9)
- JavaScript (12)
- PHP (2)
- 小工具 (19)
-
最近发表
- PostgreSQL解析wal日志之—walminer
- Oracle 19c/21c最新patch信息-202404
- PostgreSQL恢复系列:pg_filedump批量处理
- PostgreSQL部分主要字典信息
- PostgreSQL恢复系列:pg_filedump恢复字典构造
- PostgreSQL 16 源码安装
- ORA-00742 ORA-00312 恢复
- 数据库open成功后报ORA-00353 ORA-00354错误引起的一系列问题(本质ntfs文件系统异常)
- ORA-600 ktsiseginfo1故障
- ORA-00600: internal error code, arguments: [16703], [1403], [4] 原因
- 最近遇到几起ORA-600 16703故障(tab$被清空),请引起重视
- ORA-600 2662快速恢复之Patch scn工具
- TNS-12518: TNS:listener could not hand off client connection
- ora.storage无法启动报ORA-12514故障处理
- 断电引起文件scn异常数据库恢复
- ORA-16188: LOG_ARCHIVE_CONFIG settings inconsistent with previously started instance
- .[hudsonL@cock.li].mkp勒索加密数据库完美恢复
- 模拟带库实现rman远程备份
- 又一例:ORA-600 kclchkblk_4和2662故障
- Oracle误删除数据文件恢复
月归档:十二月 2012
ORCLE 12C 增加列,无默认值
对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 Invisible Columns and Column Ordering
在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 支持multiple partitions同时操作
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来说,带来了不少的方便,省去了很多重复行工作.