Re: how to improve impdp

From: David Fitzjarrell <oratune_at_yahoo.com>
Date: Tue, 16 Jul 2013 14:24:25 -0700 (PDT)
Message-ID: <1374009865.43530.YahooMailNeo_at_web121601.mail.ne1.yahoo.com>



I am at a loss to explain why LOB segments remain after the associated table is dropped:


SQL> create table lobtest(
� 2��������� lob_seg clob,
� 3��������� recid�� number,
� 4��������� descr�� varchar2(40))
� 5� lob(lob_seg) store as basicfile (tablespace indx);


Table created.


SQL>
SQL> select * from user_objects;

OBJECT_NAME������������������������������������������������������������������������������������������������������������� SUBOBJECT_NAME

���������������������������� OBJECT_ID
-------------------------------------------------------------------------------------------------------------------------------- ----------- ------------------- ------------------------------

��������������� DATA_OBJECT_ID OBJECT_TYPE�������� CREATED�� LAST_DDL_ TIMESTAMP���������� STATUS� T G S������������� NAMESPACE EDITION_NAME
------------------------------ ------------------- --------- --------- ------------------- ------- - - - ------------------------------ ----
--------------------------

SYS_IL0000073113C00001$$
�������������������������������� 73115
������������������������ 73115 INDEX�������������� 16-JUL-13 16-JUL-13 2013-07-16:15:21:51 VALID�� N Y N��������������������� 4
SYS_LOB0000073113C00001$$
�������������������������������� 73114
������������������������ 73114 LOB���������������� 16-JUL-13 16-JUL-13 2013-07-16:15:21:51 VALID�� N Y N��������������������� 8
LOBTEST
�������������������������������� 73113
������������������������ 73113 TABLE�������������� 16-JUL-13 16-JUL-13 2013-07-16:15:21:51 VALID�� N N N��������������������� 1
SQL>
SQL> alter table lobtest drop column lob_seg;


Table altered.


SQL>
SQL> select * from user_objects;

OBJECT_NAME������������������������������������������������������������������������������������������������������������� SUBOBJECT_NAME

���������������������������� OBJECT_ID
-------------------------------------------------------------------------------------------------------------------------------- ----------- ------------------- ------------------------------

��������������� DATA_OBJECT_ID OBJECT_TYPE�������� CREATED�� LAST_DDL_ TIMESTAMP���������� STATUS� T G S������������� NAMESPACE EDITION_NAME
------------------------------ ------------------- --------- --------- ------------------- ------- - - - ------------------------------ ----
--------------------------

LOBTEST
�������������������������������� 73113
������������������������ 73113 TABLE�������������� 16-JUL-13 16-JUL-13 2013-07-16:15:21:52 VALID�� N N N��������������������� 1
SQL>
SQL> drop table lobtest purge;


Table dropped.


SQL>
SQL> select * from user_objects;


no rows selected


SQL>
SQL> create table lobtest(
� 2��������� lob_seg clob,
� 3��������� recid�� number,
� 4��������� descr�� varchar2(40))
� 5� lob(lob_seg) store as basicfile (tablespace indx);


Table created.


SQL>
SQL> select * from user_objects;

OBJECT_NAME������������������������������������������������������������������������������������������������������������� SUBOBJECT_NAME

���������������������������� OBJECT_ID
-------------------------------------------------------------------------------------------------------------------------------- ----------- ------------------- ------------------------------

��������������� DATA_OBJECT_ID OBJECT_TYPE�������� CREATED�� LAST_DDL_ TIMESTAMP���������� STATUS� T G S������������� NAMESPACE EDITION_NAME
------------------------------ ------------------- --------- --------- ------------------- ------- - - - ------------------------------ ----
--------------------------

SYS_LOB0000073116C00001$$
�������������������������������� 73117
������������������������ 73117 LOB���������������� 16-JUL-13 16-JUL-13 2013-07-16:15:21:52 VALID�� N Y N��������������������� 8
SYS_IL0000073116C00001$$
�������������������������������� 73118
������������������������ 73118 INDEX�������������� 16-JUL-13 16-JUL-13 2013-07-16:15:21:52 VALID�� N Y N��������������������� 4
LOBTEST
�������������������������������� 73116
������������������������ 73116 TABLE�������������� 16-JUL-13 16-JUL-13 2013-07-16:15:21:52 VALID�� N N N��������������������� 1
SQL>
SQL> drop table lobtest purge;


Table dropped.


SQL>
SQL> select * from user_objects;


no rows selected


SQL>
No matter how I do it in 11.2.0.3 once I drop the table containing the LOB the LOB segment is dropped.�

Which release of Oracle are you using?

David Fitzjarrell  



 From: "Hsieh, Joan" <Joan.Hsieh_at_tufts.edu> To: "Hsieh, Joan" <Joan.Hsieh_at_tufts.edu>; "Allen, Brandon" <Brandon.Allen_at_OneNeck.com>; "oracle-l_at_freelists.org" <oracle-l_at_freelists.org> Sent: Tuesday, July 16, 2013 2:39 PM
Subject: RE: how to improve impdp   

Hi Brandon,

I have managed to drop all the tables and all views, it is much faster than drop user cascade.� However, there are a lot of� SYS_LOB%$$ LOBSEGMENT left. I couldn't find any drop statement to drop all these LOB objects. Does anyone know how?

Thanks,

joan

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Hsieh, Joan Sent: Wednesday, July 10, 2013 1:14 PM
To: Allen, Brandon; oracle-l_at_freelists.org Subject: RE: how to improve impdp

Thanks, Allen.
I'll try it later. My database is 11.2.0.3.� Your information is very valuable. ORACLE SR had asked me to generate a trace file for them to review.

Thank you again.

Joan

From: Allen, Brandon [mailto:Brandon.Allen_at_OneNeck.com] Sent: Wednesday, July 10, 2013 12:48 PM
To: Hsieh, Joan; oracle-l_at_freelists.org Subject: RE: how to improve impdp

Hi Joan,

Regarding the slowness of your "drop user cascade" statement - you may be encountering bug 6915130 if you're on Oracle 10.2, or maybe a similar bug in different versions.� The work around is to drop all the user's objects first (use SQL* to create a script for doing this quickly) and then drop the user.

Regards,
Brandon

*Something like this should work:

set term off
spool drop_tables.sql
set pages 0
set lines 150
set head off
set feed off
select 'drop table ' || owner || '.' || table_name || ' cascade constraints;' from dba_tables where owner = '<USERNAME>' order by table_name; spool off set head on set feed on set pages 1000 set term on

From: "Hsieh, Joan" <Joan.Hsieh_at_tufts.edu<mailto:Joan.Hsieh_at_tufts.edu>>

Subject: RE: how to improve impdp

Date: Tue, 9 Jul 2013 18:31:50 +0000

Hi David,

The database is dataguard environment, so it has to be in archive mode.� In cutover time windows, I don't want to break the standby database and have to recreate it later.� Our cutover time is combined the AIX database move to LINUX and one peopletools upgrade.� So there are two projects going on in the same open window.� Peoplesoft tech team it is ok just import data only without the statistics and indexes since both can be taken care in the peoplesoft upgrade. I'm trying to do another test without statistics and indexes.

"Drop user sysadm cascase"� on the new LINUX server take almost the whole day to finish. That's another issue.

Joan


Privileged/Confidential Information may be contained in this message or attachments hereto. Please advise immediately if you or your employer do not consent to Internet email for messages of this kind. Opinions, conclusions and other information in this message that do not relate to the official business of this company shall be understood as neither given nor endorsed by it.

--
http://www.freelists.org/webpage/oracle-l


--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l
Received on Tue Jul 16 2013 - 23:24:25 CEST

Original text of this message