Skip to Main Content

Breadcrumb

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

Question and Answer

Tom Kyte

Thanks for the question, Rupali.

Asked: October 11, 2000 - 3:03 am UTC

Answered by: Tom Kyte - Last updated: November 29, 2019 - 4:46 am UTC

Category: Database - Version: 8.1.5

Viewed 100+ times

Whilst you are here, check out some content from the AskTom team: PL/SQL 101: Raising exceptions in PL/SQL

You Asked

Dear Mr. Tom,

When we issue a DML it undergoes
PARSE
EXECUTE
FETCH process and the things happen in Database Buffer Cache.

Kindly tell me when we issue a DDL then
PARSE
EXECUTE is understandable but
what about FETCH?
What it will Fetch or the Fetch will not occur at all?

2nd thing is where that definition will be available or will it be immediately commited with the implicit commit and sent to database directly???

Rupali

and we said...

DDL won't "fetch". It may generate recursive SQL that does perform fetches.

DDL is performed in the following way:

o a commit is issued
o the DDL is performed
o a commit is issued

Therefore, the outcome of a DDL command will be immediately visible to all sessions.



and you rated our response

  (11 ratings)

Is this answer out of date? If it is, please let us know via a Review

Reviews

can we just parse ddl?

May 21, 2003 - 12:32 pm UTC

Reviewer: Gabriel from Montreal, Canada

Hello Tom,
Can you please tell me if there is a way to only parse ddl without executing it. My need comes from the fact that one of our prod envs has very outdated testing envs and there is absolutely no time to bring these testing envs in syns with prod.
I would like to parse long ddl scripts without executing them in the prod environment. I know that dbms_sql's parse procedure also executes. Is there any other way?

Thank you very much

Tom Kyte

Followup  

May 21, 2003 - 2:57 pm UTC

parsing ddl executes it, no if and or buts about it. true in all environments (not just dbms_sql)

Then why the distinction

May 21, 2003 - 3:22 pm UTC

Reviewer: Gabriel from Monteral, Canada

If one is the other then why do we have a distinction between the parsing step and the execution step. True they don't do the same thing but they are the same thing. Instead of spliting in all the books the ddl execution in two steps parsing/execution we should have just one, I think.

Tom Kyte

Followup  

May 21, 2003 - 4:10 pm UTC

what book?

Quick question

May 23, 2003 - 6:40 am UTC

Reviewer: Karl from Fiji

<quote>
DDL is performed in the following way:

o a commit is issued
o the DDL is performed
o a commit is issued
</quote>

I understand that if session 1 is querying a table, and session 2 tries to alter it, session 2 will block on a data dictionary lock until session 1's query completes.

What if session 1 starts a serializable transaction, queries the table, then session 2 alters the table, then session 1 queries the table again. Will it see the table alter or not altered, or get "can't serialize this transaction".

Thanks and apologies if you can't understand the question.

Tom Kyte

Followup  

May 23, 2003 - 8:56 am UTC

ops$tkyte@ORA817DEV> create table t ( x int );
ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> set transaction isolation level serializable;
ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> insert into t select rownum from all_users where rownum < 5;
ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> variable x refcursor
ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> begin
  2    open :x for select * from t;
  3  end;
  4  /
ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> declare
  2          pragma autonomous_transaction;
  3  begin
  4          execute immediate 'alter table t add y number default 55';
  5  end;
  6  /
declare
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified
ORA-06512: at line 4


ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> print x

         X
----------
         1
         2
         3
         4
ops$tkyte@ORA817DEV> 

Tried it out myself

May 23, 2003 - 6:53 am UTC

Reviewer: Karl from Fiji

I forgot I could answer my own question!

Check out these interesting results... 

==== session 1 ====
SQL> set transaction isolation level serializable;

Transaction set.

SQL> select * from test;

         X          Y          Z
---------- ---------- ----------
         1          2          0
         2          4          0
         3          6          0
         4          8          0

==== session 2 ====
SQL>alter table test add (q number default 1);

Table altered.

==== session 1 ====
SQL> select * from test;

         X          Y          Z          Q
---------- ---------- ---------- ----------
         1          2          0
         2          4          0
         3          6          0
         4          8          0

SQL> commit;

Commit complete.

SQL> select * from test;

         X          Y          Z          Q
---------- ---------- ---------- ----------
         1          2          0          1
         2          4          0          1
         3          6          0          1
         4          8          0          1

So the changes to the table structure in the data dictionary appear in session 1, but the value of the column doesn't appear until after the transaction ends. Could you explain how this is happening? Thanks Tom. 

Tom Kyte

Followup  

May 23, 2003 - 9:05 am UTC

the above example shows what happens in 8i, in 9i, this is what happens:

ps$tkyte@ORA920> create table t ( x ) as select rownum from all_users where rownum < 5;

Table created.

ops$tkyte@ORA920> set transaction isolation level serializable;

Transaction set.

ops$tkyte@ORA920> select * from t;

         X
----------
         1
         2
         3
         4

ops$tkyte@ORA920> declare
  2          pragma autonomous_transaction;
  3  begin
  4          execute immediate 'alter table t add y number default 55';
  5  end;
  6  /

PL/SQL procedure successfully completed.

ops$tkyte@ORA920> select * from t;

         X          Y
---------- ----------
         1
         2
         3
         4

ops$tkyte@ORA920> commit;

Commit complete.

ops$tkyte@ORA920> select * from t;

         X          Y
---------- ----------
         1         55
         2         55
         3         55
         4         55



the reason Y is "NULL" until we commit is because the alter table really does an "update t set y = 55" (sql_trace will show you that).  We cannot see the effects of updates in a serializable transaction that happened AFTER our transaction begin -- we have to see dictionary updates (SYS -- recursive -- SQL is never done in "flashback" style) so we see the column -- but not the values therein 

Clarification

May 23, 2003 - 9:24 am UTC

Reviewer: Karl from Fiji

Sorry Tom, did you make a typo? I am running 9i.

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.2.0 - 64bit Production
PL/SQL Release 9.2.0.2.0 - Production
CORE    9.2.0.2.0       Production
TNS for HPUX: Version 9.2.0.2.0 - Production
NLSRTL Version 9.2.0.2.0 - Production

What happens in 8i?
 

Tom Kyte

Followup  

May 23, 2003 - 10:01 am UTC

what typo?

I have two examples -- one in 8i, one in 9i.

Executing long ddls using dynamic sql

July 27, 2004 - 10:22 am UTC

Reviewer: A reader

Hi Tom I'm having problems executin long ddls like this for example

I need to rund ddls dinamically of packages with more than 20,000 characters, any idea. (they packages are only one copmlex process and I can't divide in smaller packages)

execute immediate 'CREATE OR REPLACE
PACKAGE adm.pck_captura2
IS
FUNCTION clt ( cCodigo VARCHAR2, dFecha DATE, nNoProceso NUMBER,cPath VARCHAR2 )
RETURN VARCHAR2;
FUNCTION mco ( cCodigo VARCHAR2, dFecha DATE,nNoProceso NUMBER,cPath VARCHAR2 )
RETURN VARCHAR2;
FUNCTION mde ( cCodigo VARCHAR2, dFecha DATE,nNoProceso NUMBER,cPath VARCHAR2 )
RETURN VARCHAR2;
FUNCTION men ( cCodigo VARCHAR2, dFecha DATE,nNoProceso NUMBER,cPath VARCHAR2 )
RETURN VARCHAR2;
FUNCTION mfa ( cCodigo VARCHAR2, dFecha DATE,nNoProceso NUMBER,cPath VARCHAR2 )
RETURN VARCHAR2;
FUNCTION mpg ( cCodigo VARCHAR2, dFecha DATE,nNoProceso NUMBER,cPath VARCHAR2 )
RETURN VARCHAR2;
FUNCTION mru ( cCodigo VARCHAR2, dFecha DATE, nNoProceso NUMBER,cPath VARCHAR2 )
RETURN VARCHAR2;
FUNCTION msf ( cCodigo VARCHAR2, dFecha DATE,nNoProceso NUMBER,cPath VARCHAR2 )
RETURN VARCHAR2;
FUNCTION mtc ( cCodigo VARCHAR2, dFecha DATE,nNoProceso NUMBER,cPath VARCHAR2 )
RETURN VARCHAR2;
FUNCTION mtr ( cCodigo VARCHAR2, dFecha DATE, nNoProceso NUMBER,cPath VARCHAR2 )
RETURN VARCHAR2;
END; -- Package spec
/';



Tom Kyte

Followup  

July 27, 2004 - 11:16 am UTC

you'll have to use dbms_sql which can parse arrays:

array(1) := 'create or replace ...'
array(2) := 'package adm.....'
array(3) := 'is'
......


of course, you could just put the code into a script and run the script -- be much easier than stuffing code into strings.....

(to put into array "easily", you can load it in your database and select it back out:

  1  select 'array('||line||') := ''' ||
  2  replace( replace( text, chr(10), '' ), '''', '''''' ) || ''';' from
  3  user_source
  4* where type = 'PROCEDURE' and name = 'P'
ops$tkyte@ORA9IR2> /
 
'ARRAY('||LINE||'):='''||REPLACE(REPLACE(TEXT,CHR(10),''),'''','''''')||''';'
-------------------------------------------------------------------------------
array(1) := 'procedure p ( p_str in varchar2 )';
array(2) := 'is';
array(3) := '   l_str   long := p_str;';
array(4) := 'begin';
array(5) := '   loop';
array(6) := '      exit when l_str is null;';
array(7) := '      dbms_output.put_line( substr( l_str, 1, 250 ) );';
array(8) := '      l_str := substr( l_str, 251 );';
array(9) := '   end loop;';
array(10) := 'end;';
 
10 rows selected.
 






 

July 27, 2004 - 2:31 pm UTC

Reviewer: A reader

Thanks Tom

July 27, 2004 - 5:27 pm UTC

Reviewer: A reader

didn't work with array, worked with variable
declare
type varchar2_aArray is table of varchar2(1000) index by binary_integer;
aArray varchar2_aArray;

nCursor NUMBER;
csql VARCHAR2(4000);
nRows NUMBER;
begin

aArray(1) := 'CREATE OR REPLACE '||CHR(10);
aArray(2) := 'PACKAGE ctb.pck_cco2'||CHR(10);
aArray(3) := ' IS'||CHR(10);
aArray(4) := ' cLUsaCostoParcial VARCHAR2(1);'||CHR(10);
aArray(5) := ' CURSOR uEstCosto IS SELECT ECC_NIVEL, ECC_LONG FROM ESTcCosto ORDER BY ECC_NIVEL;'||CHR(10);
aArray(6) := ''||CHR(10);
aArray(7) := ' FUNCTION VALIDCCOSTO (cCuenta VARCHAR2, cCosto IN OUT VARCHAR2, cCtaMayor VARCHAR2) RETURN VARCHAR2 ;'||CHR(10);
aArray(8) := ' FUNCTION getnomcosto( cCosto VARCHAR2 ) RETURN VARCHAR2;'||CHR(10);
aArray(9) := ' FUNCTION CuentaUsaCcosto( cCuenta VARCHAR2 ) RETURN BOOLEAN;'||CHR(10);
aArray(10) := ' FUNCTION UsaCcosto( cTratamiento VARCHAR2 ) RETURN BOOLEAN;'||CHR(10);
aArray(11) := ' FUNCTION UsaCcosto RETURN BOOLEAN;'||CHR(10);
aArray(12) := ' FUNCTION GetCcostoLike( cCosto VARCHAR2, cParte1 VARCHAR2, cParte2 VARCHAR2,'||CHR(10);
aArray(13) := ' cParte3 VARCHAR2, cParte4 VARCHAR2, cParte5 VARCHAR2)'||CHR(10);
aArray(14) := ' RETURN VARCHAR2;'||CHR(10);
aArray(15) := ' FUNCTION GetCostoCodigos( cCosto VARCHAR2, cParte1 OUT VARCHAR2,'||CHR(10);
aArray(16) := ' cParte2 OUT VARCHAR2, cParte3 OUT VARCHAR2,'||CHR(10);
aArray(17) := ' cParte4 OUT VARCHAR2, cParte5 OUT VARCHAR2) RETURN VARCHAR2;'||CHR(10);
aArray(18) := ' PROCEDURE GetcCostoCodigos( cCosto VARCHAR2, cAlerta VARCHAR2, cParte1 OUT VARCHAR2, cParte2 OUT VARCHAR2,'||CHR(10);
aArray(19) := ' cParte3 OUT VARCHAR2, cParte4 OUT VARCHAR2, cParte5 OUT VARCHAR2);'||CHR(10);
aArray(20) := ' FUNCTION GETCCOSTOQUERY( cCosto VARCHAR2 ) RETURN VARCHAR2;'||CHR(10);
aArray(21) := 'FUNCTION FUT_CCO_IMPUTACION( cCta VARCHAR2,nMonto NUMBER, cCCO_COD2 VARCHAR2 )'||CHR(10);
aArray(22) := ' RETURN TYP_CCO_IMPUTACION;'||CHR(10);
aArray(23) := 'END; -- Package spec'||CHR(10);
--aArray(24) := '/'||CHR(10);

FOR I IN 1..23 LOOP
csql := csql ||aArray(I);
END LOOP;
nCursor := dbms_sql.open_cursor;
dbms_sql.parse( nCursor, csql,DBMS_SQL.NATIVE);
--dbms_sql.parse( nCursor, aArray,DBMS_SQL.NATIVE); *** failed
nRows := dbms_sql.execute( nCursor);
dbms_output.put_line( nCursor );

exception when others then
dbms_output.put_line(sqlerrm);
end;

Tom Kyte

Followup  

July 27, 2004 - 7:45 pm UTC

sorry -- forgot to mention you *might* want to browse the documentation for details?

procedure parse(c in integer, statement in varchar2a,
lb in integer, ub in integer,
lfflg in boolean, language_flag in integer);
-- Description: (copied from parse for varchar2s)
-- Parse the given statement in the given cursor. The statement is not in
-- one piece but resides in little pieces in the PL/SQL table "statement".
-- Conceptually what happens is that the SQL string is put together as
-- follows:
-- String := statement(lb) || statement(lb + 1) || ... || statement(ub);
-- Then a regular parse follows.
-- If "lfflg" is TRUE then a newline is inserted after each piece.
-- For further information and for documentation on the rest of the
-- arguments see the regular parse procedure below.


the signature of parse is a tad different when hyou have an array. if you get into the supplied packages guide, it'll be helpful.

July 27, 2004 - 5:43 pm UTC

Reviewer: A reader

....
and using clob(50801 characters) gives a 6502 error

declare
ELLOB clob;
nCursor NUMBER;
csql VARCHAR2(4000);
nRows NUMBER;
begin
/*
aArray(1) := 'CREATE OR REPLACE '||CHR(10);
aArray(2) := 'PACKAGE ctb.pck_cco2'||CHR(10);
aArray(3) := ' IS'||CHR(10);
aArray(4) := ' cLUsaCostoParcial VARCHAR2(1);'||CHR(10);
aArray(5) := ' CURSOR uEstCosto IS SELECT ECC_NIVEL, ECC_LONG FROM ESTcCosto ORDER BY ECC_NIVEL;'||CHR(10);
aArray(6) := ''||CHR(10);
aArray(7) := ' FUNCTION VALIDCCOSTO (cCuenta VARCHAR2, cCosto IN OUT VARCHAR2, cCtaMayor VARCHAR2) RETURN VARCHAR2 ;'||CHR(10);
aArray(8) := ' FUNCTION getnomcosto( cCosto VARCHAR2 ) RETURN VARCHAR2;'||CHR(10);
aArray(9) := ' FUNCTION CuentaUsaCcosto( cCuenta VARCHAR2 ) RETURN BOOLEAN;'||CHR(10);
aArray(10) := ' FUNCTION UsaCcosto( cTratamiento VARCHAR2 ) RETURN BOOLEAN;'||CHR(10);
aArray(11) := ' FUNCTION UsaCcosto RETURN BOOLEAN;'||CHR(10);
aArray(12) := ' FUNCTION GetCcostoLike( cCosto VARCHAR2, cParte1 VARCHAR2, cParte2 VARCHAR2,'||CHR(10);
aArray(13) := ' cParte3 VARCHAR2, cParte4 VARCHAR2, cParte5 VARCHAR2)'||CHR(10);
aArray(14) := ' RETURN VARCHAR2;'||CHR(10);
aArray(15) := ' FUNCTION GetCostoCodigos( cCosto VARCHAR2, cParte1 OUT VARCHAR2,'||CHR(10);
aArray(16) := ' cParte2 OUT VARCHAR2, cParte3 OUT VARCHAR2,'||CHR(10);
aArray(17) := ' cParte4 OUT VARCHAR2, cParte5 OUT VARCHAR2) RETURN VARCHAR2;'||CHR(10);
aArray(18) := ' PROCEDURE GetcCostoCodigos( cCosto VARCHAR2, cAlerta VARCHAR2, cParte1 OUT VARCHAR2, cParte2 OUT VARCHAR2,'||CHR(10);
aArray(19) := ' cParte3 OUT VARCHAR2, cParte4 OUT VARCHAR2, cParte5 OUT VARCHAR2);'||CHR(10);
aArray(20) := ' FUNCTION GETCCOSTOQUERY( cCosto VARCHAR2 ) RETURN VARCHAR2;'||CHR(10);
aArray(21) := 'FUNCTION FUT_CCO_IMPUTACION( cCta VARCHAR2,nMonto NUMBER, cCCO_COD2 VARCHAR2 )'||CHR(10);
aArray(22) := ' RETURN TYP_CCO_IMPUTACION;'||CHR(10);
aArray(23) := 'END; -- Package spec'||CHR(10);
--aArray(24) := '/'||CHR(10);

FOR I IN 1..23 LOOP
csql := csql ||aArray(I);
END LOOP;
nCursor := dbms_sql.open_cursor;
dbms_sql.parse( nCursor, csql,DBMS_SQL.NATIVE);
--dbms_sql.parse( nCursor, aArray,DBMS_SQL.NATIVE);
nRows := dbms_sql.execute( nCursor);
dbms_output.put_line( nCursor );
*/

SELECT A INTO ELLOB FROM CTB.TESTE;
nCursor := dbms_sql.open_cursor;
dbms_output.put_line( ELLOB);
dbms_sql.parse( nCursor, ELLOB,DBMS_SQL.NATIVE);
--dbms_sql.parse( nCursor, aArray,DBMS_SQL.NATIVE);
nRows := dbms_sql.execute( nCursor);
dbms_output.put_line( nCursor );


exception when others then
dbms_output.put_line(sqlerrm);
end;

if you have some idea please

Tom Kyte

Followup  

July 27, 2004 - 7:46 pm UTC

yah, idea=check out the docs so you can implement the array concept.....

</code> http://docs.oracle.com/docs/cd/B10501_01/appdev.920/a96612/d_sql.htm#998100 <code>



July 27, 2004 - 6:57 pm UTC

Reviewer: A reader

Thanks Tom I got it
In </code> http://asktom.oracle.com/pls/ask/f?p=4950:9:7337978121515501587::NO:9:F4950_P9_DISPLAYID:6161200355268 <code>

Here is applied to a clob

declare
dbms_sql.varchar2s;
l_cursor integer default dbms_sql.open_cursor;
l_rows number default 0;
ELLOB clob;
nPos number ;
I number := 1;
nPosAnt number := 1;
begin
SELECT A INTO ELLOB FROM CTB.TESTE;

/* WHILE TRUE LOOP
DBMS_OUTPUT.PUT_LINE(i||'-'||substr(ellob,i,1)||'-'||ascII(substr(ellob,i,1)));
i:=i+1;
if i>50 then
exit;
end if;
END LOOP;*/

WHILE TRUE LOOP
DBMS_OUTPUT.PUT_LINE(1);
nPos := instr( ELLOB,CHR(10),1,i);
-- DBMS_OUTPUT.PUT_LINE(nPos||':pos-i:'||i);
DBMS_OUTPUT.PUT_LINE(2);
if nPos = 0 THEN
EXIT;
END IF;
DBMS_OUTPUT.PUT_LINE(substr(ellob,nPosAnt,nPos-nPosAnt));

DBMS_OUTPUT.PUT_LINE(3);
l_stmt(i) := substr(ellob,nPosAnt,nPos-nPosAnt);
DBMS_OUTPUT.PUT_LINE(4);
i:=i+1;
DBMS_OUTPUT.PUT_LINE(5);
nPosAnt := nPos;
END LOOP;


dbms_sql.parse( c => l_cursor,
statement => l_stmt,
lb => l_stmt.first,
ub => l_stmt.last,
lfflg => TRUE,
language_flag => dbms_sql.native );
l_rows := dbms_sql.execute(l_cursor);






dbms_sql.close_cursor( l_cursor );
exception
when others then
if dbms_sql.is_open(l_cursor) then
dbms_sql.close_cursor(l_cursor);
end if;
raise;
end;
/

November 27, 2019 - 1:53 pm UTC

Reviewer: Edgar from Sweden

Hi,

DDL triggers are evil, and my code is quite scratchy; but looks like we can cancel (and revert back) DDL by raising an exception in a DDL trigger.

create or replace trigger sys.check_only_alter_ddl_trg
after alter on database
declare
 n number;
 sqltext ora_name_list_t;
begin
    n := ora_sql_txt(sqltext);
    if upper(sqltext(1)) like '%/*%+%CHECKONLY%*/%' then
        raise_application_error(-20001,'It is a check only ALTER DDL.');
    end if;
end;
/

create or replace
procedure check_exchange_statement(p_table_name in varchar2,
    p_partition_name in varchar2 default null,
    p_exchange_table_name in varchar2 default null)
--------------------------------------------------------------------------------
-- Check exchange partition statement for the given table.
-- If the partition name is not specified peeks the 1st partition of the table.
-- If the exchange table name is not specified forms it by adding _EXC to the
-- table name.
is
    e_check_only exception;
    pragma exception_init(e_check_only, -04088);
-- ORA-04088: error during execution of trigger 'SYS.CHECK_ONLY_ALTER_DDL_TRG'
    v_partition_name user_tab_partitions.partition_name%type;
    v_exchange_table_name user_tables.table_name%type;
    v_cursor_id integer;
begin
    if p_partition_name is null then
       select partition_name into v_partition_name from user_tab_partitions
        where table_name = p_table_name and partition_position = 1;
    else
        v_partition_name := p_partition_name;
    end if;
    if p_exchange_table_name is null then
        v_exchange_table_name := p_table_name||'_EXC';
    else
        v_exchange_table_name := p_exchange_table_name;
    end if;
    begin
        v_cursor_id := dbms_sql.open_cursor;
        dbms_sql.parse(v_cursor_id,
            'alter /*+CHECKONLY */ table '||p_table_name||' exchange partition '
            ||v_partition_name||' with table '||v_exchange_table_name||
            ' including indexes update indexes',
            dbms_sql.native);
        dbms_sql.close_cursor(v_cursor_id);
    exception
        when e_check_only then dbms_sql.close_cursor(v_cursor_id);
    end;
end;
/

SQL> exec check_exchange_statement('CWMILESTONE');

PL/SQL procedure successfully completed.

SQL> exec check_exchange_statement('CWPC_BASKETITEM');
BEGIN check_exchange_statement('CWPC_BASKETITEM'); END;

*
ERROR at line 1:
ORA-14098: index mismatch for tables in ALTER TABLE EXCHANGE PARTITION
ORA-06512: at "SYS.DBMS_SQL", line 1134
ORA-06512: at "CW_EOC_UI.CHECK_EXCHANGE_STATEMENT", line 29
ORA-06512: at line 1

SQL> select count(*) from CWMILESTONE_EXC;

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

SQL> select 'select count(*) from CWMILESTONE partition ('||partition_name||');' from user_tab_partitions where table_name='CWMILESTONE' order by partition_position;

'SELECTCOUNT(*)FROMCWMILESTONEPARTITION('||PARTITION_NAME||');'
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
select count(*) from CWMILESTONE partition (SYS_P4790);
select count(*) from CWMILESTONE partition (SYS_P4011);
select count(*) from CWMILESTONE partition (SYS_P4012);

SQL> select count(*) from CWMILESTONE partition (SYS_P4790);

  COUNT(*)
----------
     24941

SQL> select count(*) from CWMILESTONE partition (SYS_P4011);

  COUNT(*)
----------
      6314

SQL> select count(*) from CWMILESTONE partition (SYS_P4012);

  COUNT(*)
----------
      8116

Connor McDonald

Followup  

November 29, 2019 - 4:46 am UTC

True. Other options are disabling table locks in some cases.