Saturday, September 24, 2016

Datapump Does not Export Permissions on Objects Owned by SYS

This post was sponsored by Technimove 

I was reminded recently that Datapump does not export permissions on objects owned by SYS so I decided to write a post about it for my blog. It was tested on an Oracle 11.2.0.1 database. First I created a user called USER1:

SQL> conn / as sysdba
Connected.
SQL> create user user1
  2  identified by user1
  3  /

User created.

SQL> grant create session to user1
  2  /

Grant succeeded.

SQL>


I logged in as USER1 and showed that it did not have execute permission on SYS.DBMS_LOCK:

SQL> conn user1/user1
Connected.
SQL> exec dbms_lock.sleep(1);
BEGIN dbms_lock.sleep(1); END;

      *
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'DBMS_LOCK' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

SQL>
 

I granted permission then logged in as USER1 again to check it had worked:

SQL> conn / as sysdba
Connected.
SQL> grant execute on dbms_lock to user1
  2  /

Grant succeeded.

SQL> conn user1/user1
Connected.
SQL> exec dbms_lock.sleep(1);

PL/SQL procedure successfully completed.

SQL>


I used expdp to export the schema with the following parameters:

content=all
directory=andrews_datapump_dir
dumpfile=andrew.dmp
logfile=andrew_exp.log
schemas=user1


I used impdp to import the dumpfile into a different schema with the following parameters:

content=all
directory=andrews_datapump_dir
dumpfile=andrew.dmp
logfile=andrew_imp.log
remap_schema=user1:user2
 

…but when I logged in as USER2, it did not have execute permission on SYS.DBMS_LOCK:

SQL> conn user2/user1
Connected.
SQL> exec dbms_lock.sleep(1);
BEGIN dbms_lock.sleep(1); END;

      *
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'DBMS_LOCK' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

SQL>

Sunday, September 04, 2016

Deferred Segment Creation not Supported for Partitioned Tables in Oracle 11.2.0.1


This post was sponsored by IMPERVA

I tried to create a partitioned table with deferred segment creation in an Oracle 11.2.0.1 database.

First I tried to do so explicitly but this did not work:

SQL> create table partitioned_table
  2  (refno number)
  3  segment creation deferred
  4  partition by range (refno)
  5  (partition partition1 values less than (10)
  6   tablespace users,
  7   partition partition2 values less than (maxvalue)
  8   tablespace users)
  9  /
create table partitioned_table
*
ERROR at line 1:
ORA-14223: Deferred segment creation is not supported
for this table

SQL>

Then I tried to set the appropriate parameter at session level but when I created a partitioned table, I found that it had 2 segments:

SQL> alter session set deferred_segment_creation = true
  2  /

Session altered.

SQL> create table partitioned_table
  2  (refno number)
  3  partition by range (refno)
  4  (partition partition1 values less than (10)
  5   tablespace users,
  6   partition partition2 values less than (maxvalue)
  7   tablespace users)
  8  /

Table created.

SQL> select count(*) from dba_segments
  2  where segment_name = 'PARTITIONED_TABLE'
  3  /

  COUNT(*)
----------
         2

SQL>

However, when I logged in to an Oracle 11.2.0.4 database, I found that I was able to create a partitioned table with deferred segment creation. (I understand that this was introduced in Oracle 11.2.0.2 but have no database to check this on):

SQL> create table partitioned_table
  2  (refno number)
  3  segment creation deferred
  4  partition by range (refno)
  5  (partition partition1 values less than (10)
  6   tablespace users,
  7   partition partition2 values less than (maxvalue)
  8   tablespace users)
  9  /

Table created.

SQL>

As you would expect, the table had no segments:

SQL> select count(*) from dba_segments
  2  where segment_name = 'PARTITIONED_TABLE'
  3  /

  COUNT(*)
----------
         0

SQL>

...and, as I added data, partitions were only created when they were actually needed:

SQL> insert into partitioned_table values (1)
  2  /

1 row created.

SQL> select count(*) from dba_segments
  2  where segment_name = 'PARTITIONED_TABLE'
  3  /

  COUNT(*)
----------
         1

SQL> insert into partitioned_table values (10)
  2  /

1 row created.

SQL> select count(*) from dba_segments
  2  where segment_name = 'PARTITIONED_TABLE'
  3  /

  COUNT(*)
----------
         2

SQL>