How to solve ORA-00069: cannot acquire lock -- table locks disabled

Unfortunately, the only known way out of this is to

1. stop the database
2. start in restricted mode with "startup restrict"
3. enable table locks:

 alter table scott.emp enable table lock;

4. startup the database in normal mode, and you will now be able to aquire locks on the table.

How to fix ORA-02449: unique/primary keys in table referenced by foreign keys after an online redefinition

When attempting to drop the interim table after a successful online redefinition, you may get:

ORA-02449: unique/primary keys in table referenced by foreign keys

This is easy to overlook - the online redef procedure does not disable the foreign key relationship from other tables to your (now) obsolete interim table.

To find these tables and their constraints:

SELECT  TABLE_NAME,CONSTRAINT_NAME,CONSTRAINT_TYPE,R_CONSTRAINT_NAME,STATUS
FROM    DBA_CONSTRAINTS
WHERE   OWNER = 'SH'
AND     CONSTRAINT_TYPE='R'
AND     R_CONSTRAINT_NAME IN (SELECT CONSTRAINT_NAME FROM DBA_CONSTRAINTS WHERE TABLE_NAME='SALES_INTERIM')

TABLE_NAME	CONSTRAINT_NAME	CONSTRAINT_TYPE	R_CONSTRAINT_NAME	STATUS
INVOICES	TMP$$_INVOICES_FK0	R	TMP$$_SALES_PK0	DISABLED
REFUNDS	TMP$$_REFUNDS_FK0	R	TMP$$_SALES_PK0	DISABLED
REPLENISHMENT	TMP$$_REPLENISHMENT_FK0	R	TMP$$_SALES_PK0	DISABLED

As can be seen from the table above, there are certainly constraints from other tables, pointing to the primary key on the interim table. They are disabled, but nevertheless preventing us from dropping the table.

Let's make sure that each of these "funny named" constraints have a sibling constraint, one that is named correctly after the redefintion:

SELECT TABLE_NAME,CONSTRAINT_NAME,R_CONSTRAINT_NAME,STATUS
FROM DBA_CONSTRAINTS 
WHERE TABLE_NAME in (
                    SELECT  TABLE_NAME
                    FROM    DBA_CONSTRAINTS
                    WHERE   OWNER = 'Sh'
                    AND     CONSTRAINT_TYPE='R'
                    AND     R_CONSTRAINT_NAME IN (SELECT CONSTRAINT_NAME FROM DBA_CONSTRAINTS WHERE TABLE_NAME='SALES_INTERIM')
)
AND CONSTRAINT_TYPE='R'
AND CONSTRAINT_NAME LIKE '%REPLENISHMENT%' OR CONSTRAINT_NAME LIKE '%INVOICES%' OR CONSTRAINT_NAME LIKE '%REFUNDS%'
ORDER BY TABLE_NAME,CONSTRAINT_NAME;

Result:

TABLE_NAME	CONSTRAINT_NAME	R_CONSTRAINT_NAME	STATUS
INVOICES	INVOICES_FK	SALES_PK	ENABLED
INVOICES	TMP$$_INVOICES_FK0	TMP$$_SALES_PK0	DISABLED
REFUNDS	REFUNDS_FK	SALES_PK	ENABLED
REFUNDS	TMP$$_REFUNDS_FK0	TMP$$_SALES_PK0	DISABLED
REPLENISHMENT	REPLENISHMENT_FK	SALES_PK	ENABLED
REPLENISHMENT	TMP$$_REPLENISHMENT_FK0	TMP$$_SALES_PK0	DISABLED

As you can see, the constraints come in pairs and the ones with names starting with TMP$$ are leftovers from the redefinition.

You can safely drop these constraints. Put the following in a .sql script and run it as sysdba:

alter session set nls_language='american';
set lines 200
set pages 0
set heading off
set feedback off
set trimspool on
set verify off
set echo off
spool 7.alter_table.sql

select 'spool 7.alter_table.log' from dual;

SELECT    'alter table '
        || B.OWNER
        || '.'
        || B.TABLE_NAME
        || ' drop constraint '
        || B.CONSTRAINT_NAME
        || ';'
  FROM DBA_CONSTRAINTS A
        FULL OUTER JOIN DBA_CONSTRAINTS B
           ON A.CONSTRAINT_NAME = B.R_CONSTRAINT_NAME
  WHERE A.OWNER = 'SH'
  AND A.TABLE_NAME='SALES_INTERIM'
  AND B.R_CONSTRAINT_NAME IS NOT NULL;
select 'exit' from dual;

exit

Run the script, and you will have a new script containing the drop-clauses:

alter table SH.INVOICES drop constraint TMP$$_INVOICES_FK0;
alter table SH.REFUNDS drop constraint TMP$$_REFUNDS_FK0;
alter table SH.REPLENISHMENT drop constraint TMP$$_REPLENISHMENT_FK0;

After these statements have been successfully executed, you can go ahead and drop the interim table.

How to solve ORA-14024 during execution of DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS

Background: you are redefining a table and have successfully completed the DBMS_REDEFINITION.START_REDEF_TABLE procedure.

As you are executing the next step, DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS, you receive the following error:

ERROR at line 1:
ORA-14024: number of partitions of LOCAL index must equal that of the underlying table

Cause: Your interim table has been created with more partitions than the original table you're trying to redefine.

Verify like this:

select 'original table: ' || MAX(PARTITION_POSITION) "number of partitions"
from dba_tab_partitions where table_name='SALES' 
union
select 'interim table: ' || MAX(PARTITION_POSITION) 
from dba_tab_partitions where table_name='SALES_INTERIM' 
;

number of partitions
interim table: 19
original table: 18

A simple solution here is avoiding to create the indexes as a part of the COPY_TABLE_DEPENDENTS, and create them afterwards instead.

Simply change the directive

copy_indexes=>dbms_redefinition.cons_orig_params

to

copy_indexes=>0

when you execute DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS.

When you're done redefining your table, make sure you recreate the indexes.

If this is not acceptable, you need to add another partition to your original table, so that the missing index partitions will be automatically created.


Update 21.06.2021: I faced a similar challenge today as I was trying to redefine a table with interval range partitioning. As I was testing my redefinition strategy in a test environment first, the table in question had 46 partitions with zero rows. This fact raised ORA-14024: number of partitions of LOCAL index must equal that of the underlying table, and I realized I could quite easily work around the problem by simply dropping the empty partitions. To be safe you drop any partitions that actually had rows in them, analyzed them first:

set lines 200
spool analyze_part.sql
set   trimspool on
set   verify off
set   heading off
set   pages 0
set   echo off
set   feedback off
select 'alter session set nls_language=''american'';' from dual;
select 'set timing on' from dual;
select 'set lines 200' from dual;
select 'set trimspool on' from dual;
select 'spool analyze_part.log' from dual;
select 'exec DBMS_STATS.GATHER_TABLE_STATS (OwnName=> ''TRACKER'', TabName => ''EVENTS'',partname =>''' || partition_name || ''' granularity => ''PARTITION'';' 
from dba_tab_partitions
where table_name='EVENTS'
and num_rows = 0
and partition_name like 'SYS%';

Run the analyze-script:

sqlplus / as sysdba @analyze_part.sql

Afterwards, the num_rows column for the same partitions should show 0 (zero) rows. In other words, they can be safely dropped.

To generate a "drop partition" script, put the following in an sql-script:

set lines 200
spool drop_empty_part.sql
set   trimspool on
set   verify off
set   heading off
set   pages 0
set   echo off
set   feedback off
select 'alter session set nls_language=''american'';' from dual;
select 'set timing on' from dual;
select 'set lines 200' from dual;
select 'set trimspool on' from dual;
select 'spool drop_empty_part.log' from dual;

select 'ALTER TABLE ' || table_owner || '.' || table_name ||  ' DROP PARTITION ' || PARTITION_NAME ||  ' UPDATE INDEXES;'
from dba_tab_partitions
where table_name='EVENTS'
and num_rows = 0
and partition_name like 'SYS%';
select 'exit' from dual;
exit

Execut the script:

sqlplus / as sysdba @drop_empty_part.sql

The number of partitions in the original table and the interim table should now match. Now, execute the DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS again. You shouldn't see the ORA-14024 this time around :-)

How to move data using Transportable Tablespaces

Mission:

* Move schema FXMART with all of its data from source database proddb01 to destination database testdb01
* In the same operation, rename schema FXMART with FXMARTST in the destination database testdb01.

While there are certainly a number of ways to accomplish such a task, I will show you how to use transportable tablespaces.

1. In your source database, check which tablespaces your schema is writing to:

SELECT UNIQUE TABLESPACE_NAME
FROM DBA_SEGMENTS
WHERE OWNER = 'FXMART';

TABLESPACE_NAME
FXDATA_2016
FX_MART_DATA
FXDATA_2014
FXDATA_2015

2. In your source database, check that these are self-contained:

exec dbms_tts.transport_set_check('FXDATA_2016,FX_MART_DATA,FXDATA_2014,FXDATA_2015', true);

Check errors using the view TRANSPORT_SET_VIOLATIONS:

SELECT * FROM TRANSPORT_SET_VIOLATIONS;

If you see no rows here, proceed to step 3.

3. In your source database, set the tablespaces to be transported to read only:

alter tablespace FXDATA_2016 read only;
alter tablespace FX_MART_DATA read only;
alter tablespace FXDATA_2014 read only;
alter tablespace FXDATA_2015 read only;

4. Make sure you actually have a directory object called "DP". If not, create it:

CREATE OR REPLACE DIRECTORY DP AS '/u02/exports';

5. In your source database, export the metadata for the tablespaces to be moved using data pump export.

First, create a parameter file as follows:

userid='/ as sysdba'
DIRECTORY=DP
DUMPFILE=FXMOVE_TSS.dmp
LOGFILE=FXMOVE_TSS.log
JOB_NAME=FXMOVE
TRANSPORT_TABLESPACES=FXDATA_2016,FX_MART_DATA,FXDATA_2014,FXDATA_2015
EXCLUDE=STATISTICS
METRICS=YES

Then, execute the export:

expdp parfile=expdp_tts.par

6. Identify the data files which belong to the tablespaces to be transported.

To identify the files, use this simple query:

SELECT FILE_NAME
FROM DBA_DATA_FILES
WHERE TABLESPACE_NAME IN (
    SELECT UNIQUE TABLESPACE_NAME
    FROM DBA_SEGMENTS
    WHERE OWNER = 'FXMART'
  );

FILE_NAME
/u02/oradata/proddb01/fx_mart_data.dbf
/u02/oradata/proddb01/fxdata_2014.dbf
/u02/oradata/proddb01/fxdata_2015.dbf
/u02/oradata/proddb01/fxdata_2016.dbf

7. Transport the resulting dmpfile along with the datafiles of the tablespaces to be transported, over the network to the destination server, using scp

scp -i /home/oracle/.ssh/destinationserver_rsa /u02/exports/FXMOVE_TSS.dmp oracle@destinationserver:/u02/exports/.
scp -i /home/oracle/.ssh/destinationserver_rsa /u02/exports/fxdata_2016.dbf oracle@destinationserver:/u02/exports/.
scp -i /home/oracle/.ssh/destinationserver_rsa /u02/exports/fxdata_2015.dbf oracle@destinationserver:/u02/exports/
scp -i /home/oracle/.ssh/destinationserver_rsa /u02/exports/fxdata_2014.dbf oracle@destinationserver:/u02/exports/
scp -i /home/oracle/.ssh/destinationserver_rsa /u02/exports/fx_mart_data.dbf oracle@destinationserver:/u02/exports/

When the scp session is over, set the tablespaces back to read/write to allow normal usage in the source database:

alter tablespace FXDATA_2016 read write;
alter tablespace FX_MART_DATA read write;
alter tablespace FXDATA_2014 read write;
alter tablespace FXDATA_2015 read write;

8. In the destination database, create a directory object that points to the location where the datafiles and the dump file now resides:

CREATE OR REPLACE DIRECTORY DP AS '/u02/exports';

9. In the destination database, drop the tablespaces to be replaced:

drop tablespace FXDATA_2016 including contents and datafiles;
drop tablespace FXDATA_2014 including contents and datafiles;
drop tablespace FX_MART_DATA including contents and datafiles;
drop tablespace FXDATA_2015 including contents and datafiles;

10. In the destination database, drop and recreate the schema to be repopulated with fresh data. Note that the user's default tablepace need to be temporarily set to USERS, as the tablespaces we intend to use as default tablespace is not yet imported:

drop user FXMARTTST cascade;

CREATE USER FXMARTTST
  IDENTIFIED BY mysecretpassword
  DEFAULT TABLESPACE USERS 
  TEMPORARY TABLESPACE TEMP
  PROFILE APP_SCHEMAS;

-- Grant of system privileges and/or roles here

11. On the destination server, create an import parameter file as follows:

userid='/ as sysdba'
DIRECTORY=DP
DUMPFILE=FXMOVE_TSS.dmp
LOGFILE=imp_FXMOVE_TSS.log
JOB_NAME=FXMOVE
TRANSPORT_DATAFILES=/u02/oradata/testdb01/fx_mart_data.dbf,/u02/oradata/testdb01/fxdata_2014.dbf,/u02/oradata/testdb01/fxdata_2015.dbf,/u02/oradata/testdb01/fxdata_2016.dbf
REMAP_SCHEMA=FXMART:FXMARTTST
METRICS=YES

12. In the destination database, make the freshly imported tablepaces read write:

alter tablespace FXDATA_2016 read write;
alter tablespace FX_MART_DATA read write;
alter tablespace FXDATA_2014 read write;
alter tablespace FXDATA_2015 read write;

All done. You should now be able to logon to the database using FXMARTST and use the refreshed data.

What is the meaning of INCREMENTAL_STALENESS in dbms_stats?

INCREMENTAL_STALENESS means "how we decide if a partition's statistics are stale".

Its values can be either USE_STALE_PERCENT or USE_LOCKED_STATS:

USE_STALE_PERCENT - a partition/subpartition is not considered as stale if DML changes are less than the
STALE_PERCENT preference value.

USE_LOCKED_STATS - locked partitions/subpartitions statistics are not considered as stale, regardless of DML changes

NULL - this is the default value, meaning a partition or subpartition is considered as stale as long as it has any DML changes

STALE_PERCENT is the value that determine the percentage of rows in a table that have to change stats before the statistics are deemed stale and should be regathered. Default = 10%


I have used the following script to implement this in one of my 12.2 database.
The last preference (DEGREE) set has nothing to do with incremental statistics, but I have found that using the default degree of parallelism is a good setting for most databases

define owner = 'SCOTT';
define table_name = 'EMP';
BEGIN
   dbms_stats.set_table_prefs('&&owner','&&table_name','INCREMENTAL','TRUE');
   dbms_stats.set_table_prefs('&&owner','&&table_name','INCREMENTAL_STALENESS','USE_STALE_PERCENT, USE_LOCKED_STATS');
   dbms_stats.set_table_prefs('&&owner','&&table_name','DEGREE','DBMS_STATS.DEFAULT_DEGREE');
END;
/

Verfiy your settings with the following script::

 
define owner = 'SCOTT';
define table_name = 'EMP';
set lines 200
set verify off
col "incremental" format a20
col "incr staleness" format a40
col "incremental level" format a10
col "stale percent" format a20
col "synopsis type" format a40
col "degree" format a30
col "granularity" format a20

select dbms_stats.get_prefs('INCREMENTAL','&&owner','&&table_name') "incremental",
        dbms_stats.get_prefs('INCREMENTAL_STALENESS','&&owner','&&table_name') "incr staleness",
        dbms_stats.get_prefs('INCREMENTAL_LEVEL','&&owner','&&table_name') "incremental level",
        dbms_stats.get_prefs('STALE_PERCENT','&&owner','&&table_name') "stale percent",
        dbms_stats.get_prefs('APPROXIMATE_NDV_ALGORITHM','&&owner','&&table_name') "synopsis type",
        dbms_stats.get_prefs('DEGREE','&&owner','&&table_name') "degree" ,
        dbms_stats.get_prefs('GRANULARITY','&&owner','&&table_name') "granularity"
FROM DUAL
/

Example output:

incremental	incr staleness	incremental level	stale percent	synopsis type	degree	granularity
TRUE	USE_STALE_PERCENT, USE_LOCKED_STATS	PARTITION	10	REPEAT OR HYPERLOGLOG	32	AUTO

Source: "Understanding Optimizer Statistics With Oracle Database 12c Release 2"

How to solve ORA-55622 during DROP USER ... CASCADE;

During a DROP USER ... CASCADE operation, you may encounter the following error:

ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-55622: DML, ALTER and CREATE UNIQUE INDEX operations are not allowed on table "SCOTT"."SYS_FBA_TCRV_83287"

Solution: disable flashback archive:

Check the currently enabled flashback archive tables:

set lines 155
col TABLE_NAME for a30
col OWNER_NAME for a30
col FLASHBACK_ARCHIVE_NAME for a30
col ARCHIVE_TABLE_NAME for a30
col STATUS for a30
SELECT * 
FROM DBA_FLASHBACK_ARCHIVE_TABLES 
WHERE OWNER_NAME=upper('SCOTT');

Put the following in a script called gen_disable_fda.sql:

set trimspool on
set heading off
set feedback off
set echo off
set verify off
spool disable_flb_archive.sql
SELECT 'spool disable_flb_archive.log' FROM dual;
SELECT 'alter table ' || owner_name || '.' || table_name || ' no flashback archive;' 
FROM DBA_FLASHBACK_ARCHIVE_TABLES 
WHERE OWNER_NAME=upper('SCOTT');
SELECT 'exit' FROM dual;
exit

The script above will generate alter table statements for all SCOTT-owned tables with flashback archive enabled:

sqlplus / as sysdba @gen_disable_fda.sql

Finally, run the script disable_flb_archive.sql, which now contain statements as shown below:

alter table SCOTT.EMP no flashback archive;

After this has been done, the schema may be dropped.

Display the current locks in a tree-like fashion

Oracle provides some built-in scripts and views that can be used to monitor locks in the database


http://docs.oracle.com/database/121/ADMIN/monitoring.htm#ADMIN11255

To illustrate this, I will open a session that updates a table, then create another session that updates the same table:

Session# 1:

select SYS_CONTEXT('userenv','con_name') "container name",
       SYS_CONTEXT('userenv','con_id') "container id",
       SYS_CONTEXT('userenv','CURRENT_SCHEMA') "Current schema",
       SYS_CONTEXT('userenv','SID') "SID"
FROM DUAL

container name       container id         Current schema                 SID
-------------------- -------------------- ------------------------------ -----
VEGDB01              3                    SCOTT                          362

--Update a the table rows:
 update emp set sal=1000;

14 rows updated.

I will keep the session alive, without doing commit/rollback.

From session# 2:

select SYS_CONTEXT('userenv','con_name') "container name",
        SYS_CONTEXT('userenv','con_id') "container id",
        SYS_CONTEXT('userenv','CURRENT_SCHEMA') "Current schema",
        SYS_CONTEXT('userenv','SID') "SID"
FROM DUAL;

container name       container id         Current schema                 SID
-------------------- -------------------- ------------------------------ -----
VEGDB01              3                    SCOTT                          130

--Update the same rows as in session# 1:
update emp set sal=2000;

This session will not give you the prompt back, as it is trying to aquire a lock on the table rows.

I then ran the utllockt.sql script:

sqlplus / as sysdba @$ORACLE_HOME/rdbms/admin/utllockt.sql

Output (abbreviated):

WAITING_SESSION   LOCK_TYPE         MODE_REQUESTED  MODE_HELD     LOCK_ID1          LOCK_ID2
----------------- ----------------- --------------- ------------ ----------------- -----------------
362               None                
   130            Transaction       Exclusive       Exclusive     524308             17836

The leftmost session (362) is blocking the sessions listed underneath it.

Let's check what the session with SID = 130 is doing:

SELECT sess.sid,sess.serial#,sess.sql_id,s.plan_hash_value, s.child_number,sess.client_info,substr(sql_text,1,30) "sql text",sess.program,sess.pq_status,
        decode(sess.blocking_session_status,'VALID','blocked', 'NO HOLDER','Not blocked') "blocked?",
        sess.blocking_session "blocking session SID",sess.event
FROM V$SESSION sess inner join v$sql s 
on (sess.sql_id = s.sql_id)
WHERE sess.SID IN (130)
and sess.sql_child_number = s.child_number;

This query confirms the output from the Oracle supplied utllockt.sql, and reveals what the blocking session is trying to do, in this case, to execute DML on table rows as that are already being locked for update:

SID	SERIAL#	SQL_ID	PLAN_HASH_VALUE	sql text	PROGRAM	PQ_STATUS	blocked?	blocking session SID	EVENT
130	9755	1gpj28ptjj43p	1494045816	update emp set sal=2000	sqlplus@	ENABLED	blocked	362	enq: TX - row lock contention