RSS Feed for This PostCurrent Article

Oracle Notes


ORA-14402: updating partition key column would cause a partition change

Solution: Run

alter table <table name>
enable row movement

Reference

Locking or Unlocking Statistics
Use the following procedures to lock and unlock statistics on objects.

LOCK_SCHEMA_STATS Procedure
LOCK_TABLE_STATS Procedure

UNLOCK_SCHEMA_STATS Procedure
UNLOCK_TABLE_STATS Procedure

Reference

In Oracle 10g the DBMS_JOB package is replaced by the DBMS_SCHEDULER package. The DBMS_JOB package is now deprecated and in Oracle 10g it’s only provided for backward compatibility. From Oracle 10g the DBMS_JOB package should not be used any more, because is could not exist in a future version of Oracle.

With DBMS_SCHEDULER Oracle procedures and functions can be executed. Also binary and shell-scripts can be scheduled.

Reference

Estimate table row and index size

/* TABLE */

select
3 + avg(nvl(dbms_lob.getlength(CASE_DATA),0)+1 +
nvl(vsize(CASE_NUMBER ),0)+1 +
nvl(vsize(CASE_DATA_NAME),0)+1 +
nvl(vsize(LASTMOD_TIME_T),0)+1
) “Total bytes per row”
from
arch_case_data
where
case_number = 301;

Total bytes per row
——————–
3424

/* INDEX */

select sum(COLUMN_LENGTH)
from dba_ind_columns
where TABLE_NAME = ‘ARCH_CASE_DATA’;

SUM(COLUMN_LENGTH)
————————
22

Analyze Schema Statistics

connect &&3;
define schema_name=&&1;
set echo on timing on time on termout on; spool &&2; exec DBMS_STATS.UNLOCK_SCHEMA_STATS (‘&&schema_name’); exec dbms_stats.gather_schema_stats(‘&&schema_name’,options=>’GATHER’,estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt=>’FOR ALL COLUMNS SIZE AUTO’,cascade=>TRUE); spool off; exit;

ORA-02303: cannot drop or replace a type with type or table dependents

Fix for the ORA-02303 error is to drop the dependent objects first before trying to remove or replace the parent object type. The following queries can be used to find dependent object types:

— Find nested table dependencies
select owner, parent_table_name, parent_table_column
from dba_nested_tables
where (table_type_owner, table_type_name) in
(select owner, type_name
from dba_coll_types
where elem_type_owner = ‘‘
and elem_type_name = ‘‘);
— Find VARRAY dependencies
select owner, parent_table_name, parent_table_column
from dba_varrays
where (type_owner, type_name) in
(select owner, type_name
from dba_coll_types
where elem_type_owner = ‘‘
and elem_type_name = ‘