当一个表中包含unused columns时,那么要做exchange partition就会报错。
但是,如果知道表的原始结构,那么做一个和原始结构一样的表,并也将那4列设置为unused,那么可以做exchange partition:
SQL> conn lunar/lunar
Connected.
SQL> set time on
04:40:43 SQL> set timing on
04:40:45 SQL> create table fq_test
04:40:51 2 (
04:40:51 3 OBJECT_ID NUMBER,
04:40:51 4 OWNER VARCHAR2(30),
04:40:51 5 SUBOBJECT_NAME VARCHAR2(30),
04:40:51 6 OBJECT_NAME VARCHAR2(128),
04:40:51 7 STATUS VARCHAR2(7),
04:40:51 8 TIMESTAMP VARCHAR2(19),
04:40:51 9 CREATED DATE,
04:40:51 10 GENERATED VARCHAR2(1),
04:40:51 11 OBJECT_TYPE VARCHAR2(19)
04:40:51 12 )
04:40:51 13 partition by range (OBJECT_ID)
04:40:51 14 (
04:40:51 15 partition PART_1 values less than ('10000'),
04:40:51 16 partition PART_2 values less than ('20000'),
04:40:51 17 partition PART_3 values less than ('30000'),
04:40:51 18 partition PART_4 values less than ('40000'),
04:40:51 19 partition PART_5 values less than (MAXVALUE)
04:40:51 20 );
Table created.
Elapsed: 00:00:00.65
04:40:52 SQL>
04:41:23 SQL> insert into fq_test(object_id,owner,subobject_name,object_name,status,timestamp,created,object_type)
04:41:36 2 select object_id,owner,subobject_name,object_name,status,timestamp,created,object_type from dba_objects
04:41:36 3 nologging;
11449 rows created.
Elapsed: 00:00:00.87
04:41:38 SQL> commit;
Commit complete.
Elapsed: 00:00:00.01
04:41:44 SQL>
04:41:44 SQL> select count(*) from fq_test;
COUNT(*)
----------
11449
Elapsed: 00:00:00.06
04:42:51 SQL> select count(*) from fq_test partition(PART_1);
COUNT(*)
----------
9633
Elapsed: 00:00:00.07
04:42:57 SQL> select count(*) from fq_test partition(PART_2);
COUNT(*)
----------
1816
Elapsed: 00:00:00.01
04:43:04 SQL> select count(*) from fq_test partition(PART_3);
COUNT(*)
----------
0
Elapsed: 00:00:00.00
04:43:11 SQL> desc fq_test
Name Null? Type
----------------------------------------- -------- ----------------------------
OBJECT_ID NUMBER
OWNER VARCHAR2(30)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_NAME VARCHAR2(128)
STATUS VARCHAR2(7)
TIMESTAMP VARCHAR2(19)
CREATED DATE
GENERATED VARCHAR2(1)
OBJECT_TYPE VARCHAR2(19)
04:43:37 SQL> alter table fq_test set unused column created;
Table altered.
Elapsed: 00:00:00.88
04:44:30 SQL> alter table fq_test set unused column object_type;
Table altered.
Elapsed: 00:00:00.19
04:44:35 SQL> insert into fq_test select * from fq_test;
11449 rows created.
Elapsed: 00:00:00.16
04:44:56 SQL> commit;
Commit complete.
Elapsed: 00:00:00.01
04:44:58 SQL>
04:51:55 SQL> create table fq_test2
04:52:30 2 (
04:52:30 3 OBJECT_ID NUMBER,
04:52:30 4 OWNER VARCHAR2(30),
04:52:30 5 SUBOBJECT_NAME VARCHAR2(30),
04:52:30 6 OBJECT_NAME VARCHAR2(128),
04:52:30 7 STATUS VARCHAR2(7),
04:52:30 8 TIMESTAMP VARCHAR2(19),
04:52:30 9 CREATED DATE,
04:52:30 10 GENERATED VARCHAR2(1),
04:52:30 11 OBJECT_TYPE VARCHAR2(19)
04:52:30 12 )
04:52:30 13 partition by range (OBJECT_ID)
04:52:30 14 (
04:52:30 15 partition PART_1 values less than ('10000'),
04:52:30 16 partition PART_2 values less than ('20000'),
04:52:30 17 partition PART_3 values less than ('30000'),
04:52:30 18 partition PART_4 values less than ('40000'),
04:52:30 19 partition PART_5 values less than (MAXVALUE)
04:52:30 20 );
Table created.
Elapsed: 00:00:00.09
04:52:30 SQL> create table fq_test_part_2 as select * from FQ_TEST2 partition(PART_2);
create table fq_test_part_2 as select * from FQ_TEST2 partition(PART_2)
*
ERROR at line 1:
ORA-00955: name is already used by an existing object
Elapsed: 00:00:00.03
04:52:38 SQL> drop table fq_test_part_2;
Table dropped.
Elapsed: 00:00:00.05
04:52:48 SQL> create table fq_test_part_2 as select * from FQ_TEST2 partition(PART_2);
Table created.
Elapsed: 00:00:00.03
04:52:52 SQL> alter table fq_test_part_2 set unused column created;
alter table fq_test_part_2 set unused column object_type;
Table altered.
Elapsed: 00:00:00.04
04:53:05 SQL>
Table altered.
Elapsed: 00:00:00.02
04:53:06 SQL> alter table fq_test exchange partition PART_2 with table fq_test_part_2
04:53:30 2 ;
Table altered.
Elapsed: 00:00:00.14
04:53:32 SQL>
了解一下Set unused columns的官方解释:
SET UNUSED Clause
Specify SET
UNUSED
to mark one or more columns as unused. Specifying this clause does not actually remove the target columns from each row in the table.That is, it does not restore the disk space used by these columns. Therefore, the response time is faster than when you execute theDROP
clause.
You can view all tables with columns marked UNUSED
in the data dictionary viewsUSER_UNUSED_COL_TABS
,DBA_UNUSED_COL_TABS
, and ALL_UNUSED_COL_TABS
.
Unused columns are treated as if they were dropped, even though their column data remains in the table rows. After a column has been markedUNUSED
, you have no access to that column. ASELECT
*
query will not retrieve data from unused columns. In addition, the names and types of columns markedUNUSED
will not be displayed during aDESCRIBE
, and you can add to the table a new column with the same name as an unused column.
Note: Until you actually drop these columns, they continue to count toward the absolute limit of 1000 columns in a single table. However, as with all DDL statements, you cannot roll back the results of this clause. That is, you cannot issueSET USED counterpart to retrieve a column that you haveSET UNUSED . Please refer to CREATE TABLE for more information on the 1000-column limit. Also, if you mark a |
DROP Clause
Specify DROP
to remove the column descriptor and the data associated with the target column from each row in the table. If you explicitly drop a particular column, then all columns currently markedUNUSED
in the target table are dropped at the same time.
When the column data is dropped:
-
All indexes defined on any of the target columns are also dropped.
-
All constraints that reference a target column are removed.
-
If any statistics types are associated with the target columns, then Oracle Database disassociates the statistics from the column with the
FORCE
option and drops any statistics collected using the statistics type.
Note: If the target column is a parent key of a nontarget column, or if a check constraint references both the target and nontarget columns, then Oracle Database returns an error and does not drop the column unless you have specified theCASCADE CONSTRAINTS clause. If you have specified that clause, then the database removes all constraints that reference any of the target columns. |
See Also: DISASSOCIATE STATISTICS for more information on disassociating statistics types |
DROP UNUSED COLUMNS Clause
Specify DROP
UNUSED
COLUMNS
to remove from the table all columns currently marked as unused. Use this statement when you want to reclaim the extra disk space from unused columns in the table. If the table contains no unused columns, then the statement returns with no errors.
column Specify one or more columns to be set as unused or dropped. Use theCOLUMN
keyword only if you are specifying only one column. If you specify a column list, then it cannot contain duplicates.
CASCADE CONSTRAINTS Specify CASCADE
CONSTRAINTS
if you want to drop all foreign key constraints that refer to the primary and unique keys defined on the dropped columns as well as all multicolumn constraints defined on the dropped columns. If any constraint is referenced by columns from other tables or remaining columns in the target table, then you must specifyCASCADE
CONSTRAINTS
. Otherwise, the statement aborts and an error is returned.
INVALIDATE The INVALIDATE
keyword is optional. Oracle Database automatically invalidates all dependent objects, such as views, triggers, and stored program units. Object invalidation is a recursive process. Therefore, all directly dependent and indirectly dependent objects are invalidated. However, only local dependencies are invalidated, because the database manages remote dependencies differently from local dependencies.
An object invalidated by this statement is automatically revalidated when next referenced. You must then correct any errors that exist in that object before referencing it.
CHECKPOINT SpecifyCHECKPOINT
if you want Oracle Database to apply a checkpoint for theDROP
COLUMN
operation after processing integer
rows;integer
is optional and must be greater than zero. Ifinteger
is greater than the number of rows in the table, then the database applies a checkpoint after all the rows have been processed. If you do not specifyinteger
, then the database sets the default of 512.Checkpointing cuts down the amount of undo logs accumulated during theDROP
COLUMN
operation to avoid running out of undo space. However, if this statement is interrupted after a checkpoint has been applied, then the table remains in an unusable state. While the table is unusable, the only operations allowed on it areDROP
TABLE
, TRUNCATE
TABLE
, andALTER
TABLE
DROP
... COLUMNS
CONTINUE
(described in sections that follow).
You cannot use this clause with SET
UNUSED
, because that clause does not remove column data.
DROP COLUMNS CONTINUE Clause
Specify DROP
COLUMNS
CONTINUE
to continue the drop column operation from the point at which it was interrupted. Submitting this statement while the table is in an invalid state results in an error.
Restrictions on Dropping Columns Dropping columns is subject to the following restrictions:
-
Each of the parts of this clause can be specified only once in the statement and cannot be mixed with any other
ALTER
TABLE
clauses. For example, the following statements are not allowed:ALTER TABLE t1 DROP COLUMN f1 DROP (f2); ALTER TABLE t1 DROP COLUMN f1 SET UNUSED (f2); ALTER TABLE t1 DROP (f1) ADD (f2 NUMBER); ALTER TABLE t1 SET UNUSED (f3) ADD (CONSTRAINT ck1 CHECK (f2 > 0));
-
You can drop an object type column only as an entity. To drop an attribute from an object type column, use the
ALTER
TYPE
...DROP
ATTRIBUTE
statement with theCASCADE
INCLUDING
TABLE
DATA
clause. Be aware that dropping an attribute affects all dependent objects. SeeDROP ATTRIBUTE for more information. -
You cannot specify the
DROP
clause to drop a column from a compressed table or a partitioned table containing any compressed partition, unless you first disable data compression for the table or partition. You can only use theSET
UNUSED
clause -
You can drop a column from an index-organized table only if it is not a primary key column. The primary key constraint of an index-organized table can never be dropped, so you cannot drop a primary key column even if you have specified
CASCADE
CONSTRAINTS
. -
You can export tables with dropped or unused columns. However, you can import a table only if all the columns specified in the export files are present in the table (that is, none of those columns has been dropped or marked unused). Otherwise, Oracle Database returns an error.
-
You cannot drop a column on which a domain index has been built.
-
You cannot drop a
SCOPE
table constraint or aWITH
ROWID
constraint on aREF
column. -
You cannot use this clause to drop:
-
A pseudocolumn, cluster column, or partitioning column. You can drop nonpartitioning columns from a partitioned table if all the tablespaces where the partitions were created are online and in read/write mode.
-
A column from a nested table, an object table, or a table owned by
SYS
.
-