Execute immediate guidance

Question

I have procedure that I want to run regularly to update whatever Oracle needs in order to function efficently. This procedure computes statistics, updates and rebuilds indexes on tables that are built nightly.

It looks for tables and indexes that begin with a pattern and builds the update statistics, rebuild indexes, etc command with the intent that I would to execute immediate .

In the past I got an ORA error saying I or it lacked permission to run, but I'm wondering if I can just directly execute these things.

begin
    -- Would add EXECUTE immediate lsql below, but there is some permission issue
    -- -execute immediate lsql;
    for sc_hub_indexes_record in hub_indexes_cursor
    loop
        lsql:='alter index '||sc_hub_indexes_record.table_owner||'.'||sc_hub_indexes_record.index_name||' rebuild online parallel;';
        dbms_output.put_line(lsql);
        execute immediate lsql;

        lsql:='DBMS_STATS.GATHER_INDEX_STATS(ownname => ''SC_REPORT_NEW'', indname => '||apostrophe||sc_hub_indexes_record.index_name||apostrophe||';';
        dbms_output.put_line(lsql);
        execute immediate lsql;
    end loop;

    for sc_hub_tables_record in hub_tables_cursor
    loop
        quoted_owner :=  apostrophe||'SC_REPORT_NEW'||apostrophe;
        quoted_table :=  apostrophe||sc_hub_tables_record.table_name||apostrophe;
        lsql:='dbms_stats.gather_table_stat ( ownname => '||quoted_owner||', tabname => '||quoted_table||', estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE );';
        dbms_output.put_line(lsql);
         -- This gives an error for lack of permission.  Not yet sure why. 
        execute immediate lsql;
    end loop;
end;

Please note, my account would work if I just ran the begin alter index;end;/ type things, it only failed if I made an execute statement out of it. However, I don't know how else to dynamically build these statements.

Just a suggestion. Write the SQL statements, that your PL/SQL code generates, to a file, i.e. a SQL script. Then run the script. — Abra
For calling packages you don't need dynamic SQL, i.e. execute immediate. Call it directly DBMS_STATS.GATHER_INDEX_STATS(ownname => 'SC_REPORT_NEW', indname => sc_hub_indexes_record.index_name); — Wernfried Domscheit
my account would work if I just ran the begin alter index;end;/ type things - not sure what you mean here, as PL/SQL has no alter keyword so it must use execute immediate/dbms_sql. — William Robertson
There is absolutely no need to rebuild indexes, as per statement by @pifor. And there is no need to create a job to gather stats. Oracle already has a default stats job that runs daily and is much smarter about what needs to be done. You are spinning your wheels trying to solve a problem that does not exist. — EdStevens
I would like to give credit to all the answers as I've learned something from everyone. @Abra This is normally what I do, all my scripts are checked into source control and run separately, I just wanted to show a snippet of what I run. — Woodsman

pifor pifor · Accepted Answer · 2020-06-24T06:39:23

"lack of permission" likely root cause it that in stored PL/SQL roles are not enabled by default : you must be granted direct privileges (without role). This does not apply to anonymous PL/SQL. See https://asktom.oracle.com/Misc/RolesAndProcedures.html.

Side note: in general it is not needed to rebuild indexes in Oracle.

From AskTom:

Ask them for the technical reasons WHY they rebuild.

When they say "performance of course" -- ask them for the performance metrics they took BEFORE the rebuild and AFTER the rebuild. They won't have any (no one ever does, no one seems to think about doing that). They will say "it just goes faster trust me".

When they say "to reclaim space of course" -- ask them "but how long does it take before the index is just a big as it was before the rebuild". You see they have not reclaimed any space -- the index will just grow again, eventually hitting a steady state size that it likes to be.

If you need to rebuild your indexes, you need 2x the space -- you'll have the old and the new index for a period of time. If you do it online, you'll need additional space to hold the changes that are made during the rebuild as well.

Execute immediate guidance

1 Answers