Monday, June 15, 2015

impdp / requires unjustified privileges / to copy a table between your own schemas / different schema names


Introduction


"Regular users" (without DBA privileges) need to copy tables between databases.
Exp / Imp were good tools in the past. They are deprecated today.

Expdp / Impdp are recommended instead. As usual, new tools come with new issues.

In general, issue is that new tool/approach is not "Regular user" friendly. Also, I have not seen that any other new tool would cover missing functionality.

Expdp file is written by database server and Impdp file is read by database server (exp/imp file is written/read by client).  Therefore, "Regular user" must have access to both database server "data-pump" locations. This is quite often unacceptable from security point of view.

Another option is to use database link between database servers. But there is quite nasty issue if schema names does not match.

Issue is the following:
"Regular user" can not expdp/impdp a table between databases if schema names does not match even if "Regular user" has full access (read: uses schema name to connect) to source and target database. Error "ORA-31631: privileges are required" is reported instead of table copy.

Workaround is the following:
You need to grant the following roles to "Regular user" to make successful copy (yes, both are needed!):
  1. role DATAPUMP_EXP_FULL_DATABASE in source database
  2. role DATAPUMP_IMP_FULL_DATABASE in target database
I think it is enough to note that both of listed roles give "SELECT ANY TABLE" privilege to user.
In general, this is just not acceptable.


Test case


Here is test case to demonstrate the issue based on Oracle 12.1.0.2 default database installation.

Description of test case:
  1. create user USER_SRC with table TABLE_SRC.
  2. create user USER_TGT with privilege CREATE TABLE
  3. connect USER_TGT. create DATABASE LINK to have connection to USER_SRC;
  4. start impdp to copy TABLE_SRC over database link

Result:
  • ORA-31631: privileges are required
  • ORA-39109: Unprivileged users may not operate upon other users' schemas


Here is test case output

SQL*Plus: Release 12.1.0.2.0 Production on Sun Jun 14 21:03:05 2015

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

SQL> spool test.log
SQL>
SQL> connect / as sysdba
Connected.
SQL>
SQL> -- create user
SQL> CREATE USER user_src IDENTIFIED BY user_src DEFAULT TABLESPACE users QUOTA UNLIMITED ON users;

User created.

SQL> GRANT CREATE SESSION, CREATE TABLE TO user_src;

Grant succeeded.

SQL>
SQL> -- create user
SQL> CREATE USER user_tgt IDENTIFIED BY user_tgt DEFAULT TABLESPACE users QUOTA UNLIMITED ON users;

User created.

SQL> GRANT CREATE SESSION, CREATE TABLE, CREATE DATABASE LINK TO user_tgt;

Grant succeeded.

SQL> GRANT READ, WRITE ON DIRECTORY DATA_PUMP_DIR TO user_tgt;

Grant succeeded.

SQL>
SQL> -- create table
SQL> CONNECT user_src/user_src
Connected.
SQL> CREATE TABLE table_src (ID NUMBER);

Table created.

SQL> INSERT INTO table_src VALUES (1);

1 row created.

SQL> COMMIT;

Commit complete.

SQL>
SQL> -- create db link
SQL> CONNECT user_tgt/user_tgt
Connected.
SQL> CREATE DATABASE LINK dblink_to_src CONNECT TO user_src IDENTIFIED BY user_src USING 'ORCL';

Database link created.

SQL> SELECT * FROM table_src@dblink_to_src;

    ID
----------
     1

SQL>
SQL> -- run impdp
SQL> ! impdp user_tgt/user_tgt tables=user_src.table_src CONTENT=ALL DIRECTORY=DATA_PUMP_DIR NETWORK_LINK=dblink_to_src remap_schema=user_src:user_tgt

Import: Release 12.1.0.2.0 - Production on Sun Jun 14 21:03:05 2015

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
ORA-31631: privileges are required
ORA-39109: Unprivileged users may not operate upon other users' schemas



-- PS: Error message is a bit ironic. I am not operating "upon other users' schemas". I am connected to source as "me" and to target as "me". I want to copy my table from my schema to my schema.


References 



Bug 20834622 - IMPDP REQUIRES UNJUSTIFIED PRIVILEGES TO COPY A TABLE BETWEEN CLIENT SCHEMAS
Registered in April 2015

-- end -- 


Posted by at No comments:

Tuesday, June 2, 2015

binary/textual data insert into BLOB/CLOB columns - use 50% less database (IO) resources

Introduction

There are applications that store binary or textual information in BLOB or CLOB columns.

Often I see that new row with new BLOB/CLOB data is inserted into database using one INSERT statement.

It is not optimal from performance point of view. You could reduce the IO work done by database about 50% if you could use the pattern described below.

Example code below is written in Java but the same logic applies to PL/SQL as well.

Test table structure

CREATE TABLE TESTFILE (ID NUMBER, FILE BLOB)
LOB (FILE) STORE AS SECUREFILE FILE_BLOB (DISABLE STORAGE IN ROW NOCACHE);

Option 1 - regular insert

private void insertWriteBlob(long pkValue, Connection c) throws Exception {
  // prepare blob (NB! this is temporary blob and data is written to temporary space)
  Blob blob = this.c.createBlob();
  writeBlobData(blob);
  // insert row using prepared blob (NB! temporary blob is copied to be permanent by insert)
  PreparedStatement ps = this.c.prepareStatement("insert into testfile values (?, ?)");
  ps.setLong(1, pkValue);
  ps.setBlob(2, blob);
  ps.executeUpdate();
  ps.close();
}
 

Option 2 - 50% cheaper way - write LOB after insert

private void selectForUpdateWriteBlob(long pkValue, Connection c) throws Exception {
  // insert row using empty temporary blob, insert makes it permanent
  PreparedStatement psIns = c.prepareStatement("insert into testfile values (?, ?)");
  psIns.setLong(1, pkValue);
  psIns.setBlob(2, c.createBlob());
  psIns.executeUpdate();
  // select empty permanent blob reference
  PreparedStatement psSel = c.prepareStatement("select file from testfile where id = ? for update");
  psSel.setLong(1, pkValue);
  ResultSet rs = psSel.executeQuery();
  rs.next();
  Blob blob = rs.getBlob(1);
  // write blob data directly to permanent blob
  writeBlobData(blob);
  psIns.close();
  psSel.close();
}

Explanation

Option 1:

  1. First, BLOB object is created before INSERT statement is executed.
    Therefore, the row does not exist at the time of creating     BLOB object.
    Therefore,     BLOB object is created as temporary, it can not be related to table or row yet.
  2. Then, row is INSERTed.
  3. During execution of INSERT the "temporary BLOB" is copied to be part of table (BLOB becomes permanent).
    NB! avoiding that copy operation is the key!

Option 2:

  1. First, row is inserted using empty temporary BLOB. Empty blob is created using createBlob() (java) or empty_blob() (pl/sql).
  2. Then, permanent BLOB pointer is selected.
  3. Then, BLOB data is written directly to permanent BLOB.
    NB! copy is avoided    .

v$sesstat based measurement

10 files were inserted, 100 Mb each.
VALUE_1 is measured for option 1.
VALUE_2 is measured for option 2.


NAME VALUE_1 VALUE_2 diff %
consistent changes 296993 1404 0
dirty buffers inspected 77135 0 0
hot buffers moved to head of LRU 50516 0 0
free buffer inspected 87386 14 0
write clones created in foreground 12 0 0
physical reads cache prefetch 133 0 0
prefetched blocks aged out before use 5 0 0
redo log space wait time 309 0 0
temp space allocated (bytes) 953155584 1048576 0
rollback changes - undo records applied 1 0 0
lob reads 116064 0 0
lob writes 29016 0 0
parse time cpu 1 0 0
parse time elapsed 1 0 0
shared hash latch upgrades - no wait 232813 1404 1
physical read bytes 2473984 24576 1
physical reads cache 302 3 1
physical read total bytes 2473984 24576 1
physical reads 302 3 1
physical read IO requests 169 3 2
physical read total IO requests 169 3 2
db block gets 1501542 58538 4
db block gets from cache 1501542 58538 4
free buffer requested 119422 4188 4
session logical reads 1624877 98528 6
redo log space requests 32 2 6
logical read bytes from cache 13310992384 807141376 6
db block changes 314127 20136 6
consistent gets pin (fastpath) 122604 9692 8
consistent gets pin 122704 9699 8
change write time 9 1 11
messages sent 356 61 17
consistent gets 123335 39990 32
consistent gets from cache 123335 39990 32
enqueue requests 4118 1445 35
enqueue releases 4116 1443 35
recursive calls 1660 773 47
session pga memory 7778408 3715176 48
session pga memory max 7778408 3715176 48
redo buffer allocation retries 2 1 50
non-idle wait count 117896 60207 51
non-idle wait time 1486 886 60
recursive cpu usage 8 5 63
enqueue waits 11 7 64
DB time 2287 1560 68
KTFB apply time (ms) 3051 2482 81
redo synch time (usec) 4759 3866 81
KTFB free time (ms) 561 472 84
Heap Segment Array Updates 7 6 86
CPU used when call started 919 811 88
CPU used by this session 919 811 88
KTFB commit time (ms) 352 312 89
segment chunks allocation from disepnser 718 678 94
segment dispenser allocations 254 247 97
cell physical IO interconnect bytes 979189760 964395008 98
physical write total multi block requests 1148 1126 98
securefile allocation chunks 771 753 98
physical write total bytes 976715776 964370432 99
physical write total IO requests 1462 1447 99
KTFB free req 189 188 99
securefile allocation bytes 865017856 863985664 100
table scan disk non-IMC rows gotten 11299 11345 100
KTFB free space (block) 130344 130216 100
table scan rows gotten 11299 11345 100
session connect time 1429909755 1429910011 100
process last non-idle time 1429909755 1429910011 100
bytes received via SQL*Net from client 946124986 947405272 100
securefile bytes non-transformed 943832448 943890480 100
physical write IO requests 1425 1434 101
index fetch by key 90 91 101
user calls 29071 29341 101
Requests to/from client 29057 29327 101
SQL*Net roundtrips to/from client 29058 29328 101
index scans kdiixs1 96 97 101
calls to get snapshot scn: kcmgss 175576 177339 101
securefile direct write ops 1425 1434 101
segment prealloc tasks 425 435 102
commit cleanouts 56 57 102
commit cleanouts successfully completed 56 57 102
segment cfs allocations 118 122 103
securefile number of non-transformed flushes 234 243 104
immediate (CURRENT) block cleanout applications 23 24 104
KTFB apply req 202 211 104
redo synch time overhead (usec) 3081 3227 105
execute count 217 229 106
parse count (total) 179 191 107
redo write info find 13 14 108
redo synch writes 13 14 108
redo synch time overhead count ( 2ms) 13 14 108
user I/O wait time 413 444 108
opened cursors cumulative 191 207 108
session cursor cache hits 92 99 108
file io wait time 3996654 4353931 109
redo entries 14448 15912 110
KTFB alloc time (ms) 1249 1404 112
redo size 27542172 31738136 115
undo change vector size 12159500 14241556 117
db block gets from cache (fastpath) 480 568 118
no work - consistent read gets 256 307 120
calls to kcmgcs 16208 20456 126
KTFB alloc space (block) 20185088 25821184 128
table scan blocks gotten 162 212 131
segment dispenser load tasks 189 253 134
enqueue conversions 25 34 136
table scans (short tables) 23 33 143
KTFB alloc req 22 33 150
sql area evicted 2 3 150
bytes sent via SQL*Net to client 2335870 3722091 159
switch current to new buffer 30 51 170
segment dispenser load empty 7 12 171
opened cursors current 5 9 180
sorts (memory) 11 20 182
calls to kcmgas 1099 2179 198
commit batch/immediate performed 1 2 200
commit immediate requested 1 2 200
commit batch/immediate requested 1 2 200
transaction rollbacks 1 2 200
cursor authentications 2 4 200
commit immediate performed 1 2 200
CCursor + sql area evicted 3 6 200
parse count (hard) 6 13 217
CR blocks created 512 1405 274
redo subscn max counts 19 54 284
sorts (rows) 4 13 325
application wait time 3 12 400
pinned cursors current 1 5 500
workarea executions - optimal 2 11 550
HSC Heap Segment Block Changes 53 296 558
IMU undo allocation size 792 4824 609
buffer is pinned count 16 259 1619
shared io pool buffer get success 72 1224 1700
consistent gets examination 631 30291 4800
consistent gets examination (fastpath) 587 30291 5160
buffer is not pinned count 255 58865 23084
table fetch by rowid 22 29326 133300
rows fetched via callback 19 29323 154332

Database version

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options 

References 

Open this Oracle doc: "Working with LOBs and BFILEs"
and search for section: "Creating and Populating a BLOB, CLOB or NCLOB Column"

-- end --
Posted by at No comments:
Subscribe to: Posts (Atom)