执行SQL

 记录日志

spool sqlfile.sql.log
prompt Runing sqlfile.sql
@sqlfile.sql

 

清理日志和创建监控

delete from sys.fga_log$ where ntimestamp# < sysdate - 20;

commit;

EXEC dbms_fga.add_policy(policy_name=>'MYPOLICY1',statement_types=>'INSERT,DELETE,UPDATE',object_schema=>'user',object_name=>'table');

 

授权

grant create session to USERNAME;
grant select any dictionary to USERNAME;
grant CREATE PROCEDURE to USERNAME;
grant create table to USERNAME;
grant create synonym to USERNAME;
grant create view to USERNAME;
grant create sequence to USERNAME;
grant debug any procedure to USERNAME;
grant DEBUG CONNECT SESSION to USERNAME;
grant SELECT ANY SEQUENCE to USERNAME;
grant select any table to USERNAME;
grant create trigger to USERNAME;
grant unlimited tablespace to USERNAME;
grant ALTER ANY MATERIALIZED VIEW to USERNAME;

 

生成SQL

set line 1000
set linesize 256
set pagesize 9999
set long 999999
set heading off
col myrow format a200
set feedback off;
set echo off ;
spool procedures.sql ;
SELECT DBMS_METADATA.GET_DDL('INDEX','TABLE','USER') FROM DUAL;
spool off;

 

物化视图刷新 

exec dbms_mview.refresh('USERNAME.MView','C');

call dbms_mview.refresh('USERNAME.MView', 'C');

select 'exec dbms_mview.refresh('''||OWNER||'.'||OBJECT_NAME||''',''C'');' from dba_objects where owner='USERNAME' and object_type='MATERIALIZED VIEW'

 

统计信息更新

sqlplus -s CMO/CMOOMC@${instance} << EOF
exec dbms_stats.gather_schema_stats(ownname => '${schema}',estimate_percent => dbms_stats.auto_sample_size);
exit;
EOF

 

脚本执行SQL

sqlplus -S $tsnstr << EOF > /dev/null 2>&1
set line 1000;
set pagesize 9999;
set trimout on;
set trimspool on;
set termout off;
set time on;
set timing on;
@"$SQLFILE"
commit;
exit;
EOF