Home » RDBMS Server » Server Administration » deferred_segment_creation, default true (merged) (DB11.2, Linux )  () 1 Vote
deferred_segment_creation, default true (merged) [message #440027] Wed, 20 January 2010 12:50 Go to next message
John Watson
Messages: 8938
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 Go to previous messageGo to next message
Michel Cadot
Messages: 68665
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
John Watson
Messages: 8938
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 #440097 is a reply to message #440096] Thu, 21 January 2010 01:17 Go to previous messageGo to next message
Michel Cadot
Messages: 68665
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, a check on the tablespace should be made during creation.
Can you create a table in a non-existent tablespace?
Which user are you currently using for these tests?

Thank you to post them, they will be useful.

Regards
Michel
Re: deferred_segment_creation, default true (merged) [message #440391 is a reply to message #440097] Sat, 23 January 2010 08:31 Go to previous messageGo to next message
John Watson
Messages: 8938
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 Go to previous message
Michel Cadot
Messages: 68665
Registered: March 2007
Location: Nanterre, France, http://...
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
Previous Topic: Migration from DB2 to Oracle
Next Topic: ASM Package issue
Goto Forum:
  


Current Time: Mon Jul 01 08:23:51 CDT 2024