This forum is currently locked. You can't register or post questions at this time. (read more)

Export table - first 10 million rows

All posts relating to Oracle database administration.

Moderator: Tim...

jnrpeardba
Advisor
Posts: 401
Joined: Wed May 04, 2011 3:14 pm

Re: Export table - first 10 million rows

Postby jnrpeardba » Thu Dec 19, 2013 11:55 am

Hi Tim,

Apologies for not getting back to you sooner, but here is the outcome of the datapump export I took last week.

Code: Select all

;;;
Export: Release 10.2.0.4.0 - 64bit Production on Tuesday, 10 December, 2013 10:56:59

Copyright (c) 2003, 2007, Oracle.  All rights reserved.
;;;
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_SCHEMA_03":  sys/******** AS SYSDBA schemas=KANA_RES_FR parallel=12 directory=export_dumpdir dumpfile=kana_res_fr.dmp logfile=kana_res_fr_10122013.log
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 252.7 GB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/VIEW/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/INDEX/FUNCTIONAL_AND_BITMAP/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_AND_BITMAP/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/INDEX/DOMAIN_INDEX/INDEX
. . exported "KANA_RES_FR"."KC_MESSAGEHISTORY"           767.3 MB 18520685 rows
ORA-31693: Table data object "KANA_RES_FR"."KC_RAWTEXT" failed to load/unload and is being skipped due to error:
ORA-02354: error in exporting/importing data
ORA-01555: snapshot too old: rollback segment number 31 with name "_SYSSMU31$" too small
. . exported "KANA_RES_FR"."KC_FOLDERHOURLOG"            402.7 MB 19051219 rows
. . exported "KANA_RES_FR"."KC_MESSAGE"                  272.3 MB 1662069 rows
. . exported "KANA_RES_FR"."KC_ATTACHMENT"               59.20 MB  556711 rows
. . exported "KANA_RES_FR"."KC_MESSAGERECIPIENT"         20.22 MB 1055387 rows
. . exported "KANA_RES_FR"."KC_EMAILADDRESS"             19.48 MB  244920 rows
. . exported "KANA_RES_FR"."KC_MESSAGECATEGORY"          17.81 MB 1072842 rows
. . exported "KANA_RES_FR"."KC_CASE"                     15.76 MB  994225 rows
. . exported "KANA_RES_FR"."KC_PREFERENCE"               16.61 MB  340349 rows
. . exported "KANA_RES_FR"."KC_CONSUMER"                 14.04 MB  244997 rows
. . exported "KANA_RES_FR"."KC_MESSAGEATTACHMENT"        12.24 MB  637567 rows
. . exported "KANA_RES_FR"."KC_SESSION"                  9.419 MB  169548 rows
. . exported "KANA_RES_FR"."KC_CONSUMERTEXT"             7.140 KB      79 rows
. . exported "KANA_RES_FR"."KC_TEMPLATE"                 4.625 MB     849 rows
. . exported "KANA_RES_FR"."KC_MESSAGETEMPLATE"          4.063 MB  304612 rows
. . exported "KANA_RES_FR"."KC_HOURLOG"                  2.916 MB   51462 rows
. . exported "KANA_RES_FR"."KC_DICTIONARY"               13.17 KB      31 rows
. . exported "KANA_RES_FR"."KC_ADDRESS"                  19.25 KB     147 rows
. . exported "KANA_RES_FR"."KC_CAMPAIGN"                 7.632 KB       1 rows
. . exported "KANA_RES_FR"."KC_CATEGORY"                 91.81 KB     754 rows
. . exported "KANA_RES_FR"."KC_CATEGORYTREE"             12.31 KB     620 rows
. . exported "KANA_RES_FR"."KC_COLLABORATION"               21 KB     550 rows
. . exported "KANA_RES_FR"."KC_CONSUMERCATEGORY"         55.78 KB    3626 rows
. . exported "KANA_RES_FR"."KC_CSFIELDS_TEMP"            4.976 KB       1 rows
. . exported "KANA_RES_FR"."KC_DEPARTMENT"               35.98 KB     185 rows
. . exported "KANA_RES_FR"."KC_DEPARTMENTCONSUMER"       6.742 KB      70 rows
. . exported "KANA_RES_FR"."KC_FOLDER"                   144.8 KB    1535 rows
. . exported "KANA_RES_FR"."KC_KANAFIELD"                10.41 KB      15 rows
. . exported "KANA_RES_FR"."KC_KANAUSER"                 148.3 KB    1195 rows
. . exported "KANA_RES_FR"."KC_KANAUSERDICTIONARY"       5.656 KB      31 rows
. . exported "KANA_RES_FR"."KC_KANAUSERFOLDER"           23.25 KB    1303 rows
. . exported "KANA_RES_FR"."KC_KANAUSER_EX"              20.00 KB    1193 rows
. . exported "KANA_RES_FR"."KC_MAILBOX"                  23.48 KB      38 rows
. . exported "KANA_RES_FR"."KC_OBJECTLOCK"               6.507 KB       7 rows
. . exported "KANA_RES_FR"."KC_OLDPASSWORDS"             29.76 KB     763 rows
. . exported "KANA_RES_FR"."KC_PASSWORDSETTINGS"         9.242 KB       1 rows
. . exported "KANA_RES_FR"."KC_PHONE"                    20.28 KB     310 rows
. . exported "KANA_RES_FR"."KC_PORTAL"                   9.898 KB       1 rows
. . exported "KANA_RES_FR"."KC_RAWTEXT_TEMP"             4.976 KB       1 rows
. . exported "KANA_RES_FR"."KC_RULE"                     42.44 KB     424 rows
. . exported "KANA_RES_FR"."KC_RULEACTION"               11.53 KB     438 rows
. . exported "KANA_RES_FR"."KC_RULECONDITION"            14.74 KB     458 rows
. . exported "KANA_RES_FR"."KC_RULEGROUP"                19.97 KB      97 rows
. . exported "KANA_RES_FR"."KC_RULEPARAMETER"            57.08 KB    1719 rows
. . exported "KANA_RES_FR"."KC_SERVICECLOCK"             10.27 KB       1 rows
. . exported "KANA_RES_FR"."KC_TEMPLATEATTACHMENT"       6.429 KB       2 rows
. . exported "KANA_RES_FR"."KC_TEMPLATERECIPIENT"        12.07 KB     580 rows
. . exported "KANA_RES_FR"."B181012_KC_FOLDER"           102.8 KB    1005 rows
. . exported "KANA_RES_FR"."B181012_KC_CATEGORY"         89.11 KB     731 rows
. . exported "KANA_RES_FR"."B181012_KC_KANAUSER"         96.91 KB     735 rows
. . exported "KANA_RES_FR"."KC_PARAMETER"                34.67 KB     251 rows
. . exported "KANA_RES_FR"."B181012_KC_DEPARTMENT"       34.46 KB     175 rows
. . exported "KANA_RES_FR"."B181012_KC_DEPARTMENTCATEGORY"  11.01 KB     525 rows
. . exported "KANA_RES_FR"."B181012_KC_DEPARTMENTCONSUMER"  6.554 KB      56 rows
. . exported "KANA_RES_FR"."B181012_KC_DEPARTMENTKANAUSER"  13.34 KB     723 rows
. . exported "KANA_RES_FR"."B181012_KC_DEPARTMENTMAILBOX"  5.656 KB      32 rows
. . exported "KANA_RES_FR"."B181012_KC_DEPARTMENTTREE"   12.41 KB     675 rows
. . exported "KANA_RES_FR"."B181012_KC_DEPARTMENTTYPE"   5.687 KB       2 rows
. . exported "KANA_RES_FR"."B181012_KC_PORTAL"           9.937 KB       1 rows
. . exported "KANA_RES_FR"."KC_ADDRESSTYPE"              5.687 KB       3 rows
. . exported "KANA_RES_FR"."KC_ATTACHMENTTYPE"           5.773 KB       6 rows
. . exported "KANA_RES_FR"."KC_CASEEVENT"                5.757 KB       6 rows
. . exported "KANA_RES_FR"."KC_CASESTATUS"               5.664 KB       2 rows
. . exported "KANA_RES_FR"."KC_CONNECTIONHANDLE"         8.140 KB       6 rows
. . exported "KANA_RES_FR"."KC_CONSUMEREVENT"            5.718 KB       4 rows
. . exported "KANA_RES_FR"."KC_CONSUMERTEXTTYPE"         5.656 KB       1 rows
. . exported "KANA_RES_FR"."KC_DEPARTMENTCATEGORY"       11.16 KB     540 rows
. . exported "KANA_RES_FR"."KC_DEPARTMENTKANAUSER"       18.78 KB    1193 rows
. . exported "KANA_RES_FR"."KC_DEPARTMENTMAILBOX"        5.656 KB      33 rows
. . exported "KANA_RES_FR"."KC_DEPARTMENTMAILBOX2"       5.656 KB      33 rows
. . exported "KANA_RES_FR"."KC_DEPARTMENTTREE"           12.90 KB     721 rows
. . exported "KANA_RES_FR"."KC_DEPARTMENTTYPE"           5.671 KB       2 rows
. . exported "KANA_RES_FR"."KC_EMAILADDRESSTYPE"         5.679 KB       2 rows
. . exported "KANA_RES_FR"."KC_ENCODING"                 6.437 KB      43 rows
. . exported "KANA_RES_FR"."KC_FOLDERTYPE"                5.75 KB       5 rows
. . exported "KANA_RES_FR"."KC_FORMATTYPE"               5.781 KB       9 rows
. . exported "KANA_RES_FR"."KC_GROUPADDRESSTYPE"         5.343 KB       2 rows
. . exported "KANA_RES_FR"."KC_LANGUAGENAME"             5.656 KB       1 rows
. . exported "KANA_RES_FR"."KC_MAILBOXGROUP"             6.710 KB       2 rows
. . exported "KANA_RES_FR"."KC_MESSAGECATEGORYTYPE"      5.773 KB       5 rows
. . exported "KANA_RES_FR"."KC_MESSAGEEVENT"               6.5 KB      37 rows
. . exported "KANA_RES_FR"."KC_MESSAGESTATE"             5.929 KB      13 rows
. . exported "KANA_RES_FR"."KC_MESSAGETYPE"              5.742 KB       5 rows
. . exported "KANA_RES_FR"."KC_NODEKABSTATUS"            5.992 KB       1 rows
. . exported "KANA_RES_FR"."KC_PERMISSION"               5.796 KB      24 rows
. . exported "KANA_RES_FR"."KC_PHONETYPE"                5.703 KB       5 rows
. . exported "KANA_RES_FR"."KC_PORTALTYPE"               5.710 KB       4 rows
. . exported "KANA_RES_FR"."KC_POSITIVEINTEGER"          5.656 KB     100 rows
. . exported "KANA_RES_FR"."KC_RAWTEXTTYPE"              5.726 KB       5 rows
. . exported "KANA_RES_FR"."KC_ROLE"                     8.179 KB      10 rows
. . exported "KANA_RES_FR"."KC_ROLEPERMISSION"           5.781 KB      47 rows
. . exported "KANA_RES_FR"."KC_ROUTINGREASON"            6.593 KB      10 rows
. . exported "KANA_RES_FR"."KC_ROUTINGTYPE"              5.757 KB       7 rows
. . exported "KANA_RES_FR"."KC_SESSIONTYPE"              5.906 KB      13 rows
. . exported "KANA_RES_FR"."KC_SYSTEMPARAMETER"          5.289 KB       2 rows
. . exported "KANA_RES_FR"."KC_TEMPLATETYPE"             5.812 KB       9 rows
. . exported "KANA_RES_FR"."KC_VALIDATIONTYPE"           5.742 KB       6 rows
. . exported "KANA_RES_FR"."B181012_KC_LINK"                 0 KB       0 rows
. . exported "KANA_RES_FR"."B181012_KC_SCHEDULEDREPORT"      0 KB       0 rows
. . exported "KANA_RES_FR"."KC_CAMPAIGNRECIPIENT"            0 KB       0 rows
. . exported "KANA_RES_FR"."KC_CASEHISTORY"                  0 KB       0 rows
. . exported "KANA_RES_FR"."KC_CASE_EX"                      0 KB       0 rows
. . exported "KANA_RES_FR"."KC_CATEGORYLINK"                 0 KB       0 rows
. . exported "KANA_RES_FR"."KC_CONSUMERHISTORY"              0 KB       0 rows
. . exported "KANA_RES_FR"."KC_CONSUMER_EX"                  0 KB       0 rows
. . exported "KANA_RES_FR"."KC_CONSUMER_EXTEXT"              0 KB       0 rows
. . exported "KANA_RES_FR"."KC_DBDIAGNOSIS"                  0 KB       0 rows
. . exported "KANA_RES_FR"."KC_EXPIREDMESSAGE"               0 KB       0 rows
. . exported "KANA_RES_FR"."KC_IMAILSERVER"                  0 KB       0 rows
. . exported "KANA_RES_FR"."KC_KANAUSERTEMPLATE"             0 KB       0 rows
. . exported "KANA_RES_FR"."KC_KANAUSER_EXTEXT"              0 KB       0 rows
. . exported "KANA_RES_FR"."KC_LINK"                         0 KB       0 rows
. . exported "KANA_RES_FR"."KC_MAILINGLIST"                  0 KB       0 rows
. . exported "KANA_RES_FR"."KC_MESSAGECHATDETAILS"           0 KB       0 rows
. . exported "KANA_RES_FR"."KC_MESSAGETODELETE"              0 KB       0 rows
. . exported "KANA_RES_FR"."KC_MESSAGE_EX"                   0 KB       0 rows
. . exported "KANA_RES_FR"."KC_MESSAGE_EXTEXT"               0 KB       0 rows
. . exported "KANA_RES_FR"."KC_MIMETYPE"                     0 KB       0 rows
. . exported "KANA_RES_FR"."KC_PORTALLINK"                   0 KB       0 rows
. . exported "KANA_RES_FR"."KC_SCHEDULEDREPORT"              0 KB       0 rows
. . exported "KANA_RES_FR"."KC_SCHEDULEDREPORTFIELD"         0 KB       0 rows
. . exported "KANA_RES_FR"."KC_SCHEDULEDREPORTRECIPIENT"      0 KB       0 rows
. . exported "KANA_RES_FR"."KC_WORKTABLE1"                   0 KB       0 rows
. . exported "KANA_RES_FR"."KC_WORKTABLE2"                   0 KB       0 rows
Master table "SYS"."SYS_EXPORT_SCHEMA_03" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_03 is:
  /dbatemp/kana_res_fr.dmp
Job "SYS"."SYS_EXPORT_SCHEMA_03" completed with 1 error(s) at 14:44:07


I simply ran a full schema export without specifying number of rows and as you can see within the logfile (attached) a particular table failed due to a number of oracle related errors.
This particular table makes up the crunch of the schema, estimated at 250GB and having had a quick internet search there are too many wavering factors it could be, without any positive
solutions.

The platform is Solaris with Oracle 10.2.0.4 and a common thread I am finding is that if the table contains a high number of LOBS and the UNDO retention is set to default (900)
there is simply not enough time to write the data out to the master table, hence the reason for the error. It could mean to increase UNDO Retention to a value, estimated for the length
of the export, modify the LOB and then re-export the schema.

What I believe happened was that the application was still in use and something was using this particular table, otherwise why is it using undo? My options would be either to increase the UNDO tablespace to as huge an amount I can get away with or for the users/application personnel to stop the application and put up with an outage. Compression on the export will not really speed things up, so I am rather limited to the either of the two options mentioned above.

Do you have any thoughts on this or have come across a similar issue

Many thanks,

Jnrpeardba

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Export table - first 10 million rows

Postby Tim... » Thu Dec 19, 2013 12:16 pm

Hi.

If you want to export this table, you need to keep the undo for longer. Increasing the timeout will only work if there is enough UNDO space to hold all the undo.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

jnrpeardba
Advisor
Posts: 401
Joined: Wed May 04, 2011 3:14 pm

Re: Export table - first 10 million rows

Postby jnrpeardba » Thu Dec 19, 2013 1:16 pm

HI,

so I take it that means increase the UNDO_RETENTION & the UNDO tablespace

Thanks

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Export table - first 10 million rows

Postby Tim... » Thu Dec 19, 2013 1:40 pm

Hi.

Yes. Oracle will try to honour the undo retention period, provided there is enough undo space. Changing one without consifering the other does not make sense, unless you are using Flashback Data Archive, in which case undo can be kept forever.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

jnrpeardba
Advisor
Posts: 401
Joined: Wed May 04, 2011 3:14 pm

Re: Export table - first 10 million rows

Postby jnrpeardba » Thu Dec 19, 2013 2:05 pm

Many thanks Tim,

May not get back to you until after the new year, as I need to raise a chnage, as this will be on a production box.

If I do not have any more issues until then (shutdown all instances, would be a good idea) then may I wish you a merry xmas
and a happy new year to you and your family

Jnrpeardba

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Export table - first 10 million rows

Postby Tim... » Thu Dec 19, 2013 4:05 pm

The same to you. :)

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

jnrpeardba
Advisor
Posts: 401
Joined: Wed May 04, 2011 3:14 pm

Re: Export table - first 10 million rows

Postby jnrpeardba » Thu Feb 13, 2014 4:56 pm

Hi Tim,

Apologies for only now getting back to you - but I am still experiencing the same issue

Code: Select all

Export: Release 10.2.0.4.0 - Production on Thu Feb 6 10:01:57 2014

Copyright (c) 1982, 2007, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in UTF8 character set and UTF8 NCHAR character set

About to export specified tables via Conventional Path ...
Current user changed to KANA_RES_UKPERS
. . exporting table                     KC_RAWTEXT
EXP-00056: ORACLE error 1555 encountered
ORA-01555: snapshot too old: rollback segment number 9 with name "_SYSSMU9$" too small
Export terminated successfully with warnings.


As you can see similar issue as before although I have increased undo_retention

Code: Select all

SQL> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     28800
undo_tablespace                      string      UNDOTBS1
SQL>


And the tablespace usage is - I would have throught that undo is large enough and has enough unused space

Code: Select all

13-FEB-14                                      TablespaceSpaceUtilizationStatusReport

                                       Total           Total           Total   Total   Total                   Total   Total
Tablespace      Can    Files         Current         Current         Current Current Current             TBS     Max     Max
(TBS)           Auto      In             TBS            Used            Free    Used    Free             Max    Used    Free
Name            Extend   TBS           Space           Space           Space     PCT     PCT            Size     PCT     PCT
--------------- ------ ----- --------------- --------------- --------------- ------- ------- --------------- ------- -------
SYSTEM          YES        1   4,194,304,000   2,429,157,376   1,765,146,624   57.92   42.08  34,359,721,984    5.14   94.86
USERS           YES        1      16,359,424      16,293,888          65,536   99.60     .40  34,359,721,984     .00  100.00
KANA_ADMIN      YES        1  18,924,765,184  18,901,434,368      23,330,816   99.88     .12  34,359,721,984     .07   99.93
SYSAUX          YES        1   7,873,822,720   7,734,493,184     139,329,536   98.23    1.77  34,359,721,984     .41   99.59
UNDOTBS1        YES        1   5,242,880,000     286,588,928   4,956,291,072    5.47   94.53  34,359,721,984   14.42   85.58
SQL>


Code: Select all

select * from dba_tables where owner = 'KANA_RES_UKPERS' and table_name = 'KC_RAWTEXT';

OWNER                          TABLE_NAME                     TABLESPACE_NAME                CLUSTER_NAME                   IOT_NAME
------------------------------ ------------------------------ ------------------------------ ------------------------------ --------------------------
KANA_RES_UKPERS                KC_RAWTEXT                     KANA_MESSAGE


could it be that the fact that the data type is a 'CLOB' which is causing this issue?

Code: Select all

SQL> desc KANA_RES_UKPERS.KC_RAWTEXT;
 Name                                                                                Null?    Type
 ----------------------------------------------------------------------------------- -------- --------------------------------------------------------
 RAWTEXTID                                                                           NOT NULL NUMBER(10)
 MESSAGEID                                                                           NOT NULL NUMBER(10)
 TYPE                                                                                NOT NULL NUMBER(5)
 COMPRESSLEVEL                                                                                NUMBER(10)
 VALUE                                                                                        CLOB


Seems as thought nothing is actually wrong with the table

Code: Select all

SQL> select NUM_ROWS,LAST_ANALYZED,STATUS from dba_tables where owner = 'KANA_RES_UKPERS' and table_name = 'KC_RAWTEXT';

  NUM_ROWS LAST_ANALYZED   STATUS
---------- --------------- --------
  23219290 10-FEB-14       VALID


This is a 10.2.0.4 64 Bit instance running on Solaris 10

Do you have any other suggestions of what I may try in order to resolve or remove this error and have a successful export?

Much appreciated Tim,

Jnrpeardba

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Export table - first 10 million rows

Postby Tim... » Fri Feb 14, 2014 8:39 am

Hi.

Well, if it were a really large table that I expected to move, I would probably:

1) Do a reorg, to put that table into it's own tablespace.
2) Use transportable tablespaces to move that tablespace between databases.

Using this method means you are actually just exporting the metadata, so it is quick no matter what the size of the tablespace.

At some point you have to take a step back and ask yourself, is what you are trying to do the right thing. I think it is blatantly obvious it is not in this case. :)

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

jnrpeardba
Advisor
Posts: 401
Joined: Wed May 04, 2011 3:14 pm

Re: Export table - first 10 million rows

Postby jnrpeardba » Fri Feb 14, 2014 12:57 pm

Hi Tim,

thanks for the feedback, just a couple of questions as I believe as you mentioned I only have the TTS option available to me in this scenario

If I am going from little Endian (Solaris / 10g) to big Endian (Linux / 11.2)

a) does the tablespace need to be placed offline and then put into read only mode
b) can I go from little endian to big endian without any issues, once I have performed the convert

Many thanks

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Export table - first 10 million rows

Postby Tim... » Fri Feb 14, 2014 2:12 pm

Hi.

Ah... Switching between endians and versions... has that been mentioned before? If so I forgot...

You will have to do some serious testing to make sure the TTS works with this. You will probably need to do a CONVERT also. Typically, TTS requires the tablespace to be read-only...

http://www.oracle-base.com/articles/10g ... conversion

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

jnrpeardba
Advisor
Posts: 401
Joined: Wed May 04, 2011 3:14 pm

Re: Export table - first 10 million rows

Postby jnrpeardba » Mon Mar 24, 2014 3:04 pm

Hi Tim,

Apologies for only just getting back to you - but I have success :D

As you know my 243GB export was failing constantly due to an ora-1555
Basically it was one schema with one table with a LOB

So this is what I did:

Code: Select all

select max(maxquerylen) from v$undostat;

MAX(MAXQUERYLEN)
----------------
           46395

SQL> select retention, pctversion from dba_lobs where owner='KANA_RES_UKPERS' and table_name = 'KC_RAWTEXT';

 RETENTION PCTVERSION
---------- ----------
     28800

alter system set "_first_spare_parameter" = 900000 scope=both;

System altered.

SQL> show parameter "_first_spare_parameter"

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
_first_spare_parameter               integer     900000

SQL> alter system set undo_retention = 900000 scope=BOTH;

System altered.

SQL> alter table KANA_RES_UKPERS.KC_RAWTEXT modify lob (VALUE)(pctversion 10);

Table altered.

SQL> alter table KANA_RES_UKPERS.KC_RAWTEXT modify lob (VALUE) (retention);

Table altered.

SQL> select retention, pctversion from dba_lobs where owner='KANA_RES_UKPERS' and table_name = 'KC_RAWTEXT';

 RETENTION PCTVERSION
---------- ----------
     900000

SQL> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     900000
undo_tablespace                      string      UNDOTBS1

I cannot explain why, if the querylength from undostat was lower than undo_retention why that wasn't sufficient
As you can see I altered the table a few times to get the retention value into the table. I then re-submitted my export and hey-ho presto
it worked. It was as though the export was timining out, due to the contraints of the undo_retention.

As an ammendment I have also tested TTS from Big Endian to Little Endian on a 120GB tablespace and this also worked successfully. I find TTS is a quicker option over exp/imp,
but more tricky if you are not familiar with the process.

Thanks again for your help, assistance and guidance in this post.

Jnrpeardba

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Export table - first 10 million rows

Postby Tim... » Mon Mar 24, 2014 4:25 pm

:)
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

kfoster
Member
Posts: 17
Joined: Wed Feb 05, 2014 10:04 pm

Re: Export table - first 10 million rows

Postby kfoster » Mon Mar 24, 2014 4:47 pm

Always easier said than done but... Sounds like it is time to upgrade the source database to 11g.

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Export table - first 10 million rows

Postby Tim... » Mon Mar 24, 2014 7:42 pm

Yes. Get everything up to 11.2, then start learning 12cR1 fast. It will soon be time to start the upgrades to 12cR2... :)

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

jnrpeardba
Advisor
Posts: 401
Joined: Wed May 04, 2011 3:14 pm

Re: Export table - first 10 million rows

Postby jnrpeardba » Tue Mar 25, 2014 10:41 am

Hi all thanks for comments,

Not always as straight forward in large organisations to simply upgrade as and when.
Customer's may have not tested the application with 11gR2 or they may be waiting until they
get the go ahead to move platforms: ie. Solaris to Linux and will be more than happy to pay for
extended support until such a time is appropriate.

Believe it or not, this however is the easy part, trying to get buisness approval to actually perform the
'Go-Live' migration which may take 10+ hrs to move 1TB of THEIR DATA which they initially
requested can give you a headache, where you actually welcome early retirement and look forward to
the day when you are eligible for free public transport travel. :lol:

Jnrpeardba


Return to “Oracle Database Administration”

Who is online

Users browsing this forum: No registered users and 0 guests