tag:blogger.com,1999:blog-5124641802818980374.post3468985078791629385..comments2023-09-06T11:58:30.752+02:00Comments on Oracle related stuff: EXPLAIN PLAN on DDLsUnknownnoreply@blogger.comBlogger6125tag:blogger.com,1999:blog-5124641802818980374.post-68526824771835612372013-11-22T17:01:50.436+01:002013-11-22T17:01:50.436+01:00Can I get execution plan of alter table Oracle 11....Can I get execution plan of alter table Oracle 11.2.0.1 Windows :<br /><br />SQL> CREATE TABLE TEST<br /> 2 (<br /> 3 TEST_ID NUMBER NOT NULL,<br /> 4 TEST_NAME CLOB,<br /> 5 CONSTRAINT PK_TEST PRIMARY KEY(TEST_ID)<br /> 6 )<br /> 7 /<br /><br />Table created.<br /><br />SQL> explain plan for<br /> 2 ALTER TABLE scott.test MOVE LOB (test_name) STORE AS (TABLESPACE EXAMPLE) PARALLEL 2;<br />ALTER TABLE scott.test MOVE LOB (test_name) STORE AS (TABLESPACE EXAMPLE) PARALLEL 2<br /> *<br />ERROR at line 2:<br />ORA-00600: internal error code, arguments: [audxpl:invalid action], [15], [],<br />[], [], [], [], [], [], [], [], []<br />ORA-00900: invalid SQL statement<br /><br />Regards<br />Girish SharmaAnonymoushttps://www.blogger.com/profile/01325588005633104905noreply@blogger.comtag:blogger.com,1999:blog-5124641802818980374.post-85621419221340999362009-04-28T10:45:00.000+02:002009-04-28T10:45:00.000+02:00Hemant,
thanks for mentioning the DBMS_SPACE proc...Hemant,<br /><br />thanks for mentioning the DBMS_SPACE procedure.<br /><br />I haven't tested it yet but looking at the procedure definition it may just run an EXPLAIN PLAN under the covers since it requires a plan table and the information about used bytes and allocated bytes could actually be taken from the EXPLAIN PLAN result (the BYTES column and the OTHER_XML xml information).<br /><br />If it does so the results should be consistent with what I've mentioned here.<br /><br />Regards,<br />RandolfRandolfhttps://www.blogger.com/profile/13463198440639982695noreply@blogger.comtag:blogger.com,1999:blog-5124641802818980374.post-27871327729073304342009-04-28T05:14:00.000+02:002009-04-28T05:14:00.000+02:00In 10gR2, I can see a DBMS_SPACE.CREATE_INDEX_COST...In 10gR2, I can see a DBMS_SPACE.CREATE_INDEX_COST procedure which does provide estimates of USED_BYTES and ALLOCATED_BYTES (but not Time and TempSpace). <br />Rudimentary testing shows that it DOES rely on Table (and column statistics).Hemant K Chitalehttps://www.blogger.com/profile/07369112096230549250noreply@blogger.comtag:blogger.com,1999:blog-5124641802818980374.post-17587426603926911222009-03-03T22:48:00.000+01:002009-03-03T22:48:00.000+01:00Gints,thanks for pointing this out... I'm referrin...Gints,<BR/><BR/>thanks for pointing this out... <BR/><BR/>I'm referring here to statements that are actually mentioned in the manuals but probably only used rarely with EXPLAIN PLAN.<BR/><BR/>But you're correct, the MERGE statement is missing in the manuals, may be they think it's already covered by the INSERT/UPDATE/DELETEs statements mentioned.<BR/><BR/>Regards,<BR/>RandolfRandolfhttps://www.blogger.com/profile/13463198440639982695noreply@blogger.comtag:blogger.com,1999:blog-5124641802818980374.post-80946557423390330382009-03-03T22:14:00.000+01:002009-03-03T22:14:00.000+01:00A bit messed up my previous comment - MERGE statem...A bit messed up my previous comment - MERGE statement isn't mentioned there, although explain plan works for it.Gints Plivnahttps://www.blogger.com/profile/17716037729002528529noreply@blogger.comtag:blogger.com,1999:blog-5124641802818980374.post-87080895867547050442009-03-03T22:12:00.000+01:002009-03-03T22:12:00.000+01:00This comment has been removed by the author.Gints Plivnahttps://www.blogger.com/profile/17716037729002528529noreply@blogger.com