Re: Newbie: Import problem

From: Prasad Chavali <pchavali_at_assettech.com>
Date: 1998/07/03
Message-ID: <6nj78p$heu_at_nntp02.primenet.com>


The tablespace is running out of extents. Try the following to see how much space you have left in the tablespace

selcet tablespace_name, sum(bytes/1024) from dba_free_space group by tablespace_name;

After checking how much free space is left, add more space if needed.

If there is enough space, you may to change the extent sizes on the Tablespace and/or table also.

HTH Prasad Chavali
pchavali_at_assettech.com
Wayne Larimore wrote in message <01bda699$7b31a4a0$2a27a8c0_at_computer>...
>Greetings,
>
>I am new to the DBA world and have been given the task of doing Y2K testing
>on a test platform. I am trying to import a schema's data objects onto the
>test platform. I am running HP-UX 10.10 and Oracle 7.3.2.3. I have one
>large table (Payment) that is not importing without choking due to
>insufficient allocated space. Below, I have posted the tasks I performed
>and the results. Please, any guidance will be much appreciated.
>
>hprms:/oracle1/exp>exp lb/password file=/oracle1/exp/lbdump.dmp
>tables=auth_user,
>avail_rpt, avail_rpt_detail, bed, charges, ctl_header, header,
>lbx_services, output,
>payment, profile, report_ids compress=y grants=y
>log=/oracle1/exp/lbdump.log
>
>Export: Release 7.3.2.2.0 - Production on Fri Jul 3 08:58:16 1998
>
>Copyright (c) Oracle Corporation 1979, 1994. All rights reserved.
>
>
>Connected to: Oracle7 Server Release 7.3.2.3.0 - Production Release
>PL/SQL Release 2.3.2.3.0 - Production
>Export done in US7ASCII character set
>
>About to export specified tables via Conventional Path ...
> . exporting table AUTH_USER 36 rows exported
> . exporting table AVAIL_RPT 111 rows exported
> . exporting table AVAIL_RPT_DETAIL 276 rows exported
> . exporting table BED 1563 rows exported
> . exporting table CHARGES 12769 rows exported
> . exporting table CTL_HEADER 3777 rows exported
 

> . exporting table HEADER 6575 rows exported
> . exporting table LBX_SERVICES 31 rows exported
> . exporting table OUTPUT 0 rows exported
> . exporting table PAYMENT 178382 rows exported
> . exporting table PROFILE 494 rows exported
> . exporting table REPORT_IDS 967 rows exported
>Export terminated successfully without warnings.
>
>lbxtest:/oracle/exp>imp lb/password file=/oracle/exp/lbdump.dmp ignore=y
>tables=*
> commit=y
>
>Import: Release 7.3.2.2.0 - Production on Fri Jul 3 09:13:24 1998
>
>Copyright (c) Oracle Corporation 1979, 1994. All rights reserved.
>
>
>Connected to: Oracle7 Server Release 7.3.2.3.0 - Production Release
>PL/SQL Release 2.3.2.3.0 - Production
>
>Export file created by EXPORT:V07.03.02 via conventional path
>. importing LB's objects into LB
> . importing table "AUTH_USER" 36 rows imported
> . importing table "AVAIL_RPT" 111 rows imported
> . importing table "AVAIL_RPT_DETAIL" 276 rows imported
> . importing table "BED" 1563 rows imported
> . importing table "CHARGES" 12769 rows imported
> . importing table "CTL_HEADER" 3777 rows imported
> . importing table "HEADER" 6575 rows imported
> . importing table "LBX_SERVICES" 31 rows imported
> . importing table "OUTPUT" 0 rows imported
>. . importing table "PAYMENT"
>IMP-00003: ORACLE error 1653 encountered
>ORA-01653: unable to extend table LB.PAYMENT by 1588 in tablespace LOCKBOX
>IMP-00018: partial import of previous table completed: 50635 rows imported
>
>IMP-00017: following statement failed with ORACLE error 1658:
> "ALTER TABLE "PAYMENT" ADD CONSTRAINT "PK_PAYMENT" PRIMARY KEY
>("SPMT_LBX_N"
>

 "BR","SPMT_OPERATOR_ID","SPMT_BATCH_SEQUENCE","SPMT_BATCH_DATE","SPMT_ROW_N
>B"
> "R") USING INDEX STORAGE (INITIAL 20971520 NEXT 532480 MINEXTENTS 1
>MAXEXTEN"
> "TS 505 PCTINCREASE 1 FREELISTS 1 ) TABLESPACE "LOCKBOX""
>IMP-00003: ORACLE error 1658 encountered
>ORA-01658: unable to create INITIAL extent for segment in tablespace
>LOCKBOX
> . importing table "PROFILE" 494 rows imported
> . importing table "REPORT_IDS" 967 rows imported
>Import terminated successfully with warnings.
>
>SQL> descr payment
> Name Null? Type
> ------------------------------- -------- ----
> SPMT_LBX_NBR NOT NULL CHAR(10)
> SPMT_BATCH_SEQUENCE NOT NULL NUMBER(3)
> SPMT_OPERATOR_ID NOT NULL CHAR(3)
> SPMT_BATCH_DATE NOT NULL DATE
> SPMT_ROW_NBR NOT NULL NUMBER(4)
> SPMT_STATUS CHAR(1)
> SPMT_C1 CHAR(40)
> SPMT_C2 CHAR(40)
> SPMT_C3 CHAR(40)
> SPMT_C4 CHAR(40)
> SPMT_C5 CHAR(40)
> SPMT_C6 CHAR(40)
> SPMT_C7 CHAR(40)
> SPMT_C8 CHAR(40)
> SPMT_C9 CHAR(40)
> SPMT_C10 CHAR(40)
> SPMT_N1 NUMBER(17,2)
> SPMT_N2 NUMBER(11,2)
> SPMT_N3 NUMBER(17,2)
> SPMT_N4 NUMBER(17,2)
> SPMT_N5 NUMBER(11,2)
> SPMT_N6 NUMBER(11,2)
> SPMT_N7 NUMBER(11,2)
> SPMT_N8 NUMBER(11,2)
> SPMT_N9 NUMBER(11,2)
> SPMT_N10 NUMBER(11,2)
>
>
>Fri Jul 3
> page 1
> Tablespace Info from
>dba_extents on TEST Platform
>
>Owner Tablespace Name Extent Id Bytes Blocks
>--------------- --------------- --------------- ---------------
>---------------
>LB LOCKBOX 0 1064960 520
>LB LOCKBOX 0 1064960 520
>LB LOCKBOX 0 10649600 5200
>LB LOCKBOX 0 1064960 520
>LB LOCKBOX 0 3194880 1560
>LB LOCKBOX 0 1064960 520
>LB LOCKBOX 0 2703360 1320
>LB LOCKBOX 0 61440 30
>LB LOCKBOX 0 51200 25
>LB LOCKBOX 0 1064960 520
>LB LOCKBOX 0 20480 10
>LB LOCKBOX 0 4669440 2280
>LB LOCKBOX 0 81920 40
>LB LOCKBOX 0 20480 10
>LB LOCKBOX 0 471040 230
>LB LOCKBOX 0 25006080 12210
>LB LOCKBOX 1 3153920 1540
>LB LOCKBOX 1 81920 40
>LB LOCKBOX 2 3194880 1560
>LB LOCKBOX 2 92160 45
>LB LOCKBOX 3 3225600 1575
>LB LOCKBOX 3 92160 45
>LB LOCKBOX 4 92160 45
>LB LOCKBOX 5 92160 45
>LB LOCKBOX 6 92160 45
>LB LOCKBOX 7 102400 50
>LB LOCKBOX 8 102400 50
>LB LOCKBOX 9 102400 50
>LB LOCKBOX 10 102400 50
>LB LOCKBOX 11 102400 50
>LB USERS 0 20480 10
>LB USERS 0 20480 10
>LB USERS 0 20480 10
>LB USERS 0 81920 40
>LB USERS 0 40960 20
>LB USERS 0 40960 20
>LB USERS 0 20480 10
>LB USERS 0 122880 60
>
>38 rows selected.
>
>Fri Jul 3
> page 1
> Tablespace Info - from dba_extents
>on PRODuction Platform
>Owner Tablespace Name Extent id Bytes Blocks
>--------------- --------------- --------------- ---------------
>---------------
>LB LOCKBOX 0 1064960 130
>LB LOCKBOX 0 16384 2
>LB LOCKBOX 0 32768 4
>LB LOCKBOX 0 1064960 130
>LB LOCKBOX 0 3194880 390
>LB LOCKBOX 0 16384 2
>LB LOCKBOX 0 1064960 130
>LB LOCKBOX 0 125829120 15360
>LB LOCKBOX 0 1064960 130
>LB LOCKBOX 0 122880 15
>LB LOCKBOX 0 1064960 130
>LB LOCKBOX 0 10649600 1300
>LB LOCKBOX 0 16384 2
>LB LOCKBOX 0 16384 2
>LB LOCKBOX 0 32768 4
>LB LOCKBOX 0 81920 10
>LB LOCKBOX 0 4669440 570
>LB LOCKBOX 0 491520 60
>LB LOCKBOX 0 81920 10
>LB LOCKBOX 0 81920 10
>LB LOCKBOX 0 81920 10
>LB LOCKBOX 0 2703360 330
>LB LOCKBOX 0 16384 2
>LB LOCKBOX 0 16384 2
>LB LOCKBOX 0 20971520 2560
>LB LOCKBOX 1 81920 10
>LB LOCKBOX 2 122880 15
>LB LOCKBOX 3 122880 15
>LB LOCKBOX 4 122880 15
>LB LOCKBOX 5 122880 15
>LB LOCKBOX 6 122880 15
>LB LOCKBOX 7 163840 20
>LB LOCKBOX 8 163840 20
>LB LOCKBOX 9 163840 20
>LB LOCKBOX 10 163840 20
>LB LOCKBOX 11 163840 20
>LB LOCKBOX 12 204800 25
>LB LOCKBOX 13 204800 25
>LB LOCKBOX 14 204800 25
>LB LOCKBOX 15 204800 25
>LB LOCKBOX 16 204800 25
>LB LOCKBOX 17 245760 30
>LB LOCKBOX 18 245760 30
>LB LOCKBOX 19 245760 30
>LB LOCKBOX 20 245760 30
>LB LOCKBOX 21 245760 30
>
>46 rows selected.
>
Received on Fri Jul 03 1998 - 00:00:00 CEST

Original text of this message