ORA-00059: maximum number of DB_FILES exceeded

SQL> show parameter db_files;


NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_files                             integer     200

SQL> alter system set db_files=300 scope=spfile;

SQL> shutdown immediate

SQL> startup

Open Oracle Table using Excel

Export Oracle Data to CSV using UTL_FILE

$ mkdir -p /home/oracle/export_data

SQL> create directory export_file as '/home/oracle/export_data';
SQL> grant read,write on directory export_file to public; (Optional)

SQL>

create or replace procedure dump_table_to_csv( p_tname in varchar2,
                                                 p_dir   in varchar2,
                                                 p_filename in varchar2 )
      is
         l_output        utl_file.file_type;
         l_theCursor     integer default dbms_sql.open_cursor;
         l_columnValue   varchar2(4000);
         l_status        integer;
         l_query         varchar2(1000)
                         default 'select * from ' || p_tname;
         l_colCnt        number := 0;
         l_separator     varchar2(1);
         l_descTbl       dbms_sql.desc_tab;
begin
         l_output := utl_file.fopen( p_dir, p_filename, 'w' );
         execute immediate 'alter session set nls_date_format=''dd-mon-yyyy hh24:mi:ss'' ';
         dbms_sql.parse(  l_theCursor,  l_query, dbms_sql.native );
         dbms_sql.describe_columns( l_theCursor, l_colCnt, l_descTbl );
          for i in 1 .. l_colCnt loop
            utl_file.put( l_output, l_separator || '"' || l_descTbl(i).col_name|| '"' );
            dbms_sql.define_column( l_theCursor, i, l_columnValue, 4000 );
            l_separator := ',';
         end loop;
         utl_file.new_line( l_output );
         l_status := dbms_sql.execute(l_theCursor);
          while ( dbms_sql.fetch_rows(l_theCursor) > 0 ) loop
             l_separator := '';
             for i in 1 .. l_colCnt loop
                 dbms_sql.column_value( l_theCursor, i, l_columnValue );
                 utl_file.put( l_output, l_separator || l_columnValue );
                 l_separator := ',';
             end loop;
             utl_file.new_line( l_output );
         end loop;
         dbms_sql.close_cursor(l_theCursor);
         utl_file.fclose( l_output );
         execute immediate 'alter session set nls_date_format=''dd-MON-yy'' ';
exception
      when utl_file.invalid_mode then
         raise_application_error(-20101,'Invalid Mode');
      when utl_file.invalid_operation then
         raise_application_error(-20102,'Invalid Operation');
      when utl_file.invalid_filehandle then
         raise_application_error(-20103,'Invalid FileHandle');
      when utl_file.write_error then
         raise_application_error(-20104,'Write Error');
      when utl_file.read_error then
         raise_application_error(-20105,'Read Error');
      when utl_file.internal_error then
         raise_application_error(-20106,'Internal Error');
      when others then
           utl_file.fclose( l_output );
           execute immediate 'alter session set nls_date_format=''dd-MON-yy'' ';
           raise;
end;
/

SQL> exec dump_table_to_csv( 'ALL_OBJECTS', 'EXPORT_FILE','ALL.csv');
PL/SQL procedure successfully completed.


[oracle@ncdung ~]$ head -10 /home/oracle/export_data/ALL.csv
"OWNER","OBJECT_NAME","SUBOBJECT_NAME","OBJECT_ID","DATA_OBJECT_ID","OBJECT_TYPE","CREATED","LAST_DDL_TIME","TIMESTAMP","STATUS","TEMPORARY","GENERATED","SECONDARY","NAMESPACE","EDITION_NAME"
SYS,ICOL$,,20,2,TABLE,15-aug-2009 00:16:51,15-aug-2009 00:29:27,2009-08-15:00:16:51,VALID,N,N,N,1,
SYS,I_USER1,,46,46,INDEX,15-aug-2009 00:16:51,15-aug-2009 00:16:51,2009-08-15:00:16:51,VALID,N,N,N,4,
SYS,CON$,,28,28,TABLE,15-aug-2009 00:16:51,15-aug-2009 00:36:04,2009-08-15:00:16:51,VALID,N,N,N,1,
SYS,UNDO$,,15,15,TABLE,15-aug-2009 00:16:51,15-aug-2009 00:16:51,2009-08-15:00:16:51,VALID,N,N,N,1,
SYS,C_COBJ#,,29,29,CLUSTER,15-aug-2009 00:16:51,15-aug-2009 00:16:51,2009-08-15:00:16:51,VALID,N,N,N,5,
SYS,I_OBJ#,,3,3,INDEX,15-aug-2009 00:16:51,15-aug-2009 00:16:51,2009-08-15:00:16:51,VALID,N,N,N,4,
SYS,PROXY_ROLE_DATA$,,25,25,TABLE,15-aug-2009 00:16:51,15-aug-2009 00:16:51,2009-08-15:00:16:51,VALID,N,N,N,1,
SYS,I_IND1,,41,41,INDEX,15-aug-2009 00:16:51,15-aug-2009 00:16:51,2009-08-15:00:16:51,VALID,N,N,N,4,
SYS,I_CDEF2,,54,54,INDEX,15-aug-2009 00:16:51,15-aug-2009 00:16:51,2009-08-15:00:16:51,VALID,N,N,N,4,

Note: Table, Directory must be Up-Case

Cấu hình RHEL/OEL6 cài đặt Oracle 11G

1. Yêu cầu tối thiểu:
• RAM tối thiểu 1G
• Yêu cầu Swap:
• RAM 1G - 2G   ==> Swap = RAM*1.5
• RAM 2G - 16G ==> Swap = RAM*1
• RAM >16G       ==> Swap = RAM
• Thư mục /tmp từ 1G - 2TB
• Oracle software ~ 4.4G
2. Cấu hình OS
• Tạo user
• groupadd dba
• groupadd oinstall
• useradd -g dba -G oinstall oracle
• Kiểm tra các package thiếu
• rpm -q binutils compat-libstdc++-33 glibc ksh libaio libgcc libstdc++ make compat-libcap1 gcc gcc-c++ glibc-devel libaio-devel libstdc++-devel sysstat compat-libstdc++-33
• Chỉnh sửa kernel
• vi /etc/sysctl.conf

kernel.shmall = physical RAM size / pagesize For most systems, this will be the value 2097152 (8G)

kernel.shmmax = 1/2 of physical RAM (the value 2147483648 for a system with 4GB of physical RAM)

kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
fs.file-max = 512 x processes (for example 6815744 for 13312 processes)
fs.aio-max-nr = 1048576
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576

• Sửa lại file limit.conf
• vi /etc/security/limits.conf

oracle soft nproc 2047

oracle hard nproc 16384
oracle soft nofile 1024
oracle hard nofile 65536 
oracle soft stack 10240

• Sửa lại file /etc/pam.d/login
• vi /etc/pam.d/login

session required pam_limits.so

• Sửa lại file /etc/profile
• vi /etc/profile

if [ $USER = "oracle" ]; then

    if [ $SHELL = "/bin/ksh" ]; then

       ulimit -u 16384 

       ulimit -n 65536

    else

       ulimit -u 16384 -n 65536

    fi

fi

• Sửa lại file /etc/hosts
• vi /etc/hosts

192.168.2.2     ncdung.oracle.com       ncdung

• Disable SELinux
• vi /etc/selinux/config

#SELINUX=enforcing

SELINUX=disabled





NOTE: 1441282.1

Export Oracle Data to CSV using SQL*Plus

SQL> SET NEWPAGE NONE

SQL> SET UNDERLINE OFF

SQL> SET COLSEP ',' 

SQL> SET PAGESIZE 0
SQL> SET LINESIZE 9999

SQL> SET FEEDBACK OFF

SQL> SET HEADING OFF

SQL> SPOOL ABC_YYYYMMDD.CSV

SQL> select * 

from ABC 

where 1=1

and SNAPSHOT_DATE='19-MAR-2013'

;

SQL> SPOOL OFF

Format Short Date In Oracle

SQL> select to_date('3/4/1983','FMDD/FMMM/YYYY') Short_Date
from dual
;


SHORT_DATE
----------
03-APR-83
1 row selected.