Default role

SQL> grant connect,resource to datauser identified by 123123;

Grant succeeded.

SQL> grant connect,resource to selectuser identified by 123123;

Grant succeeded.

SQL> create table datauser.table1(id number,data varchar2(10));

Table created.

SQL> create table datauser.table2(id number,data varchar2(10));

Table created.

SQL>  create table datauser.table3(id number,data varchar2(10));

Table created.

SQL> insert into datauser.table1 values (1,'table1');

1 row created.

SQL> insert into datauser.table2 values(2,'table2');

1 row created.

SQL> insert into datauser.table3 values(3,'table3');

1 row created.

SQL> commit;

Commit complete.

SQL> create role role_r_table1;

Role created.

SQL> create role role_r_table2;

Role created.

SQL> create role role_r_table3;

Role created.

SQL> grant select on datauser.table1 to role_r_table1;

Grant succeeded.

SQL> grant select on datauser.table2 to role_r_table2;

Grant succeeded.

SQL> grant select on datauser.table3 to role_r_table3;

Grant succeeded.

SQL> grant role_r_table1,role_r_table2 to selectdata;
grant role_r_table1,role_r_table2 to selectdata
                                     *
ERROR at line 1:
ORA-01917: user or role 'SELECTDATA' does not exist


SQL> grant role_r_table1,role_r_table2 to selectuser;

Grant succeeded.

SQL> alter user selectuser default role role_r_table1;

User altered.

SQL>
SQL>
SQL> conn selectuser/123123;
ERROR:
ORA-01045: user SELECTUSER lacks CREATE SESSION privilege; logon denied


Warning: You are no longer connected to ORACLE.
SQL> conn / as sysdba
Connected.
SQL> grant create session to selectuser;

Grant succeeded.

SQL> conn selectuser/123123;
Connected.
SQL> select * from datauser.table1;

        ID DATA
---------- ----------
         1 table1

SQL> select * from datauser.table2;
select * from datauser.table2
                       *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> set role role_r_table2;

Role set.

SQL> select * from datauser.table2;

        ID DATA
---------- ----------
         2 table2

SQL> set role role_r_table3;
set role role_r_table3
*
ERROR at line 1:
ORA-01924: role 'ROLE_R_TABLE3' not granted or does not exist


SQL> conn / as sysdba
Connected.
SQL> alter user selectuser default role all;

User altered.

SQL> conn selectuser/123123;
Connected.
SQL> select * from datauser.table1;

        ID DATA
---------- ----------
         1 table1

SQL> select * from datauser.table2;

        ID DATA
---------- ----------
         2 table2

SQL> select * from datauser.table3;
select * from datauser.table3
                       *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> show user
USER is "SELECTUSER"
SQL> conn / as sysdba
Connected.
SQL> grant role_r_table3 to selectuser;

Grant succeeded.

SQL> conn selectuser/123123
Connected.
SQL> select * from datauser.table1;

        ID DATA
---------- ----------
         1 table1

SQL> select * from datauser.table2;

        ID DATA
---------- ----------
         2 table2

SQL> conn / as sysdba
Connected.
SQL> alter user selectuser default role role_r_table1;

User altered.

SQL> conn selectuser/123123
Connected.
SQL> select * from datauser.table1;

        ID DATA
---------- ----------
         1 table1

SQL> select * from datauser.table2;
select * from datauser.table2
                       *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> select * from datauser.table3;
select * from datauser.table3
                       *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> set role role_r_table3;

Role set.

SQL> select * from datauser.table1;
select * from datauser.table1
                       *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> select * from datauser.table2;
select * from datauser.table2
                       *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> select * from datauser.table3;

        ID DATA
---------- ----------
         3 table3

SQL> set role all
  2  ;

Role set.

SQL> select * from datauser.table1;

        ID DATA
---------- ----------
         1 table1

SQL> select * from datauser.table2;

        ID DATA
---------- ----------
         2 table2

SQL> select * from datauser.table3;

        ID DATA
---------- ----------
         3 table3

Start ASM - Error ORA-01078, ORA-29701

Error:

[grid@OCA11g ~]$ sqlplus / as sysasm
SQL*Plus: Release 11.2.0.1.0 Production on Mon May 27 09:43:00 2013
Copyright (c) 1982, 2009, Oracle.  All rights reserved.
Connected to an idle instance.

SQL> startup
ORA-01078: failure in processing system parameters
ORA-29701: unable to connect to Cluster Synchronization Service



Solution:

[grid@OCA11g ~]$ crsctl start resource ora.cssd
CRS-2672: Attempting to start 'ora.cssd' on 'oca11g'
CRS-2679: Attempting to clean 'ora.diskmon' on 'oca11g'
CRS-2681: Clean of 'ora.diskmon' on 'oca11g' succeeded
CRS-2672: Attempting to start 'ora.diskmon' on 'oca11g'
CRS-2676: Start of 'ora.diskmon' on 'oca11g' succeeded
CRS-2676: Start of 'ora.cssd' on 'oca11g' succeeded

[grid@OCA11g ~]$ crs_stat -t
Name           Type           Target    State     Host
------------------------------------------------------------
ora.DATA.dg    ora....up.type OFFLINE   OFFLINE
ora.FRA.dg     ora....up.type OFFLINE   OFFLINE
ora....ER.lsnr ora....er.type ONLINE    ONLINE    oca11g
ora.asm        ora.asm.type   OFFLINE   OFFLINE
ora.cssd       ora.cssd.type  ONLINE    ONLINE    oca11g
ora.diskmon    ora....on.type ONLINE    ONLINE    oca11g
ora.orcl.db    ora....se.type OFFLINE   OFFLINE


[grid@OCA11g ~]$ sqlplus / as sysasm
SQL*Plus: Release 11.2.0.1.0 Production on Mon May 27 09:47:42 2013
Copyright (c) 1982, 2009, Oracle.  All rights reserved.
Connected to an idle instance.

SQL> startup
ASM instance started

Total System Global Area  284565504 bytes
Fixed Size                  1336036 bytes
Variable Size             258063644 bytes
ASM Cache                  25165824 bytes
ASM diskgroups mounted

Get Meta Data of Table

SQL> grant connect,resource to dungnc identified by oracle123;
Grant succeeded.

SQL> conn dungnc/oracle123;
Connected.

SQL> create table test (id number);
Table created.

SQL> conn / as sysdba
Connected.

SQL> select dbms_metadata.get_ddl('TABLE','TEST','DUNGNC') from dual;

DBMS_METADATA.GET_DDL('TABLE','TEST','DUNGNC')
--------------------------------------------------------------------------------

  CREATE TABLE "DUNGNC"."TEST"
   (    "ID" NUMBER
   ) SEGMENT CREATION DEFERRED

Dump Redo Log - Insert Single Row Record

SQL> alter system switch logfile;
System altered.

SQL> /
System altered.

SQL> /
System altered.

SQL> set transaction name 'DungNC';
Transaction set.

SQL> insert into test.nhanvien values (4,'D');
1 row created.

SQL> select xid,status from v$transaction;
XID              STATUS
---------------- ----------------
03000600D20A0000 ACTIVE

SQL> select b.member
from v$log a, v$logfile b
where a.group#=b.group#
and a.status = 'CURRENT';  2    3    4

MEMBER
--------------------------------------------------------------------------------
+DATA/orcl/onlinelog/group_1.262.815134559
+DATA/orcl/onlinelog/group_1.263.815134559

SQL> commit;
Commit complete.

SQL> alter system switch logfile;
System altered.

SQL> /
System altered.

SQL> alter system dump logfile '+DATA/orcl/onlinelog/group_1.262.815134559';
System altered.



REDO RECORD - Thread:1 RBA: 0x000049.0000000b.0010 LEN: 0x01ec VLD: 0x05
SCN: 0x0000.00208c95 SUBSCN:  1 05/25/2013 07:33:33

CHANGE #1 TYP:0 CLS:21 AFN:3 DBA:0x00c000a0 OBJ:4294967295 SCN:0x0000.00208c4a SEQ:1 OP:5.2 ENC:0 RBL:0
ktudh redo: slt: 0x0006 sqn: 0x00000ad2 flg: 0x0012 siz: 136 fbi: 0
            uba: 0x00c005a9.00eb.14    pxid:  0x0000.000.00000000

CHANGE #2 TYP:0 CLS:22 AFN:3 DBA:0x00c005a9 OBJ:4294967295 SCN:0x0000.00208c49 SEQ:2 OP:5.1 ENC:0 RBL:0
ktudb redo: siz: 136 spc: 4916 flg: 0x0012 seq: 0x00eb rec: 0x14
            xid:  0x0003.006.00000ad2
ktubl redo: slt: 6 rci: 0 opc: 11.1 [objn: 75259 objd: 75259 tsn: 4]
Undo type:  Regular undo        Begin trans    Last buffer split:  No
Temp Object:  No
Tablespace Undo:  No
             0x00000000  prev ctl uba: 0x00c005a9.00eb.12
prev ctl max cmt scn:  0x0000.00208892  prev tx cmt scn:  0x0000.002088c3
txn start scn:  0xffff.ffffffff  logon user: 0  prev brb: 12584358  prev bcl: 0 BuExt idx: 0 flg2: 0
KDO undo record:
KTB Redo
op: 0x03  ver: 0x01
compat bit: 4 (post-11) padding: 1
op: Z
KDO Op code: DRP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x01000226  hdba: 0x01000222
itli: 2  ispac: 0  maxfr: 4858
tabn: 0 slot: 1(0x1)

CHANGE #3 TYP:2 CLS:1 AFN:4 DBA:0x01000226 OBJ:75259 SCN:0x0000.00208c46 SEQ:1 OP:11.2 ENC:0 RBL:0
KTB Redo
op: 0x01  ver: 0x01
compat bit: 4 (post-11) padding: 1
op: F  xid:  0x0003.006.00000ad2    uba: 0x00c005a9.00eb.14
KDO Op code: IRP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x01000226  hdba: 0x01000222
itli: 2  ispac: 0  maxfr: 4858
tabn: 0 slot: 1(0x1) size/delt: 8
fb: --H-FL-- lb: 0x2  cc: 2
null: --
col  0: [ 2]  c1 05
col  1: [ 1]  44

CHANGE #4 MEDIA RECOVERY MARKER SCN:0x0000.00000000 SEQ:0 OP:5.20 ENC:0
session number   = 36
serial  number   = 458
transaction name = DungNC
version 186646784
audit sessionid 4294967295
Client Id =

Truncate Partition Table with GLOBAL INDEX and LOCAL INDEX

1. Create 4 tablespace

SQL> create tablespace part1 datafile '/d01/oracle/database/oradata/DUNGDB/file1.dbf' size 1M;
Tablespace created.

SQL> create tablespace part2 datafile '/d01/oracle/database/oradata/DUNGDB/file2.dbf' size 1M;
Tablespace created.

SQL> create tablespace part3 datafile '/d01/oracle/database/oradata/DUNGDB/file3.dbf' size 1M;
Tablespace created.

SQL> create tablespace part4 datafile '/d01/oracle/database/oradata/DUNGDB/file4.dbf' size 1M;
Tablespace created.


2. Create partition table TEST_PARTITION

SQL> CREATE TABLE test_partition
(test_id NUMBER(5),
test_name VARCHAR2(30),
test_date DATE)
PARTITION BY RANGE(test_date)
(PARTITION tb_part1 VALUES LESS THAN(TO_DATE('20/10/2010','DD/MM/YYYY')) TABLESPACE part1 ,
PARTITION tb_part2 VALUES LESS THAN(TO_DATE('21/10/2010','DD/MM/YYYY')) TABLESPACE part2 ,
PARTITION tb_part3 VALUES LESS THAN(TO_DATE('22/10/2010','DD/MM/YYYY')) TABLESPACE part3 ,
PARTITION tb_part4 VALUES LESS THAN(TO_DATE('23/10/2010','DD/MM/YYYY')) TABLESPACE part4);

Table created.


3. Create GLOBAL INDEX and PRIMARY KEY

SQL> CREATE UNIQUE INDEX test_id_ix ON test_partition(test_id) GLOBAL;
Index created.


SQL> Alter table test_partition add (
Constraint test_id_pk primary key (test_id)
Using index test_id_ix);
Table altered.


4. Insert data to table TEST_PARTITION

SQL> INSERT INTO test_partition VALUES (1,'Test case 1',TO_DATE('19/10/2010 10:10:10','DD/MM/YYYY HH24:MI:SS'));
1 row created.

SQL> INSERT INTO test_partition VALUES (2,'Test case 2',TO_DATE('19/10/2010 10:10:10','DD/MM/YYYY HH24:MI:SS'));
1 row created.

SQL> INSERT INTO test_partition VALUES (3,'Test case 3',TO_DATE('20/10/2010 10:10:10','DD/MM/YYYY HH24:MI:SS'));
1 row created.

SQL> INSERT INTO test_partition VALUES (4,'Test case 4',TO_DATE('20/10/2010 10:10:10','DD/MM/YYYY HH24:MI:SS'));
1 row created.

SQL> INSERT INTO test_partition VALUES (5,'Test case 5',TO_DATE('21/10/2010 10:10:10','DD/MM/YYYY HH24:MI:SS'));
1 row created.

SQL> INSERT INTO test_partition VALUES (6,'Test case 6',TO_DATE('21/10/2010 10:10:10','DD/MM/YYYY HH24:MI:SS'));
1 row created.

SQL> INSERT INTO test_partition VALUES (7,'Test case 7',TO_DATE('22/10/2010 10:10:10','DD/MM/YYYY HH24:MI:SS'));
1 row created.

SQL> INSERT INTO test_partition VALUES (8,'Test case 8',TO_DATE('22/10/2010 10:10:10','DD/MM/YYYY HH24:MI:SS'));
1 row created.

SQL> commit;
Commit complete.


SQL> SELECT partition_name, tablespace_name, high_value
FROM user_tab_partitions
WHERE TABLE_NAME='TEST_PARTITION';

PARTITION_NAME TABLESPACE_NAME HIGH_VALUE
------------------------------ ------------------------------ --------------------------------------------------------------------------------
TB_PART1 PART1 TO_DATE(' 2010-10-20 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
TB_PART2 PART2 TO_DATE(' 2010-10-21 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
TB_PART3 PART3 TO_DATE(' 2010-10-22 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
TB_PART4 PART4 TO_DATE(' 2010-10-23 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA



5. Truncate 1 partition of table and try to insert data to table

SQL> ALTER TABLE TEST_PARTITION TRUNCATE PARTITION tb_part1;
Table truncated.


SQL> select * from TEST_PARTITION;

TEST_ID TEST_NAME TEST_DATE
---------- ------------------------------ ---------
3 Test case 3 20-OCT-10
4 Test case 4 20-OCT-10
5 Test case 5 21-OCT-10
6 Test case 6 21-OCT-10
7 Test case 7 22-OCT-10
8 Test case 8 22-OCT-10

6 rows selected.

SQL> INSERT INTO TEST_PARTITION VALUES (1,'Test case 1',TO_DATE('19/10/2010 10:10:10','DD/MM/YYYY HH24:MI:SS'));
INSERT INTO TEST_PARTITION VALUES (1,'Test case 1',TO_DATE('19/10/2010 10:10:10','DD/MM/YYYY HH24:MI:SS'))
*
ERROR at line 1:
ORA-01502: index 'SYS.TEST_ID_IX' or partition of such index is in unusable state



SQL> select INDEX_NAME,STATUS
2 from user_indexes
3 where INDEX_NAME='TEST_ID_IX';

INDEX_NAME STATUS
------------------------------ --------
TEST_ID_IX UNUSABLE


6. Index need to rebuild

SQL> alter index TEST_ID_IX rebuild;

Index altered.


SQL> select INDEX_NAME,STATUS
2 from user_indexes
3 where INDEX_NAME='TEST_ID_IX';

INDEX_NAME STATUS
------------------------------ --------
TEST_ID_IX VALID



SQL> INSERT INTO TEST_PARTITION VALUES (1,'Test case 1',TO_DATE('19/10/2010 10:10:10','DD/MM/YYYY HH24:MI:SS'));

1 row created.

SQL> INSERT INTO TEST_PARTITION VALUES (2,'Test case 2',TO_DATE('19/10/2010 10:10:10','DD/MM/YYYY HH24:MI:SS'));

1 row created.

SQL> commit;

Commit complete.

SQL> select *
2 from TEST_PARTITION;

TEST_ID TEST_NAME TEST_DATE
---------- ------------------------------ ---------
1 Test case 1 19-OCT-10
2 Test case 2 19-OCT-10
3 Test case 3 20-OCT-10
4 Test case 4 20-OCT-10
5 Test case 5 21-OCT-10
6 Test case 6 21-OCT-10
7 Test case 7 22-OCT-10
8 Test case 8 22-OCT-10

8 rows selected.


7. Try again with LOCAL INDEX (Local index can't used to build Primary key)

SQL> Alter table TEST_PARTITION drop primary key;

Table altered.

SQL> DROP INDEX test_id_ix;

Index dropped.

SQL> CREATE INDEX test_id_ix ON test_partition(test_id) LOCAL;

Index created.

SQL> ALTER TABLE TEST_PARTITION TRUNCATE PARTITION tb_part1;

Table truncated.

SQL> select *
2 from TEST_PARTITION;

TEST_ID TEST_NAME TEST_DATE
---------- ------------------------------ ---------
3 Test case 3 20-OCT-10
4 Test case 4 20-OCT-10
5 Test case 5 21-OCT-10
6 Test case 6 21-OCT-10
7 Test case 7 22-OCT-10
8 Test case 8 22-OCT-10

6 rows selected.

SQL> select INDEX_NAME,STATUS
2 from user_indexes
3 where INDEX_NAME='TEST_ID_IX';

INDEX_NAME STATUS
------------------------------ --------
TEST_ID_IX N/A

SQL> INSERT INTO TEST_PARTITION VALUES (1,'Test case 1',TO_DATE('19/10/2010 10:10:10','DD/MM/YYYY HH24:MI:SS'));

1 row created.

SQL> INSERT INTO TEST_PARTITION VALUES (2,'Test case 2',TO_DATE('19/10/2010 10:10:10','DD/MM/YYYY HH24:MI:SS'));

1 row created.

SQL> commit;

Commit complete.

SQL> select INDEX_NAME,STATUS
2 from user_indexes
3 where INDEX_NAME='TEST_ID_IX';

INDEX_NAME STATUS
------------------------------ --------
TEST_ID_IX N/A


8. Clear test case

SQL> DROP INDEX test_id_ix;
Index dropped.

SQL> DROP TABLE TEST_PARTITION CASCADE CONSTRAINTS PURGE;
Table dropped.

SQL> DROP TABLESPACE part1 INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped.

SQL> DROP TABLESPACE part2 INCLUDING CONTENTS AND DATAFILES;
Tablespace dropped.

SQL> DROP TABLESPACE part3 INCLUDING CONTENTS AND DATAFILES;
Tablespace dropped.

SQL> DROP TABLESPACE part4 INCLUDING CONTENTS AND DATAFILES;
Tablespace dropped.

Expdp/Impdp Metadata Only Oracle Schema

$ sqlplus / as sysdba

SQL> create directory export_dump as '/home/oracle/backup';
Directory created.

SQL> create user export1 identified by oracle123;
User created.

SQL> grant connect,resource to export1;
Grant succeeded.

SQL> create table export1.data1 as select * from dba_objects;
Table created.

SQL> exit

$ mkdir -p /home/oracle/backup

$ expdp system/oracle123 DUMPFILE=export.dmp DIRECTORY=export_dump SCHEMAS=export1 content=metadata_only

 $ impdp system/oracle123 DUMPFILE=export.dmp DIRECTORY=export_dump SCHEMAS=export1 content=metadata_only remap_schema=export1:import1

$ impdp system/oracle123 DUMPFILE=export.dmp DIRECTORY=export_dump SCHEMAS=export1 content=metadata_only remap_schema=export1:import2

 $ sqlplus / as sysdba

SQL> create table export1.data2 as select * from dba_tables;
Table created.

SQL> exit

$ expdp system/oracle123 DUMPFILE=export3.dmp DIRECTORY=export_dump SCHEMAS=export1 content=metadata_only

 $ impdp system/oracle123 DUMPFILE=export3.dmp DIRECTORY=export_dump SCHEMAS=export1 content=metadata_only table_exists_action=replace remap_schema=export1:import2

Tổng kết:
- Expdp có thể export metadata
- Khi impdp, nếu chưa có user thì sẽ tạo user (user system)
- Bổ sung tham số table_exists_action để việc impdp uyển chuyển hơn
- Khi impdp, nếu user có tồn tại sẽ báo lỗi ORA-31684: Object type USER:"IMPORT2" already exists

Setup Yum with ISO file

# cd /
# mkdir /mnt/RHEL53
# mount -o loop -t iso9660 /data5/Enterprise-R5-U3-Server-x86_64-dvd.iso /mnt/RHEL53

# vi /etc/yum.repos.d/RHEL53.repo

[RHEL53]
name = Red-Hat Enterprise Linux 5.3 DVD
baseurl=file:///mnt/RHEL53/Server/
gpgcheck=1
enabled=1
gpgkey=file:///mnt/RHEL53/RPM-GPG-KEY-oracle


# yum search abc

#yum install abc

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.