执行SQL

标签：USERNAME dbms grant create sql spool 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'

 

标签：USERNAME,dbms,grant,create,sql,spool,SQL,执行
From： https://www.cnblogs.com/teiperfly/p/18232938