Script to lock multiple tables in a schema oracle
One of my fried asked me for a dynamic script to lock stats for multiple tables in a schema with some specific naming convention. So that automatic script will not gather the stats for those tables. Below is procedure for the same.
1) Create the script file “lock_script.sql” with below content.
————————————-
SET ECHO OFF
SET SPACE 0
SET PAGESIZE 0
SET FEEDBACK OFF
SET HEADING OFF
SET trimspool on
set serveroutput on
SET SPACE 0
SET PAGESIZE 0
SET FEEDBACK OFF
SET HEADING OFF
SET trimspool on
set serveroutput on
spool lock_tables.sql
begin
for i in (select * from dba_tables where owner=’SCOTT’ and table_name like ‘DEPT%’)
loop
dbms_output.put_line(‘exec dbms_stats.lock_table_stats(”’||i.owner||”’,”’||i.table_name||”’);’);
end loop;
end;
/
SPOOL off
set echo on
set feedback on
set heading on
set trimspool off
@lock_tables
—————————————
Change the select query in for loop with query suitable for your environment. This will generate a output file with list of lock tables commands in “lock_tables.sql” file. at the end script will automatically execute the same file which will lock the tables.
Below is the output :-
SQL> @lock_script
exec dbms_stats.lock_table_stats(‘SCOTT’,’DEPT2′);
exec dbms_stats.lock_table_stats(‘SCOTT’,’DEPT1′);
exec dbms_stats.lock_table_stats(‘SCOTT’,’DEPT’);
SQL> set feedback on
SQL> set heading on
SQL> set trimspool off
SQL>
SQL> @lock_tables
SQL> exec dbms_stats.lock_table_stats(‘SCOTT’,’DEPT2′);
exec dbms_stats.lock_table_stats(‘SCOTT’,’DEPT2′);
exec dbms_stats.lock_table_stats(‘SCOTT’,’DEPT1′);
exec dbms_stats.lock_table_stats(‘SCOTT’,’DEPT’);
SQL> set feedback on
SQL> set heading on
SQL> set trimspool off
SQL>
SQL> @lock_tables
SQL> exec dbms_stats.lock_table_stats(‘SCOTT’,’DEPT2′);
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.lock_table_stats(‘SCOTT’,’DEPT1′);
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.lock_table_stats(‘SCOTT’,’DEPT’);
PL/SQL procedure successfully completed.
SQL> ————————–
SQL>
SQL> select stattype_locked , table_name from dba_tab_statistics where owner=’SCOTT’ and table_name like ‘DEPT%’;
STATT TABLE_NAME
—– ——————————
ALL DEPT
ALL DEPT1
ALL DEPT2
ALL means the table is locked. If the table is not locked it will show nothing.
Viswamitra's blog 