Home » RDBMS Server » Server Utilities » need help with partition export (8.1.7 windows server)
need help with partition export [message #464264] Wed, 07 July 2010 03:10 Go to next message
ultgnp
Messages: 106
Registered: May 2010
Location: Germany
Senior Member
Hi,

I have a table which is 4.5GB in size. I created a new partition table,with local indexes. I exported the original table with all indexes,triggers and procedures.

After creating the empty partitioned table,i imported the dump file. The data isn't loaded. My questions are:

1) Will the indexes be overwritten?
2) Will the data go automatically in allocated partitions?
3) Do i need to export only table data ignoring indexes,triggers etc?


Best Regards,

Report message to a moderator

Re: need help with partition export [message #464285 is a reply to message #464264] Wed, 07 July 2010 04:32 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
There is absolutely no proof for whatever you are telling here.
Always please post the session. Copy and Paste it. Explaining
what you did will not help us.

Report message to a moderator

Re: need help with partition export [message #464293 is a reply to message #464264] Wed, 07 July 2010 04:55 Go to previous messageGo to next message
ultgnp
Messages: 106
Registered: May 2010
Location: Germany
Senior Member
 
CREATE TABLE A_PT_ORDREG
(
  BUS_UNIT                       VARCHAR2(5)    NOT NULL,
  ORDER_NO                       VARCHAR2(15)   NOT NULL,
  PT_CODE                        VARCHAR2(15)   NOT NULL,
  REG_NO                         VARCHAR2(15)   NOT NULL,
  AS_APPLICATION                 VARCHAR2(5)    NOT NULL,
  SERVICE_TYPE_CODE              VARCHAR2(10)   NOT NULL,
  SERVICE_CODE                   VARCHAR2(10)   NOT NULL,
  ORD_DATE                       DATE,
  ORD_TIME                       DATE,
  ORD_END_DATE                   DATE,
  ORD_PRIORTY                    VARCHAR2(1),
  MD_CODE                        VARCHAR2(10)   NOT NULL,
  NURSE_OPER_CODE                VARCHAR2(10),
  WARD_CODE                      VARCHAR2(10),
  CLINIC_CODE                    VARCHAR2(10),
  BILL_FLAG                      VARCHAR2(1),
  ORD_FILM_SIZE                  VARCHAR2(20),
  ORD_STATUS                     VARCHAR2(1),
  ORD_BILL_AMOUNT                NUMBER(7,2),
  SUGERY_PROCEDURE               VARCHAR2(100),
  NO_OF_DAYS                     NUMBER(3),
  LAST_DATE_DONE                 DATE,
  FREQUENCY                      NUMBER(2),
  RECURRING_FLAG                 VARCHAR2(1),
  REGULAR_ORDER_FLAG             VARCHAR2(1),
  CRE_BY                         VARCHAR2(15),
  CRE_DATE                       DATE,
  DEPT_CODE                      VARCHAR2(10),
  HOSP_DEPT_CODE                 VARCHAR2(10),
  SEQ_NO                         NUMBER(10),
  ORD_BILL_DISCOUNT              NUMBER(12,2),
  CASH_FLAG                      VARCHAR2(1),
  ECG_AMB_FLAG                   VARCHAR2(1),
  ECG_BED_FLAG                   VARCHAR2(1),
  ECG_EMG_FLAG                   VARCHAR2(1),
  ECG_DIG_FLAG                   VARCHAR2(1),
  ECG_QUIN_FLAG                  VARCHAR2(1),
  ORD_UNIT_PRICE                 NUMBER(12,2),
  VAC_REPEATS_DONE               NUMBER(3),
  ORD_FINAL_COM_DATE             DATE,
  RAD_PREPERATION_FLAG           VARCHAR2(1),
  RAD_CONSENT_FLAG               VARCHAR2(1),
  RAD_COMPLETED_FLAG             VARCHAR2(1),
  ORD_END_TIME                   DATE,
  DOSAGE_CODE                    VARCHAR2(200),
  FREQUENCY_CODE                 VARCHAR2(200),
  UNIT_CODE                      VARCHAR2(10),
  QTY_ISSUED                     NUMBER(11,2),
  ITEM_REV                       VARCHAR2(5),
  SERVICETYPE_GROUP_CODE         VARCHAR2(10),
  ORD_BILL_DEDUCTIBLE_AMT        NUMBER(12,2),
  ORD_BILL_PAID_AMT              NUMBER(12,2),
  ORD_BILL_DISCOUNT_AMT          NUMBER(12,2),
  ORD_BILL_NET_AMT               NUMBER(12,2),
  FOLLOW_UP_FLAG                 VARCHAR2(1),
  ORD_BILL_DISCOUNT_PERCENT      NUMBER(5,2),
  PT_BILL_DISCOUNT_AMT           NUMBER(12,2),
  DISCOUNT_CODE                  VARCHAR2(10),
  RENT_DATE                      DATE,
  RETURN_DUE_DATE                DATE,
  RETURN_DATE                    DATE,
  PT_ISSUED_FLAG                 VARCHAR2(1),
  MIS_FLAG                       VARCHAR2(1),
  REMAKE_FLAG                    VARCHAR2(1),
  TOOTH_NO                       VARCHAR2(15),
  DNTL_PROCESS_FLAG              VARCHAR2(1),
  APRVL_XFR_FLAG                 VARCHAR2(1),
  PACKAGE_CODE                   VARCHAR2(10),
  INP_DOUBT_ORDR                 VARCHAR2(1),
  INP_TAKE_HOME_DRUG             VARCHAR2(1),
  INP_PRE_OPRATVE                VARCHAR2(1),
  SND_INPT_ORD_2_BRDG            VARCHAR2(1),
  PERSONAL_ORDER_YN              VARCHAR2(1),
  VOID_BY                        VARCHAR2(15),
  NEW_UNIT_CODE                  VARCHAR2(10),
  SERVICE_CHARGE_AMT             NUMBER(12,5),
  INPT_INS_FIRE_FLAG             VARCHAR2(1),
  SERVICE_PAID_YN                VARCHAR2(1),
  INPT_FINAL_RLEAS_FLAG          VARCHAR2(1),
  DENTAL_LAB_ORD_STATUS          VARCHAR2(1),
  DENTAL_LAB_END_DATE            DATE,
  DEDUC_REF_ROWID                VARCHAR2(25)
)
TABLESPACE ULTGNP
PARTITION BY RANGE (ORD_DATE) 
(  
  PARTITION ORDREG_2004 VALUES LESS THAN (TO_DATE(' 01-01-2004 00:00:00', 'MM-DD-YYYY HH24:MI:SS'))
    LOGGING
    TABLESPACE ULTGNP
               ),  
  PARTITION ORDREG_2006 VALUES LESS THAN (TO_DATE(' 01-01-2006 00:00:00', 'MM-DD-YYYY HH24:MI:SS'))
    LOGGING
    TABLESPACE ULTGNP
               ),  
  PARTITION ORDREG_2008 VALUES LESS THAN (TO_DATE(' 01-01-2008 00:00:00', 'MM-DD-YYYY HH24:MI:SS'))

    LOGGING
    TABLESPACE ULTGNP
              ),  
  PARTITION ORDREG_2011 VALUES LESS THAN (TO_DATE(' 01-01-2011 00:00:00', 'MM-DD-YYYY HH24:MI:SS'))

    LOGGING
    TABLESPACE ULTGNP
                  )
)
NOCACHE
NOPARALLEL;


When i create this table and check the partitions, the ord_date is displayed as below:
/forum/fa/7979/0/

Will this change of format in date be responsible that the data is not inserted? My Insert statements are as follows:

Insert into A_PT_ORDREG
   (BUS_UNIT, ORDER_NO, PT_CODE, REG_NO, AS_APPLICATION, SERVICE_TYPE_CODE, SERVICE_CODE, ORD_DATE, ORD_TIME, ORD_PRIORTY, MD_CODE, BILL_FLAG, ORD_STATUS, ORD_BILL_AMOUNT, REGULAR_ORDER_FLAG, CRE_BY, CRE_DATE, SEQ_NO, CASH_FLAG, SERVICETYPE_GROUP_CODE, PRE_EXIST_ILLNESS_FLAG, ORD_BILL_NET_AMT, MIS_FLAG)
 Values
   ('100', 'MIS-00-282768', '3619579', 'O-144719', 'OPT', '001', '91100', TO_DATE('08/01/2000 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('08/01/2000 16:38:28', 'MM/DD/YYYY HH24:MI:SS'), 'N', '00190', 'O', 'C', 70, 'Y', 'G00190', TO_DATE('08/01/2000 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 1, 'I', '01', 'N', 70, 'Y');


I have data till June 2010.
  • Attachment: 1.JPG
    (Size: 16.35KB, Downloaded 1805 times)

[Updated on: Wed, 07 July 2010 05:39]

Report message to a moderator

Re: need help with partition export [message #464301 is a reply to message #464293] Wed, 07 July 2010 06:11 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Where are your export/import logs?

Report message to a moderator

Re: need help with partition export [message #464307 is a reply to message #464301] Wed, 07 July 2010 06:26 Go to previous messageGo to next message
ultgnp
Messages: 106
Registered: May 2010
Location: Germany
Senior Member
EXPORT LOG:

 
Connected to: Oracle8 Enterprise Edition Release 8.0.6.0.0 - Production
PL/SQL Release 8.0.6.0.0 - Production
Export done in WE8ISO8859P1 character set and AR8MSWIN1256 NCHAR character set
Note: grants on tables/views/sequences/roles will not be exported
Note: indexes on tables will not be exported
Note: constraints on tables will not be exported

About to export specified tables via Conventional Path ...
Current user changed to ULTGNP
. . exporting table                    A_PT_ORDREG
...........................................................................
...........................................................................
...........................................................................
...........................................................................
...........................................................................
...........................................................................
...........................................................................
...........................................................................
...........................................................................
...........................................................................
...........................................................................
...........................................................................
...........................................................................
...........................................................................
...........................................................................
...........................................................................
...........................................................................
...........................................................................
...........................................................................
...........................................................................
...........................................................................
...........................................................................
...........................................................................
...........................................................................
...........................................................................
...........................................................................
...........................................................................
...........................................................................
..
                                                     10514915 rows exported
Export terminated successfully without warnings.



IMPORT LOG:
Connected to: Oracle8 Enterprise Edition Release 8.0.6.0.0 - Production
With the Partitioning option
PL/SQL Release 8.0.6.0.0 - Production

Export file created by EXPORT:V08.00.06 via conventional path
Import terminated successfully without warnings.

Report message to a moderator

Re: need help with partition export [message #464310 is a reply to message #464293] Wed, 07 July 2010 06:38 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>>My Insert statements are as follows:
I am trying to reproduce the case.
Your insert statement is not working. Column PRE_EXIST_ILLNESS_FLAG does not exist in table DDL.

Report message to a moderator

Re: need help with partition export [message #464312 is a reply to message #464310] Wed, 07 July 2010 06:41 Go to previous messageGo to next message
ultgnp
Messages: 106
Registered: May 2010
Location: Germany
Senior Member
i have deleted some columns from the code here since there are more than 60 columns. i can post if u want.

Report message to a moderator

Re: need help with partition export [message #464362 is a reply to message #464312] Wed, 07 July 2010 09:24 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>(8.1.7 windows server)
>Connected to: Oracle8 Enterprise Edition Release 8.0.6.0.0 - Production

so which is true & real?

Report message to a moderator

Re: need help with partition export [message #464469 is a reply to message #464362] Thu, 08 July 2010 02:44 Go to previous messageGo to next message
ultgnp
Messages: 106
Registered: May 2010
Location: Germany
Senior Member
we have two versions.8.0.6 and 8.1.7 for testing purpose. We are planning to upgrade our database. This test was done on 8.0.6

Report message to a moderator

Re: need help with partition export [message #464620 is a reply to message #464469] Thu, 08 July 2010 09:14 Go to previous message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
> I exported the original table with all indexes,triggers and procedures.
>Note: indexes on tables will not be exported
I won't waste more time trying to guess the ending to this mystery novel.

Report message to a moderator

Previous Topic: SQL*Loader PPT
Next Topic: SQL Loader - How To Implement
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Mon Dec 23 13:58:34 CST 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.