物化视图有两种不同的刷新方式，其中的 FAST REFRESH 对于数据仓库型的数据库相当于有用，它只将上一次刷新之后修改的数据刷新到本地。

       要使用 FAST REFRESH 必须在 MASTER 表上建立物化视图日志，用于记录对 MASTER 表的修改。Oracle 用两种方式来定位被修改的行，一种是 ROWID，一种是 Primary Key。在 Oracle 8 之前，只有 ROWID 这种方式被支持，从 Oracle 8 开始 Primary Key 方式开始被支持，并成为了默认方式。而为了向前兼容，ROWID 的方式也被保留。下面看看两种不同类型物化视图在创建过程中都做了些什么，有什么不同。

基于 ROWID 的物化视图

SQL> create table skytest(a number primary key);

Table created.

SQL> create materialized view log on skytest with rowid;

Materialized view log created.

SQL> select object_name,object_type from user_objects
  2  where object_name like '%SKYTEST%';

OBJECT_NAME                    OBJECT_TYPE
------------------------------ ------------------
MLOG$_SKYTEST                  TABLE
SKYTEST                        TABLE

SQL> create materialized view MV_SKYTEST
  2  REFRESH FAST
  3  as
  4  select * from skytest;
select * from skytest
              *
ERROR at line 4:
ORA-23415: snapshot log for "ORACLE"."SKYTEST" does not record the primary key

Oracle 8 开始默认使用 Primary Key 方式。

SQL> create materialized view MV_SKYTEST
  2  REFRESH FAST
  3  with rowid
  4  as
  5  select * from skytest;

Materialized view created.

SQL> select object_name,object_type from user_objects
  2  where object_name like '%SKYTEST%';

OBJECT_NAME                    OBJECT_TYPE
------------------------------ ------------------
I_SNAP$_MV_SKYTEST             INDEX
MLOG$_SKYTEST                  TABLE
MV_SKYTEST                     TABLE
MV_SKYTEST                     UNDEFINED
SKYTEST                        TABLE

        可以看到除了多了物化视图 MV_SKYTEST 之外，还多了一个索引 I_SNAP$_MV_SKYTEST 和一个表 MLOG$_SKYTEST

SQL> desc MLOG$_SKYTEST
 Name                                        Null?    Type
 ------------------------------------------- -------- -------------------
 M_ROW$$                                              VARCHAR2(255)
 SNAPTIME$$                                           DATE
 DMLTYPE$$                                            VARCHAR2(1)
 OLD_NEW$$                                            VARCHAR2(1)
 CHANGE_VECTOR$$                                      RAW(255)

       MLOG$_SKYTEST 表中记录了主表上修改的记录，M_ROW$$ 保存主表上修改行的 ROWID，这样可以定位修改的行。

SQL> select INDEX_NAME,INDEX_TYPE,TABLE_NAME,UNIQUENESS,STATUS,GENERATED from dba_indexes
  2  where index_name='I_SNAP$_MV_SKYTEST';

INDEX_NAME                     INDEX_TYPE                  TABLE_NAME                     UNIQUENES STATUS   G
------------------------------ --------------------------- ------------------------------ --------- -------- -
I_SNAP$_MV_SKYTEST             NORMAL                      MV_SKYTEST                     UNIQUE    VALID    N

SQL> select table_name,index_name,column_name from dba_ind_columns
  2  where index_name='I_SNAP$_MV_SKYTEST';

TABLE_NAME                     INDEX_NAME                     COLUMN_NAME
------------------------------ ------------------------------ ------------------------------
MV_SKYTEST                     I_SNAP$_MV_SKYTEST             M_ROW$$

SQL> select table_name,column_name from dba_tab_columns
  2  where table_name='MV_SKYTEST';

TABLE_NAME                     COLUMN_NAME
------------------------------ ------------------------------
MV_SKYTEST                     A

       I_SNAP$_MV_SKYTEST 是建立在物化视图 MV_SKYTEST 中 M_ROW$$ 隐藏列上的唯一索引，该列正是保存了主表上对应行的 ROWID 值，建立该索引应该是为了提高刷新的性能。

       另外一个类型为 UNDEFINED，名字和物化视图一样的东东不知道是干吗的。

基于 Primary Key 的物化视图

SQL> drop materialized view MV_SKYTEST;

Materialized view dropped.

SQL> drop materialized view log on SKYTEST;

Materialized view log dropped.

SQL> select object_name,object_type from user_objects
  2  where object_name like '%SKYTEST%';

OBJECT_NAME                    OBJECT_TYPE
------------------------------ ------------------
SKYTEST                        TABLE

SQL> create materialized view log on SKYTEST;

Materialized view log created.

SQL> select object_name,object_type from user_objects
  2  where object_name like '%SKYTEST%';

OBJECT_NAME                    OBJECT_TYPE
------------------------------ ------------------
MLOG$_SKYTEST                  TABLE
RUPD$_SKYTEST                  TABLE
SKYTEST                        TABLE

SQL> create materialized view MV_SKYTEST
  2  REFRESH FAST
  3  as
  4  select * from skytest;

Materialized view created.

SQL> select object_name,object_type from user_objects
  2  where object_name like '%SKYTEST%';

OBJECT_NAME                    OBJECT_TYPE
------------------------------ ------------------
MLOG$_SKYTEST                  TABLE
MV_SKYTEST                     TABLE
MV_SKYTEST                     UNDEFINED
RUPD$_SKYTEST                  TABLE
SKYTEST                        TABLE

SQL> desc MLOG$_SKYTEST
 Name                                                                                Null?    Type
 ----------------------------------------------------------------------------------- -------- --------------------------------------------------------
 A                                                                                            NUMBER
 SNAPTIME$$                                                                                   DATE
 DMLTYPE$$                                                                                    VARCHAR2(1)
 OLD_NEW$$                                                                                    VARCHAR2(1)
 CHANGE_VECTOR$$                                                                              RAW(255)

SQL> desc RUPD$_SKYTEST
 Name                                                                                Null?    Type
 ----------------------------------------------------------------------------------- -------- --------------------------------------------------------
 A                                                                                            NUMBER
 DMLTYPE$$                                                                                    VARCHAR2(1)
 SNAPID                                                                                       NUMBER(38)
 CHANGE_VECTOR$$                                                                              RAW(255)

       此时 mlog 中保存了 Primary Key。RUPD$_ 开头的表用于可更新的基于 Primary Key 的物化视图。

SQL> select INDEX_NAME,INDEX_TYPE,TABLE_NAME,UNIQUENESS,STATUS,GENERATED from dba_indexes
  2  where table_name in ('SKYTEST','MV_SKYTEST'); 

INDEX_NAME                     INDEX_TYPE                  TABLE_NAME                     UNIQUENES STATUS   G
------------------------------ --------------------------- ------------------------------ --------- -------- -
SYS_C001221                    NORMAL                      SKYTEST                        UNIQUE    VALID    Y
SYS_C001224                    NORMAL                      MV_SKYTEST                     UNIQUE    VALID    Y

SQL> select CONSTRAINT_NAME,CONSTRAINT_TYPE,DEFERRABLE,DEFERRED,GENERATED from dba_constraints
  2  where table_name in ('SKYTEST','MV_SKYTEST');

CONSTRAINT_NAME                C DEFERRABLE     DEFERRED  GENERATED
------------------------------ - -------------- --------- --------------
SYS_C001221                    P NOT DEFERRABLE IMMEDIATE GENERATED NAME
SYS_C001224                    P NOT DEFERRABLE IMMEDIATE GENERATED NAME

       同时，Oracle 也会在物化视图上建立相应的 Primary Key。不过对此我比较疑惑，由于刷新机制的缘故，Oracle 是不建议在物化视图上使用 Primary/Unique constraint 的，而建议使用 non unique index + deferred constraint 的形式，参见：Note:284101.1

       最后要说明的是，在创建物化视图日志时可以同时指定 WITH ROWID 和 WITH PRIMARY KEY。此时，在物化视图日志中两者都会保存，但最终物化视图是哪种类型的则是在创建物化视图时决定（只能指定 WITH ROWID 和 WITH PRIMARY KEY 其中一个）。如果所有的物化视图都是同一种类型的，那么建议在创建物化视图时也只指定那一种类型，可以减少空间消耗提高刷新性能。

参考：

Oracle materialized view mlog$ table

Differences Between Rowid & Primary Key Materialized Views
Note:254593.1

Fast Refresh Causing ORA-1/ORA-2291/ORA-2292, Complete Refresh Works Fine
Note:284101.1

昨天需要按照条件导出一个表，再导入另一个数据库，很自然想到了 exp 的 query 参数，本来以为可以很顺利完成，结果 query 参数的格式怎么也写不对，因为时间紧迫，最后通过 dblink 直接插入了另一个数据库。今天回过头来再研究了一下，发现其实也不复杂，按照文档还是能很好的解决的。

希望导出测试表中日期为 2007-09-22 的一条数据：

SQL> select * from skytest;

       SID DT
---------- -----------------
         1 20070913 00:00:00
         2 20070914 00:00:00
         3 20070915 00:00:00
         4 20070916 00:00:00
         5 20070917 00:00:00
         6 20070918 00:00:00
         7 20070919 00:00:00
         8 20070920 00:00:00
         9 20070921 00:00:00
        10 20070922 00:00:00
        12 20070924 00:00:00

11 rows selected.

凭印象按照以下格式运行：

exp / tables=skytest file=test.dmp query=\"where dt=to_date(\'2007-09-22\',\'yyyy-mm-dd\')\"

ksh: syntax error: `(' unexpected

其实文档中讲得很清楚：

exp scott/tiger TABLES=emp QUERY=\"WHERE job=\'SALESMAN\' and sal \<1600\"

Note:
Because the value of the QUERY parameter contains blanks, most operating systems require that the entire strings WHERE job=\'SALESMAN\' and sal\<1600 be placed in double quotation marks or marked as a literal by some method. Operating system reserved characters also need to be preceded by an escape character. See your Oracle operating system-specific documentation for information about special and reserved characters on your system.

所有操作系统保留字符都要使用转义符号，看来 ( ) 也需要转义。

正确的写法：

oracle DBALNP01 > exp / tables=skytest file=test.dmp query=\"where dt=to_date\(\'2007-09-22\',\'yyyy-mm-dd\'\)\"

Export: Release 8.1.7.4.0 - Production on Wed Sep 12 04:30:45 2007

(c) Copyright 2000 Oracle Corporation.  All rights reserved.

Connected to: Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
With the Partitioning option
JServer Release 8.1.7.4.0 - Production
Export done in US7ASCII character set and US7ASCII NCHAR character set

About to export specified tables via Conventional Path ...
. . exporting table                        SKYTEST          1 rows exported
Export terminated successfully without warnings.

简洁的写法：
如果环境变量设置合适，就不需要使用 to_date 函数了

oracle DBALNP01 > exp / tables=skytest file=test.dmp query=\"where dt=\'20070922\'\"

Export: Release 8.1.7.4.0 - Production on Wed Sep 12 04:25:56 2007

(c) Copyright 2000 Oracle Corporation.  All rights reserved.

Connected to: Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
With the Partitioning option
JServer Release 8.1.7.4.0 - Production
Export done in US7ASCII character set and US7ASCII NCHAR character set

About to export specified tables via Conventional Path ...
. . exporting table                        SKYTEST          1 rows exported
Export terminated successfully without warnings.

任何 os 平台都适用的方法（推荐）：
以上两种方法并不一定适合其他操作系统，不过使用 parfile 就不用担心这些格式问题

oracle DBALNP01 > cat > test.par
tables=skytest
file=test.dmp
query="where dt=to_date('2007-09-22','yyyy-mm-dd')"

oracle DBALNP01 > exp / parfile=test.par

Export: Release 8.1.7.4.0 - Production on Wed Sep 12 04:22:27 2007

(c) Copyright 2000 Oracle Corporation.  All rights reserved.

Connected to: Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
With the Partitioning option
JServer Release 8.1.7.4.0 - Production
Export done in US7ASCII character set and US7ASCII NCHAR character set

About to export specified tables via Conventional Path ...
. . exporting table                        SKYTEST          1 rows exported
Export terminated successfully without warnings.

不需要任何转义符既简洁又可以在多种操作系统平台上通用，推荐使用这种方式。

参考：http://www.itpub.net/683793.html

       需要写一个简单的脚本，将所有数据文件、控制文件、在线日志文件等输出到一个文件中，一口气写完之后，发现输出结果中每行之间都有一空行，非常不爽，研究了一下 sqlplus 的设置参数之后解决了问题，把几个常用的参数在这里做一个小结，方便以后查询。

set echo on/off                     是否显示脚本中的需要执行的命令
set feedback on/off               是否显示 select 结果之后返回多少行的提示
set linesize n                          设置一行最多显示多少字符，之前就是因为 n 设得过大，导致行与行之间有空白行
set termout on/off                 在执行脚本时是否在屏幕上输出结果，如果 spool 到文件可以将其关闭
set heading on/off                 是否显示查询结果的列名，如果设置为 off，将用空白行代替，如果要去除该空白行，可以用 set pagesize 0
set pagesize n                        设置每页的行数，将 n 设为 0 可以不显示所有 headings, page breaks, titles, the initial blank line, and other formatting information
set  trimspool on/off               在 spool 到文件时是否去除输出结果中行末尾的空白字符，之前的隔行可以用该参数去掉，和该参数对应的是 trimout，后者用于屏幕输出
set trimout on/off                   是否去掉屏幕上输出结果行末尾的空白字符

       前几天在使用 dbms_job.remove 移除 job 时碰到了 ORA-23421 错误：

ORA-23421:    job number string is not a job in the job queue
Cause:    There is no job visible to the caller with the given job number.
Action:    Choose the number of a job visible to the caller.

SQL> show user
USER is "SKY"
SQL> declare n number;
  2  begin
  3  dbms_job.submit(n,'null;',sysdate,'sysdate+1');
  4  commit;
  5  end;
  6  /

PL/SQL procedure successfully completed.

SQL> select job,log_user,priv_user,schema_user,last_date,next_date,what
  2  from user_jobs;

JOB LOG_USER PRIV_USE SCHEMA_U LAST_DATE           NEXT_DATE           WHAT
--- -------- -------- -------- ------------------- ------------------- -----
 23 SKY      SKY      SKY      2007-04-24 00:32:56 2007-04-25 00:32:56 null;

SQL> conn /as sysdba
Connected.
SQL> exec dbms_job.INTERVAL(23,'sysdate+2')
BEGIN dbms_job.INTERVAL(23,'sysdate+2'); END;

*
ERROR at line 1:
ORA-23421: job number 23 is not a job in the job queue
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_IJOB", line 529
ORA-06512: at "SYS.DBMS_JOB", line 234
ORA-06512: at line 1


SQL> exec dbms_job.next_date(23,sysdate+1)
BEGIN dbms_job.next_date(23,sysdate+1); END;

*
ERROR at line 1:
ORA-23421: job number 23 is not a job in the job queue
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_IJOB", line 529
ORA-06512: at "SYS.DBMS_JOB", line 215
ORA-06512: at line 1


SQL> exec dbms_job.remove(23)
BEGIN dbms_job.remove(23); END;

      *
ERROR at line 1:
ORA-23421: job number 23 is not a job in the job queue
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_IJOB", line 529
ORA-06512: at "SYS.DBMS_JOB", line 215
ORA-06512: at line 1

注：dba_jobs 中的有三个（10g 中）表示用户的字段：
LOG_USER        提交任务的用户
PRIV_USER        赋予任务权限的用户
SCHEMA_USER  对任务作语法分析的用户模式
其中 PRIV_USER 表示 job 的拥有者。
Tom 有一个例子，演示了三个字段显示不同用户的情况（似乎很少碰到这种情况）：

ops$tkyte@ORA9IR2> grant create session, create procedure to a;
Grant succeeded.
 
ops$tkyte@ORA9IR2> grant create session, create procedure to b;
Grant succeeded.
 
ops$tkyte@ORA9IR2> grant create session to c;
Grant succeeded.
 
ops$tkyte@ORA9IR2> @connect a/a
ops$tkyte@ORA9IR2> set termout off
a@ORA9IR2> set termout on
a@ORA9IR2> create procedure p
  2  as
  3  begin
  4      null;
  5  end;
  6  /
 
Procedure created.
 
a@ORA9IR2> grant execute on p to b;
 
Grant succeeded.
 
a@ORA9IR2>
a@ORA9IR2> @connect b/b
a@ORA9IR2> set termout off
b@ORA9IR2> set termout on
b@ORA9IR2> create or replace procedure p
  2  as
  3     n number;
  4  begin
  5      execute immediate '
  6          declare
  7             n number;
  8          begin
  9             execute immediate ''alter session set current_schema=a'';
 10             dbms_job.submit(n,''p;'');
 11          end;';
 12  end;
 13  /
 
Procedure created.
 
b@ORA9IR2> grant execute on p to c;
 
Grant succeeded.
 
b@ORA9IR2>
b@ORA9IR2> @connect c/c
b@ORA9IR2> set termout off
c@ORA9IR2> set termout on
c@ORA9IR2> exec b.p
 
PL/SQL procedure successfully completed.
 
c@ORA9IR2> @connect b/b
c@ORA9IR2> set termout off
b@ORA9IR2> set termout on
b@ORA9IR2> select log_user, priv_user, schema_user from user_jobs;
 
LOG_USER PRIV_USE SCHEMA_USE
-------- -------- ----------
C        B        A

       接下来要引出正题，如何对非当前用户下的 job 操作？在网上搜索了一下，找到了 yangtingkun 的一篇总结 ，受益匪浅。

可以分两种方法：
1. 使用 Undocument 的包 DBMS_IJOB
该包应该是 Oracle 内部操作使用的，在文档上并没有公开，不过的确挺好用，jametong 已经对其作了总结，大部分过程和 DBMS_JOB 差不多，有一个区别要注意的是：DBMS_IJOB.submit 的 job 参数是 in 的，而 DBMS_JOB 中是 out 的。这里我做一个简单的实验，移除刚才创建的 job：

SQL> show user
USER is "SYS"
SQL> select job,log_user,priv_user,schema_user,last_date,next_date,what
  2  from dba_jobs
  3  where job=23;

JOB LOG_USER PRIV_USE SCHEMA_U LAST_DATE           NEXT_DATE           WHAT
--- -------- -------- -------- ------------------- ------------------- -----
 23 SKY      SKY      SKY      2007-04-24 00:32:56 2007-04-25 00:32:56 null;

SQL> exec dbms_ijob.remove(23)

PL/SQL procedure successfully completed.

SQL> select job,log_user,priv_user,schema_user,last_date,next_date,what
  2  from dba_jobs
  3  where job=23;

no rows selected

成功移除了。

2. 通过建立其他用户下的存储过程来执行 dbms_job 包
这种方法应该是比较正统的方法，虽然复杂一些。以下例子，转载自 yangtingkun 的 blog

SQL> show user
USER 为"YANGTK"
SQL> declare
  2  v_job number;
  3  begin
  4  dbms_job.submit(v_job, 'null;', sysdate, 'sysdate + 1');
  5  commit;
  6  end;
  7  /

 

PL/SQL 过程已成功完成。

SQL> select job, priv_user, what from user_jobs;

       JOB PRIV_USER                      WHAT
---------- ------------------------------ --------------------
        85 YANGTK                         null;

SQL> conn /@test4 as sysdba
已连接。
SQL> grant create session to b identified by b;

授权成功。

SQL> grant create any procedure, execute any procedure to b;

授权成功。

SQL> conn b/b@test4
已连接。
SQL> create procedure yangtk.p_execute(p_str in varchar2) as
  2  begin
  3  execute immediate p_str;
  4  end;
  5  /

过程已创建。

注意，如果需要以CREATE OR REPLACE方式创建存储过程，还需要ALTER ANY PROCEDURE权限。

SQL> begin
  2  yangtk.p_execute('begin dbms_job.remove(85); commit; end;');
  3  end;
  4  /

PL/SQL 过程已成功完成。

SQL> conn yangtk/yangtk@test4
已连接。
SQL> select job, priv_user, what from user_jobs;

未选定行

第二种方法还支持删除其他用户下数据库链的功能。

SQL> create database link yangtk.yangtingkun;

数据库链接已创建。

SQL> conn /@test4 as sysdba
已连接。
SQL> col db_link format a40
SQL> select owner, db_link from dba_db_links where owner = 'YANGTK';

OWNER                          DB_LINK
------------------------------ ---------------------------
YANGTK                         YANGTK.YANGTINGKUN

SQL> drop database link yangtk.yangtingkun;
drop database link yangtk.yangtingkun
                   *
ERROR 位于第 1 行:
ORA-02024: 未找到数据库链接

SQL> drop database link yangtk.yangtk.yangtingkun;
drop database link yangtk.yangtk.yangtingkun
                   *
ERROR 位于第 1 行:
ORA-02024: 未找到数据库链接

SQL> conn b/b@test4
已连接。
SQL> exec yangtk.p_execute('drop database link yangtk.yangtingkun')

PL/SQL 过程已成功完成。

SQL> conn yangtk/yangtk@test4
已连接。
SQL> select * from user_db_links;

未选定行

参考：
http://yangtingkun.itpub.net/post/468/24348
http://blog.itpub.net/post/5042/24344
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:633537913184

补充：
我们知道 dbms_job 中的过程操作之后都要 commit 才能永久生效（我一直对这点感到很奇怪，为什么 Oracle 不在过程中直接加上 commit），这点也适用于 dbms_ijob 包：

SQL> select job,log_user,priv_user,schema_user,last_date,next_date,what
  2  from dba_jobs;

JOB LOG_USER PRIV_USE SCHEMA_U LAST_DATE           NEXT_DATE           WHAT
--- -------- -------- -------- ------------------- ------------------- -----
  1 SYSMAN   SYSMAN   SYSMAN   2007-04-24 01:10:54 2007-04-24 01:11:54 EMD_M
                                                                       AINTE
                                                                       NANCE
                                                                       .EXEC
                                                                       UTE_E
                                                                       M_DBM
                                                                       S_JOB
                                                                       _PROC
                                                                       S();

 23 SKY      SKY      SKY      2007-04-24 00:32:56 2007-04-25 00:32:56 null;

SQL> exec dbms_ijob.next_date(23,sysdate+1)

PL/SQL procedure successfully completed.

SQL> select job,log_user,priv_user,schema_user,last_date,next_date,what
  2  from dba_jobs;

JOB LOG_USER PRIV_USE SCHEMA_U LAST_DATE           NEXT_DATE           WHAT
--- -------- -------- -------- ------------------- ------------------- -----
  1 SYSMAN   SYSMAN   SYSMAN   2007-04-24 01:20:35 2007-04-24 01:21:35 EMD_M
                                                                       AINTE
                                                                       NANCE
                                                                       .EXEC
                                                                       UTE_E
                                                                       M_DBM
                                                                       S_JOB
                                                                       _PROC
                                                                       S();

 23 SKY      SKY      SKY      2007-04-24 00:32:56 2007-04-25 01:21:31 null;

SQL> rollback;

Rollback complete.

SQL> select job,log_user,priv_user,schema_user,last_date,next_date,what
  2  from dba_jobs;

JOB LOG_USER PRIV_USE SCHEMA_U LAST_DATE           NEXT_DATE           WHAT
--- -------- -------- -------- ------------------- ------------------- -----
  1 SYSMAN   SYSMAN   SYSMAN   2007-04-24 01:21:40 2007-04-24 01:22:40 EMD_M
                                                                       AINTE
                                                                       NANCE
                                                                       .EXEC
                                                                       UTE_E
                                                                       M_DBM
                                                                       S_JOB
                                                                       _PROC
                                                                       S();

 23 SKY      SKY      SKY      2007-04-24 00:32:56 2007-04-25 00:32:56 null;

Today I wrote a simple procedure for a friend:

create or replace procedure P_QN_SERVICE is
  l_table_name varchar2(100);
  no_table EXCEPTION;
  PRAGMA EXCEPTION_INIT(no_table, -942);
begin
  l_table_name := 'v_dat_sdr_' || to_char(sysdate - 1, 'yyyymmdd');
  execute immediate 'insert into t_qn_service_bak(stime,service_id,cnt) ' ||
                    'select :date,service_id,count(*) from USBOSS2.' ||
                    l_table_name ||
                    '@usboss where service_id in (''0575SXZQ'',''0575MiYu'',''0575News'',''0575SMS'',''0575Test'',''0575QYDT'',''05759004050101'') group by service_id'
    using trunc(sysdate - 1);
  commit;
exception
  when no_table then
    dbms_output.putline('The view USBOSS2.' || l_table_name ||
                        'does not exists!');
end;

 Here I use a bind variable ":date". But when I execute this procedure, it raise an error "ORA-01745".

Here is the introduction about this error:

ORA-01745: invalid host/bind variable name
Cause: A colon in a bind variable or INTO specification was followed by an inappropriate name, perhaps a reserved word.
Action: Change the variable name and retry the operation.

After I changed the bind variable ":date" to ":l_date", it worked well.

Blog this case here to remind myself do not use reserved word as a bind variable.