deferred_segment_creation, default true (merged) [message #440027] |
Wed, 20 January 2010 12:50 |
John Watson
Messages: 8960 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
What do you think of this 11.2 parameter? It can give some pretty weird results.
SQL> create tablespace ts1 datafile size 10m;
Tablespace created.
SQL> create table t1 (c1 date) tablespace ts1;
Table created.
SQL> drop tablespace ts1;
Tablespace dropped.
SQL> insert into t1 values(sysdate);
insert into t1 values(sysdate)
*
ERROR at line 1:
ORA-00959: tablespace 'TS1' does not exist
SQL> drop table t1;
drop table t1
*
ERROR at line 1:
ORA-00959: tablespace 'TS1' does not exist
SQL> create tablespace ts1 datafile size 10m;
Tablespace created.
SQL> drop table t1;
Table dropped.
(please forgive me if this isn't an appropriate place to raise a discussion point - this is my first topic creation)
|
|
|
Re: deferred_segment_creation, default true (merged) [message #440030 is a reply to message #440027] |
Wed, 20 January 2010 12:58 |
|
Michel Cadot
Messages: 68718 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
I think either the third statement (no error on drop tablespace) or the fifth one (error on drop table) is a bug.
I have not 11.2, but do you also get the error if you omit the insert statement and drop the table just after dropping the tablespace?
I think it is weird that this parameter is set to true by default.
Regards
Michel
[Updated on: Wed, 20 January 2010 12:58] Report message to a moderator
|
|
|
Re: deferred_segment_creation, default true (merged) [message #440096 is a reply to message #440030] |
Thu, 21 January 2010 01:12 |
John Watson
Messages: 8960 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
Thank you for replying, Michel - the DML is not relevant. Bugs I can live with, if they are known. I don't think I'll bother to report it to Larry. It doesn't work for sys or system (which is documented), but it keeps getting wierder:
SQL> create undo tablespace undo2 datafile size 20m;
Tablespace created.
SQL> create table t2(c1 date) tablespace undo2;
Table created.
SQL> insert into t2 values(sysdate);
insert into t2 values(sysdate)
*
ERROR at line 1:
ORA-30022: Cannot create segments in undo tablespace
SQL> alter table t2 move tablespace ts1;
Table altered.
[Updated on: Thu, 21 January 2010 01:16] Report message to a moderator
|
|
|
|
Re: deferred_segment_creation, default true (merged) [message #440391 is a reply to message #440097] |
Sat, 23 January 2010 08:31 |
John Watson
Messages: 8960 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
Just to wrap this up (at least, for me): any user with the create table privilege can control whether to defer the segment creation, either by using the SEGMENT CREATION IMMEDIATE|DEFERRED clause, or by setting the deferred_segment_creation instance and session parameter. It doesn't work for SYS, SYSTEM, or OUTLN - they get an error if they try to defer segment creation. There appear to be a couple of bugs with the checking of tablespace type and existance, but nothing serious. The contents of DBA_FREE_SPACE and the behaviour of the space usage alert does appear to be consistent with the space actually used.
The situation with dropping objects is made a bit more complicated when you remember (as I finally did!) that by default a DROP doesn't PURGE.
Overall, I would say this is a useful facility. Think of SAP databases, where you can have thousands of never-used segments.
Test it!
|
|
|
Re: deferred_segment_creation, default true (merged) [message #440394 is a reply to message #440391] |
Sat, 23 January 2010 10:39 |
|
Michel Cadot
Messages: 68718 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
Quote:The situation with dropping objects is made a bit more complicated when you remember (as I finally did!) that by default a DROP doesn't PURGE.
Good remark!
I hope I will be able to make my own tests soon.
For instance, if we activate flashback data archive on this table, does the flashback table segments also deferred?
...
Regards
Michel
|
|
|