CODEWITHSUNDEEP
sqloracle-databasesql-loader
user4441082
user4441082

Reputation: 371

Loading large csv file by altering tablespace

I am loading a large csv file (>1.5G) in Oracle on my laptop. And some error came out saying "ORA-01653: table SYSTEM.CTS can't pass 1024 (in tablespace SYSTEM ) extend."

I know I can fix it by using code like:

alter tablespace SYSTEM add datafile 'XXX' size 2000m;

My questions are:

(1) What should XXX be in my code? I am loading file using sqlldr, and here is my ctl file:

LOAD DATA INFILE 'C:\...cts.csv'
APPEND
INTO TABLE cts
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
   Tdate,
   Symbol,
   Topen,
   Thigh,
   Tlow,
   Tclose,
   Tvolume
)

(2) Is the size 2000m enough please?

Upvotes: 0

Views: 291

Answers (1)

Entalyan
Entalyan

Reputation: 931

You can increase the SYSTEM tablespace by adding another datafile. In the place of your 'XXX' should be the location of your new datafile, for instance:

alter tablespace SYSTEM add datafile 'C:\oracle\OracleHome\oradata\orcl\extra_system.dbf' size 2000m;

I would think 2000m would be enough for a 1,5G file, but you might want to put a little extra in there, considering it's the SYSTEM tablespace.

It is probably wise to put your own data in a seperate tablespace btw, and not in SYSTEM. This way you have seperate datafiles for SYSTEM and your personal applications, which means you can more easily move your own stuff to a larger harddrive, or back them up. This is also more secure and managable, because you don't have to use a user that can alter the SYSTEM tablespaces.

EDIT: The datafile is a file used by Oracle to store its data. All data inside the database will be stored inside these types of files, in the Oracle installation directory (typically). You can see the name of the current SYSTEM datafiles by using this query:

SELECT  FILE_NAME, BLOCKS, TABLESPACE_NAME
FROM DBA_DATA_FILES
WHERE TABLESPACE_NAME = 'SYSTEM';

Upvotes: 1

Related Questions