SQL> create table mytable1(id number);
Table created.
SQL> create table mytable2(id number);
Table created.
SQL> select segment_name,bytes from user_segments where segment_name like 'MYTABLE_';
no rows selected
SQL> insert into mytable1 values(1);
1 row created.
SQL> commit;
Commit complete.
SQL> select segment_name,bytes from user_segments where segment_name like 'MYTABLE_';
SEGMENT_NAME BYTES
---------- ------
MYTABLE1 65536
SQL> truncate table mytable1;
Table truncated.
SQL> select segment_name,bytes from user_segments where segment_name like 'MYTABLE_';
SEGMENT_NAME BYTES
---------- ------
MYTABLE1 65536
SQL> alter table mytable1 move;
Table altered.
SQL> select segment_name,bytes from user_segments where segment_name like 'MYTABLE_';
SEGMENT_NAME BYTES
---------- ------
MYTABLE1 65536
SQL> alter table mytable1 move segment creation deferred;
alter table mytable1 move segment creation deferred
*
ERROR at line 1:
ORA-14133: ALTER TABLE MOVE cannot be combined with other operations
SQL> alter table mytable1 segment creation deferred;
alter table mytable1 segment creation deferred
*
ERROR at line 1:
ORA-01735: invalid ALTER TABLE option
Ok, then let's try to export and import tables to see if it is easy :
[oracle@orion2:/oradata/DMOFSCM9/dpdump]$ expdp sysadm/sysadm dumpfile=mytable.dmp logfile=mytable.log directory=data_pump_dir tables=mytable1,mytable2
Export: Release 11.2.0.1.0 - Production on Tue Sep 8 21:57:36 2009
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSADM"."SYS_EXPORT_TABLE_01": sysadm/******** dumpfile=mytable.dmp logfile=mytable.log directory=data_pump_dir tables=mytable1,mytable2
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 0 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "SYSADM"."MYTABLE1" 0 KB 0 rows
. . exported "SYSADM"."MYTABLE2" 0 KB 0 rows
Master table "SYSADM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSADM.SYS_EXPORT_TABLE_01 is:
/oradata/DMOFSCM9/dpdump/mytable.dmp
Job "SYSADM"."SYS_EXPORT_TABLE_01" successfully completed at 21:57:46
Then standard import (without particular options) :
[oracle@orion2:/oradata/DMOFSCM9/dpdump]$ impdp sysadm/sysadm dumpfile=mytable.dmp logfile=mytable_imp.log directory=data_pump_dir sqlfile=mytable.sql
Import: Release 11.2.0.1.0 - Production on Tue Sep 8 21:58:04 2009
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSADM"."SYS_SQL_FILE_FULL_01" successfully loaded/unloaded
Starting "SYSADM"."SYS_SQL_FILE_FULL_01": sysadm/******** dumpfile=mytable.dmp logfile=mytable_imp.log directory=data_pump_dir sqlfile=mytable.sql
Processing object type TABLE_EXPORT/TABLE/TABLE
Job "SYSADM"."SYS_SQL_FILE_FULL_01" successfully completed at 21:58:06
[oracle@orion2:/oradata/DMOFSCM9/dpdump]$ more mytable.sql
-- CONNECT SYSADM
ALTER SESSION SET EVENTS '10150 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10904 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '25475 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10407 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10851 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER, LEVEL 192 ';
-- new object type path: TABLE_EXPORT/TABLE/TABLE
CREATE TABLE "SYSADM"."MYTABLE1"
( "ID" NUMBER
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "PSDEFAULT" ;
CREATE TABLE "SYSADM"."MYTABLE2"
( "ID" NUMBER
) SEGMENT CREATION DEFERRED
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
TABLESPACE "PSDEFAULT" ;
The SEGMENT CREATION IMMEDIATE is there for my table mytable1 (eventhough it is empty).
Let's try to remove storage of table during import :
[oracle@orion2:/oradata/DMOFSCM9/dpdump]$ impdp sysadm/sysadm dumpfile=mytable.dmp logfile=mytable_imp.log directory=data_pump_dir transform=storage:n sqlfile=mytable.sql
Import: Release 11.2.0.1.0 - Production on Tue Sep 8 21:58:17 2009
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSADM"."SYS_SQL_FILE_FULL_01" successfully loaded/unloaded
Starting "SYSADM"."SYS_SQL_FILE_FULL_01": sysadm/******** dumpfile=mytable.dmp logfile=mytable_imp.log directory=data_pump_dir transform=storage:n sqlfile=mytable.sql
Processing object type TABLE_EXPORT/TABLE/TABLE
Job "SYSADM"."SYS_SQL_FILE_FULL_01" successfully completed at 21:58:19
[oracle@orion2:/oradata/DMOFSCM9/dpdump]$ more mytable.sql
-- CONNECT SYSADM
ALTER SESSION SET EVENTS '10150 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10904 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '25475 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10407 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10851 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER, LEVEL 192 ';
-- new object type path: TABLE_EXPORT/TABLE/TABLE
CREATE TABLE "SYSADM"."MYTABLE1"
( "ID" NUMBER
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
TABLESPACE "PSDEFAULT" ;
CREATE TABLE "SYSADM"."MYTABLE2"
( "ID" NUMBER
) SEGMENT CREATION DEFERRED
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
TABLESPACE "PSDEFAULT" ;
No more luck.
Finally, let's remove the segment attribute :
[oracle@orion2:/oradata/DMOFSCM9/dpdump]$ impdp sysadm/sysadm dumpfile=mytable.dmp logfile=mytable_imp.log directory=data_pump_dir transform=segment_attributes:n sqlfile=mytable.sql
Import: Release 11.2.0.1.0 - Production on Tue Sep 8 21:58:34 2009
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSADM"."SYS_SQL_FILE_FULL_01" successfully loaded/unloaded
Starting "SYSADM"."SYS_SQL_FILE_FULL_01": sysadm/******** dumpfile=mytable.dmp logfile=mytable_imp.log directory=data_pump_dir transform=segment_attributes:n sqlfile=mytable.sql
Processing object type TABLE_EXPORT/TABLE/TABLE
Job "SYSADM"."SYS_SQL_FILE_FULL_01" successfully completed at 21:58:36
[oracle@orion2:/oradata/DMOFSCM9/dpdump]$ more mytable.sql
-- CONNECT SYSADM
ALTER SESSION SET EVENTS '10150 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10904 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '25475 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10407 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10851 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER, LEVEL 192 ';
-- new object type path: TABLE_EXPORT/TABLE/TABLE
CREATE TABLE "SYSADM"."MYTABLE1"
( "ID" NUMBER
) ;
CREATE TABLE "SYSADM"."MYTABLE2"
( "ID" NUMBER
) ;
[oracle@orion2:/oradata/DMOFSCM9/dpdump]$
Unfortunately, no more tablespace... that means every single imported table will be (re)created into the user's default tablespace, which is not good enough.
Looks like as soon as we have a segment for a table, it is rather difficult to "remove" it.
To be continued,
Nicolas.
10 comments:
Hmmmmmm, not very good news is it?
Looks like another half-brained implementation from the Oracle boys...
Nuno,
Finally, I got it :
ALTER TABLE MOVE path does not provide SEGMENT CREATION clause :
move_table_clause -> segment_attributes_clause -> physical_attributes_clause -> storage_clause
The right path to reach this clause is the following :
ALTER TABLE > column_clause > add_column_clause > column_properties > nested_table_col_properties > physical_properties > deferred_segment_creation > segment_attributes_clause
If you read it correctly, that won’t work for standard table and "common"
ALTER TABLE statement.
So, no luck on the way we would like to expect.
So, there is still the solution to export all the empty tables, drop all the empty tables, create a sqlfile, replace SEGMENT CREATION IMMEDIATE by SEGMENT CREATION DEFERRED and run the script.
Nicolas.
And yet the fact remains that this:
"When you issue an ALTER TABLE ... MOVE statement any storage properties you specify override the storage properties specified in the CREATE TABLE statement."
is clearly written in the section of the manual specifically dealing with deferred segment creation.
Which seems to indicate it should be possible to specify the deferred option.
Not having it available in ALTER forces us to go the exp/imp path as you state, which is indeed a very inconvenient way of achieving this.
It'd have been a lot easier to do it via ALTER as it can be done online, for empty tables.
Ah well: there is always 11gr3...
It is worse than that :
SQL> create table mytable (id number);
Table created.
SQL> select bytes from user_segments where segment_name='MYTABLE';
no rows selected
SQL> alter table mytable move;
Table altered.
SQL> select bytes from user_segments where segment_name='MYTABLE';
BYTES
----------
65536
So, ALTER TABLE MOVE ignore the deferrable clause and create the segment anyway.
Surprisingly, the DBMS_METADATA.GET_DDL for an empty and nosegment table return "SEGMENT CREATION IMMEDIATE"...
That's too bad.
Anyway, I have a SR regarding this to try to understand, and I'll keep you informed.
Nuno,
By the way, you wrote
"And yet the fact remains that this:
"When you issue an ALTER TABLE ... MOVE statement any storage properties you specify override the storage properties specified in the CREATE TABLE statement.""
Yes, but according to the test I did with import dump, deferrable clause is not a storage clause, but a segment_attribute...
Did you see the test ? When I removed the storage from import, the deferrable clause is still there, not anymore when segment_attribute is removed...
Nicolas.
Well, finally, it looks there is a bug #8816562 (non-public) for the move which create segment I explained earlier...
To be followed,
Have fun.
Nicolas.
Can't see that bug anywhere, not even listed or referenced. How did you find out about it?
Nuno,
It is a non-public bug reference.
So far, it could be one of three bugs :
Bug 8816562 Abstract: DBBETA: DEFERRED SEGMENT CREATION NOT FUNCTIONAL
Bug 7835663 Abstract: DEFERRED SEGMENT CREATION DOES NOT WORK WITHOUT EXPLICIT ALTER SESSION/SYSTEM CO
Bug 8347699 Abstract: ALTER TABLE DISABLES DEFERRED SEGMENT CREATION
Unfortunately, none of them are readable to public.
They have been given to me following my SR creation, but still not sure if one of them fix the issue we are talking about.
Nicolas.
Thanks heaps, man.
There's another problem with DSC: You can no longer export/import from EE with DCS enabled to SE, where this feature is not available.
Post a Comment