1.关于define表示定义
2.sql%rowcount用于记录修改的条数,必须放在一个CUD语句后面执行,无法在select中使用。
3.两种调用过程的关键字:exec和call
两者区别:
(1)exec是sqlplus命令,只能在sqlplus中使用;call为SQL命令,没有限制.
(2)存储过程没有参数时,exec可以直接跟过程名(可以省略()),但call则必须带上()
4.复制表数据,用where 1=0等;
5.关于&和&&的区别:看http://blog.csdn.net/studyvcmfc/article/details/7055258;
主要区别在与:
&username和&&usernae都会在sqlplus执行的时候,会提示你输入一个username对应的值。
但&输入一次后,下次遇到还要继续输入;而&&输入一次后,下次遇到会默认选择上一次输入的内容。
6.授权:grant
回收权限:remvoke
define numrows=10000000 --definde 表示定义变量 drop table big_table purge; create table big_table as select rownum id, OWNER, OBJECT_NAME, SUBOBJECT_NAME, OBJECT_ID, DATA_OBJECT_ID, OBJECT_TYPE, CREATED, LAST_DDL_TIME, TIMESTAMP, STATUS, TEMPORARY, GENERATED, SECONDARY, NAMESPACE, EDITION_NAME from all_objects where 1=0 / alter table big_table nologging; declare l_cnt number; l_rows number := &numrows; begin insert /*+ append */ into big_table select rownum id, OWNER, OBJECT_NAME, SUBOBJECT_NAME, OBJECT_ID, DATA_OBJECT_ID, OBJECT_TYPE, CREATED, LAST_DDL_TIME, TIMESTAMP, STATUS, TEMPORARY, GENERATED, SECONDARY, NAMESPACE, EDITION_NAME from all_objects where rownum <= &numrows; --numrows的值为10000000 -- l_cnt := sql%rowcount;--sql%rowcount用于记录修改的条数 commit; while (l_cnt < l_rows) loop insert /*+ APPEND */ into big_table select rownum+l_cnt,OWNER, OBJECT_NAME, SUBOBJECT_NAME, OBJECT_ID, DATA_OBJECT_ID, OBJECT_TYPE, CREATED, LAST_DDL_TIME, TIMESTAMP, STATUS, TEMPORARY, GENERATED, SECONDARY, NAMESPACE, EDITION_NAME from big_table a where rownum <= l_rows-l_cnt; l_cnt := l_cnt + sql%rowcount; commit; end loop; end; / alter table big_table add constraint big_table_pk primary key(id); exec dbms_stats.gather_table_stats( user, 'BIG_TABLE', estimate_percent=> 1);--exec就是调用存储过程,1但是exec是sqlplus命令,只能在sqlplus中使用;call为SQL命令,没有限制. 2存储过程没有参数时,exec可以直接跟过程名(可以省略()),但call则必须带上().
connect / as sysdba define username=eoda define usernamepwd=foo create user &&username identified by &&usernamepwd; grant dba to &&username; grant execute on dbms_stats to &&username;--给dbms_stats过程授予权限 grant select on V_$STATNAME to &&username;--赋予查询权限 grant select on V_$MYSTAT to &&username; grant select on V_$LATCH to &&username; grant select on V_$TIMER to &&username; conn &&username/&&usernamepwd