Shulou(Shulou.com)05/31 Report--

This article will explain in detail how to import a table of 1.8 billion 300G data files into the database. The editor thinks it is very practical, so I share it for you as a reference. I hope you can get something after reading this article.

1. Problem: need to import a 300G data file with 1.8 billion data

two。 The customer gives a data file that is exported using imp. Contains a table xx. The table is structured as follows:

Click (here) to collapse or open

CREATE TABLE "username". "xx"

"DTLCARDNO" CHAR (16) NOT NULL ENABLE

"DTLCITY" NUMBER (4. 0)

"DTLCDCNT" NUMBER (6. 0) NOT NULL ENABLE

"DTLTXNCODE" NUMBER (4. 0) NOT NULL ENABLE

"DTLINNTYPE" NUMBER (4. 0)

"DTLPOSID" VARCHAR2 (12)

DTLSAMID VARCHAR2 (16)

"DTLPOSSEQ" NUMBER (1010)

"DTLDATE" NUMBER (8. 0)

"DTLTIME" NUMBER (6. 0) NOT NULL ENABLE

"DTLSETTDATE" NUMBER (8. 0)

"DTLCENSEQ" NUMBER (1010)

"DTLAMT" NUMBER (9. 0) NOT NULL ENABLE

"DTLSLAMT" NUMBER (9. 0)

"DTLBEFBAL" NUMBER (9. 0) NOT NULL ENABLE

"DTLAFTBAL" NUMBER (9. 0)

"DTLSTATID" NUMBER (9. 0)

"DTLERRCODE" NUMBER (6. 0)

"DTLINNERR" NUMBER (6. 0)

"DTLRSVD" VARCHAR2 (10)

"DTLPKGID" NUMBER (1010)

"DTLUNITID" NUMBER (8. 0)

"DTLCRDTYPE" NUMBER (4. 0)

"DTLTAC" CHAR (8)

"PARTFLAG" NUMBER (3jin0) NOT NULL ENABLE

) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255LOGGING

STORAGE (

BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

TABLESPACE "CRDDTL01_TS"

PARTITION BY RANGE ("PARTFLAG")

(PARTITION "P_JY001" VALUES LESS THAN (1)

PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

STORAGE (INITIAL 797966336 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

TABLESPACE "CRDDTL01_TS" NOCOMPRESS

PARTITION "P_JY002" VALUES LESS THAN (2)

PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

STORAGE (INITIAL 751828992 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

TABLESPACE "CRDDTL01_TS" NOCOMPRESS

PARTITION "P_JY003" VALUES LESS THAN (3)

PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

STORAGE (INITIAL 829423616 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

TABLESPACE "CRDDTL01_TS" NOCOMPRESS

PARTITION "P_JY004" VALUES LESS THAN (4)

PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

STORAGE (INITIAL 886046720 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

TABLESPACE "CRDDTL01_TS" NOCOMPRESS

PARTITION "P_JY005" VALUES LESS THAN (5)

PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

STORAGE (INITIAL 901775360 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

TABLESPACE "CRDDTL01_TS" NOCOMPRESS

PARTITION "P_JY006" VALUES LESS THAN (6)

PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

STORAGE (INITIAL 826277888 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

TABLESPACE "CRDDTL01_TS" NOCOMPRESS

PARTITION "P_JY007" VALUES LESS THAN (7)

PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

STORAGE (INITIAL 803209216 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

TABLESPACE "CRDDTL01_TS" NOCOMPRESS

PARTITION "P_JY008" VALUES LESS THAN (8)

PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

STORAGE (INITIAL 961544192 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

TABLESPACE "CRDDTL01_TS" NOCOMPRESS

PARTITION "P_JY009" VALUES LESS THAN (9)

PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

STORAGE (INITIAL 995098624 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

TABLESPACE "CRDDTL01_TS" NOCOMPRESS

PARTITION "P_JY010" VALUES LESS THAN (10)

PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

STORAGE (INITIAL 972029952 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

TABLESPACE "CRDDTL01_TS" NOCOMPRESS

.

Similarly, there are 360 partitions

As you can see, it's a partition table.

Import statement

Click (here) to collapse or open

Echo time%

Imp system/manager@orcl file=E:\ yikatong\ tlcarddtltb.dmp fromuser=u1 touser=u1 ignore=y log=E:\ yikatong\ tlcarddtltb.dmp.log indexes=N RECORDLENGTH=65535 buffer=502400000 commit=n feedback=10000000

Echo time%

Require 10 million rows to respond once, submit as N, and do not insert the index. Buffer is set to 500m

3. Start importing. Because the customer does not provide the size of the data file, only the tablespaces that need to be created are provided. I have created the following self-increasing tablespaces.

Create tablespace crddtl01_ts datafile'DUR autoextend on next oracle size size 8024m oracle 1024m

Increase by 1024m each time. Considering that the data is relatively large, each self-increase is too small, and the time spent is small. And then began to import. After waiting for several hours, an error was reported:

01659, 00000, "unable to allocate MINEXTENTS beyond s in tablespace s"

/ / * Cause: Failed to find sufficient contiguous space to allocate MINEXTENTS

/ / for the segment being created.

/ / * Action: Use ALTER TABLESPACE ADD DATAFILE to add additional space to the

/ / tablespace or retry with smaller value for MINEXTENTS, NEXT or

/ / PCTINCREASE

. It was a little confusing at first. I build tablespaces are self-growing. Can't it grow by itself. It can grow under the inquiry. Look at the tablespace size of 32G. It turns out that the maximum size of oracle ordinary tablespace files is 32G. Then start adding four files to each tablespace. Sql is as follows:

Click (here) to collapse or open

Create tablespace crddtl01_ts datafile'DUR autoextend on next oracle size size 8024m oracle 1024m

Create tablespace crddtlidx01_ts datafile'd autoextend on next size 5024m oracle 1024m

Alter tablespace crddtl01_ts add datafile'DUR size oracle autoextend on next 1024m

Alter tablespace crddtl01_ts add datafile'DUR size oracle autoextend on next 1024m crddtl01bprinter. DBF'

Create tablespace crddtl02_ts datafile'Elux size oracleSecretdataCompact crddtl02roomts.dbf'or24m autoextend on next 1024m autoallocate

Alter tablespace crddtl02_ts add datafile'Elux size oracleSecretdataCompact crddtl02aroomts.dbf'oracledata1024m autoextend on next 1024m

Alter tablespace crddtl02_ts add datafile'Elux size oracleSecretdataCompact crddtl02broomts.dbf'ort1024m autoextend on next 1024m

Create tablespace crddtlidx02_ts datafile'Elux size size 5024m autoextend on next 1024m

Create tablespace crddtl03_ts datafile'Flux size oracleDataCompact crddtl03accounts.dbf' size 8024m autoextend on next 1024m autoallocate

Alter tablespace crddtl03_ts add datafile'Flux size oracleDataCompact crddtl03apacts.dbf'SQL 1024m autoextend on next 1024m

Alter tablespace crddtl03_ts add datafile'Flux size oracleDataCompact crddtl03bdata.dbf'SQL 1024m autoextend on next 1024m

Create tablespace crddtlidx03_ts datafile'Flux size size 5024m autoextend on next 1024m

Create tablespace crddtl04_ts datafile'DUR autoextend on next oracle autoallocate size 8024m oracle 1024m

Alter tablespace crddtl04_ts add datafile'DUR size oracle autoextend on next 1024m

Alter tablespace crddtl04_ts add datafile'DUR size oracle autoextend on next 1024m crddtl04binstruments. DBF'

So the long wait began.

4. How do you know how much data has been imported? Where is the progress of the import? Or is the import process stuck or dead?

First look at feedback. I set the parameter feedback=10000000 in the importer and respond to a black dot for every 10 million data imported.

Second, you can look at the import log. Each time a partition is imported, it inserts a record in the log.

Third, open the resource manager, let's take a look at the imp process, the occupied cpu, hard disk, network, memory resources.

But everything on this server was slow at that time. It took me more than 10 minutes to assign an 8G file. How do you explain this?

5. After a long wait, it took me 2 days and 7 hours to import this 1.8 billion data into it.

Finally, take a look at the data file size:

Click (here) to collapse or open

Directory size (M) tablespace

D:\ ORACLE\ TABLESPACE\ CRDDTL01A_TS.DBF 32767 CRDDTL01_TS

D:\ ORACLE\ TABLESPACE\ CRDDTL01_TS.DBF 32767 CRDDTL01_TS

D:\ ORACLE\ TABLESPACE\ CRDDTL01B_TS.DBF 32767 CRDDTL01_TS

F:\ ORACLEDATA\ CRDDTL01D_TS.DBF 25600 CRDDTL01_TS

E:\ ORACLE_DATA\ CRDDTL02C_TS.DBF 9216 CRDDTL02_TS

E:\ ORACLE_DATA\ CRDDTL02D_TS.DBF 8192 CRDDTL02_TS

E:\ ORACLE_DATA\ CRDDTL02B_TS.DBF 32767 CRDDTL02_TS

E:\ ORACLE_DATA\ CRDDTL02_TS.DBF 32767 CRDDTL02_TS

E:\ ORACLE_DATA\ CRDDTL02A_TS.DBF 30720 CRDDTL02_TS

F:\ ORACLEDATA\ CRDDTL03D_TS.DBF 5120 CRDDTL03_TS

F:\ ORACLEDATA\ CRDDTL03B_TS.DBF 28672 CRDDTL03_TS

F:\ ORACLEDATA\ CRDDTL03_TS.DBF 32600 CRDDTL03_TS

F:\ ORACLEDATA\ CRDDTL03A_TS.DBF 27648 CRDDTL03_TS

F:\ ORACLEDATA\ CRDDTL03C_TS.DBF 5120 CRDDTL03_TS

D:\ ORACLE\ TABLESPACE\ CRDDTL04A_TS.DBF 28672 CRDDTL04_TS

D:\ ORACLE\ TABLESPACE\ CRDDTL04B_TS.DBF 32767 CRDDTL04_TS

E:\ ORACLE_DATA\ CRDDTL04D_TS.DBF 7168 CRDDTL04_TS

E:\ ORACLE_DATA\ CRDDTL04C_TS.DBF 7168 CRDDTL04_TS

D:\ ORACLE\ TABLESPACE\ CRDDTL04_TS.DBF 32767 CRDDTL04_TS

A tablespace has four files, almost 120g

This is the end of the article on "how to import a table of 1.8 billion 300G data files in the database". I hope the above content can be of some help to you, so that you can learn more knowledge. If you think the article is good, please share it out for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.