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