Oracle Database Discussions

Oracle 19.22

Enterprise. Dont have advanced compression

Create 2 users. Grant them DBA.

Login as USER1.

Create a compress partitioned table in USER1. try and move its partition to nocompress. Works

Create the table in USER2 schema. Repeat the move. Works

Create the table in USER1 schema but this time add it to a flashback area. Disassociate the table from flashback. Move the partition nocompress. Works.

Now repeat in USER2 schema (still logged in as USER1). Create the table, add to flashback. Disassociate. Move. We get insufficient privileges error. <<<<

Login as USER2. Move now works.

Reproducible, multiple environments. You have to be in as the table owner or move wont work. Even as SYS.

Code as follows…

SQL> create user user1 identified by abc;
create user user2 identified by abc;

grant DBA to user1;
grant DBA to user2;
User created.

SQL> 
User created.

SQL> SQL> 
Grant succeeded.

SQL> 

Grant succeeded.

SQL> 
SQL> 
SQL> conn user1/abc
Connected.
SQL> 
SQL> 
SQL> -- as user1 create a partitioned compressed table, insert a row and try and move the partition nocompress
SQL> CREATE TABLE user1.t1
  2  (
  3  COL1 NUMBER ,
  4  COL2 NUMBER NOT NULL,
  5  COL3 DATE
  6  )
  7  COMPRESS BASIC
  8  PARTITION BY RANGE (COL3)
  9   INTERVAL (NUMTODSINTERVAL(1,'DAY'))
 10   (
 11   PARTITION PART_01 VALUES LESS THAN (TO_DATE(' 2019-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
 12   COMPRESS BASIC
 13   )
 14   ;

Table created.

SQL> insert into user1.t1 (col3,col2) values(trunc(sysdate),1);

1 row created.

SQL> alter table user1.t1 move partition part_01 nocompress;

Table altered.

SQL> show user
USER is "USER1"
SQL> 
SQL> 
SQL> CREATE TABLE user2.t2
  2  (
  3  COL1 NUMBER ,
  4  COL2 NUMBER NOT NULL,
  5  COL3 DATE
  6  )
  7  COMPRESS BASIC
  8  PARTITION BY RANGE (COL3)
  9   INTERVAL (NUMTODSINTERVAL(1,'DAY'))
 10   (
 11   PARTITION PART_01 VALUES LESS THAN (TO_DATE(' 2019-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
 12   COMPRESS BASIC
 13   )
 14   ;

Table created.

SQL> insert into user2.t2 (col3,col2) values(trunc(sysdate),1);

1 row created.

SQL> commit;

Commit complete.

SQL> alter table user2.t2 move partition part_01 nocompress;

Table altered.




-- ok all good to here.  Now create t3 and put in user1 schema and add to an FDA we already have called fda_7d.


SQL> 
SQL> CREATE TABLE user1.t3
  2  (
  3  COL1 NUMBER ,
  4  COL2 NUMBER NOT NULL,
  5  COL3 DATE
  6  )
  7  COMPRESS BASIC
  8  PARTITION BY RANGE (COL3)
  9   INTERVAL (NUMTODSINTERVAL(1,'DAY'))
 10   (
 11   PARTITION PART_01 VALUES LESS THAN (TO_DATE(' 2019-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
 12   COMPRESS BASIC
 13   )
 14   flashback archive fda_7d
 15   ;

Table created.

SQL> insert into user1.t3 (col3,col2) values(trunc(sysdate),1);

1 row created.

SQL> commit;

Commit complete.

-- if we try the move without disassociate first we get an error
SQL> 
SQL> alter table user1.t3 move partition part_01 nocompress;
alter table user1.t3 move partition part_01 nocompress
*
ERROR at line 1:
ORA-55610: Invalid DDL statement on history-tracked table


-- so disassociate
SQL> EXEC DBMS_FLASHBACK_ARCHIVE.DISASSOCIATE_FBA ( OWNER_NAME => 'USER1', TABLE_NAME => 'T3');

PL/SQL procedure successfully completed.


-- and now the move works
SQL> alter table user1.t3 move partition part_01 nocompress;

Table altered.

SQL> EXEC DBMS_FLASHBACK_ARCHIVE.REASSOCIATE_FBA ( OWNER_NAME => 'USER1', TABLE_NAME => 'T3');

PL/SQL procedure successfully completed.

SQL> 


-- now create T4 in user2 schema and add to FDA
SQL> CREATE TABLE user2.t4
  2  (
  3  COL1 NUMBER ,
  4  COL2 NUMBER NOT NULL,
  5  COL3 DATE
  6  )
  7  COMPRESS BASIC
  8  PARTITION BY RANGE (COL3)
  9   INTERVAL (NUMTODSINTERVAL(1,'DAY'))
 10   (
 11   PARTITION PART_01 VALUES LESS THAN (TO_DATE(' 2019-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
 12   COMPRESS BASIC
 13   )
 14    flashback archive fda_7d
 15   ;

Table created.

SQL> 
SQL> insert into user2.t4 (col3,col2) values(trunc(sysdate),1);

1 row created.

SQL> commit;

Commit complete.

SQL> EXEC DBMS_FLASHBACK_ARCHIVE.DISASSOCIATE_FBA ( OWNER_NAME => 'USER2', TABLE_NAME => 'T4');

PL/SQL procedure successfully completed.


--- Now try and move the partition but we get the following error

SQL>  alter table user2.t4 move partition part_01 nocompress;
 alter table user2.t4 move partition part_01 nocompress
*
ERROR at line 1:
ORA-01031: insufficient privileges


SQL> 
SQL> 
SQL> 

-- and login as USER2 and try it and it works.
SQL> conn user2/abc
Connected.
SQL> alter table user2.t4 move partition part_01 nocompress;

Table altered.

SQL> 

Anyone spot anything obvious Im missing? not that this will come up so often but we have a lot of partitioned tables in FDA. Small pain having to login as owner each time rather than as DBA