Monday, March 20, 2017

ORA-00059: maximum number of DB_FILES exceeded

SQL>  sho parameter db_files

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_files                             integer     200
SQL> alter system set db_files = 1000 scope = spfile;

System altered.

SQL>
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.

Total System Global Area 1.9003E+10 bytes
Fixed Size                  2399752 bytes
Variable Size            2256536056 bytes
Database Buffers         1.6735E+10 bytes
Redo Buffers                9273344 bytes
Database mounted.
Database opened.
SQL> sho parameter db_files

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_files                             integer     1000

Wednesday, March 15, 2017

Using SQL Loader

1. Create Table 

CREATE TABLE H1BCASE_DATA
( CASE_SL             NUMBER(10),
  CASE_STATUS         VARCHAR2(1000 BYTE),
  EMPLOYER_NAME       VARCHAR2(1000 BYTE),
  SOC_NAME            VARCHAR2(1000 BYTE),
  JOB_TITLE           VARCHAR2(1000 BYTE),
  FULL_TIME_POSITION  VARCHAR2(1000 BYTE),
  PREVAILING_WAGE     VARCHAR2(1000 BYTE),
  YEAR                VARCHAR2(1000 BYTE),
  WORKSITE            VARCHAR2(1000 BYTE),
  LON                 VARCHAR2(1000 BYTE),
  LAT                 VARCHAR2(1000 BYTE)
);

2. Create ctl file.

bash-4.3$ vi h1b_kaggle.ctl

LOAD DATA
INFILE '/home/oracle/h1b_kaggle.csv'
BADFILE '/home/oracle/h1b_kaggle.bad'
DISCARDFILE '/home/oracle/h1b_kaggle.dsc'
INSERT INTO TABLE H1BCASE_DATA
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS
(CASE_SL, CASE_STATUS, EMPLOYER_NAME, SOC_NAME, JOB_TITLE, FULL_TIME_POSITION, PREVAILING_WAGE, YEAR, WORKSITE, LON, LAT)

3. Load data into table 

bash-4.3$ sqlldr userid=HR/HR control=h1b_kaggle.ctl log=h1b_kaggle.log