Mercurial Mercurial > tips / comparison
summary | shortlog | changelog | graph | tags | bookmarks | branches | files | changeset | file | latest | revisions | annotate | diff | comparison | raw | help
Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
oracle.rst
changeset 2231 2813a509381c
parent 2226 d72d3ce66806
child 2243 eccc5846e1dd
equal deleted inserted replaced
2230:9e6ad6607a9e 2231:2813a509381c 
    27   Oracle Reports is a tool for developing reports against data
 
    27   Oracle Reports is a tool for developing reports against data
 
    28   stored in an Oracle database.
 
    28   stored in an Oracle database.
 
    29 
    29 
    30 Useful PL/SQL stubs
 
    30 Useful PL/SQL stubs
 
    31 ===================
 
    31 ===================
 
    32 ::
 
    32 
    33 
    33 Declare executable block::
 
    34   set autotrace on statistics;
 
       
    35   set timing on;
 
       
    36 
    34 
    37   declare
 
    35   declare
 
    38   begin
 
    36   begin
 
    39     null;
 
    37     null;
 
    40   end;
 
    38   end;
 
    47   begin
 
    45   begin
 
    48     select 1 into x from dual;
 
    46     select 1 into x from dual;
 
    49   end;
 
    47   end;
 
    50   /
 
    48   /
 
    51 
    49 
    52 Enabling printing::
 
       
    53 
       
    54   set serveroutput on;
 
       
    55   exec DBMS_OUTPUT.PUT_LINE('Hello');
 
       
    56   exec DBMS_OUTPUT.DISABLE();
 
       
    57   exec DBMS_OUTPUT.PUT_LINE('Silence');
 
       
    58   exec DBMS_OUTPUT.ENABLE();
 
       
    59 
       
    60 Call procedure::
 
    50 Call procedure::
 
    61 
    51 
    62   create or replace procedure MY_PROC as
 
    52   create or replace procedure MY_PROC as
 
    63   begin
 
    53   begin
 
    64     null;
 
    54     null;
 
    82     x NUMBER;
 
    72     x NUMBER;
 
    83   begin
 
    73   begin
 
    84     ret := MY_FUN();
 
    74     ret := MY_FUN();
 
    85   end;
 
    75   end;
 
    86   /
 
    76   /
 
       
    77 
       
    78 Common client options
 
       
    79 =====================
 
       
    80 
       
    81 Following options supports Sql*Plus, SQLcl, Oracle Developer.
 
       
    82 
       
    83 Enabling printing::
 
       
    84 
       
    85   set serveroutput on;
 
       
    86   exec DBMS_OUTPUT.PUT_LINE('Hello');
 
       
    87   exec DBMS_OUTPUT.DISABLE();
 
       
    88   exec DBMS_OUTPUT.PUT_LINE('Silence');
 
       
    89   exec DBMS_OUTPUT.ENABLE();
 
       
    90 
       
    91 Enable printing of query execution time::
 
       
    92 
       
    93   set timing on;
 
       
    94 
       
    95 Sql*Plus client options
 
       
    96 =======================
 
       
    97 
       
    98 Enable printing execution plan stats::
 
       
    99 
       
   100   set autotrace on;
 
       
   101   set autotrace off;
 
       
   102   set autotrace on statistics;
 
       
   103   set autotrace on explain;
 
       
   104 
       
   105 Disable printing of ``select`` result, show only stats::
 
       
   106 
       
   107   set autotrace traceonly;
 
       
   108   set autotrace traceonly on explain;
 
       
   109 
       
   110 Adjust date format.
 
       
   111 ===================
 
       
   112 ::
 
       
   113 
       
   114   column parameter format a32;
 
       
   115   column value format a32;
 
       
   116   select parameter, value from v$nls_parameters;
 
       
   117 
       
   118   alter session set NLS_DATE_FORMAT = 'yyyy-mm-dd HH:MI:SS';
 
       
   119   alter session set NLS_TIMESTAMP_FORMAT = 'MI:SS.FF6';
 
       
   120   alter session set NLS_TIME_FORMAT = 'HH24:MI:SS.FF6';
 
       
   121 
       
   122   alter session set TIME_ZONE = '+06:00';
 
       
   123 
       
   124   select sysdate from dual;
 
       
   125 
       
   126 Working with SQL/Plus
 
       
   127 =====================
 
       
   128 
       
   129 Show error details::
 
       
   130 
       
   131   show errors;
 
       
   132 
       
   133 Modify output format::
 
       
   134 
       
   135   set pagesize 40;
 
       
   136   set linesize 400;
 
    87 
   137 
    88 Database info
 
   138 Database info
 
    89 =============
 
   139 =============
 
    90 
   140 
    91 List of users::
 
   141 List of users::
 
   317   grant CREATE TABLE, CREATE SEQUENCE, CREATE VIEW, CREATE SYNONYM to BOB;
 
   367   grant CREATE TABLE, CREATE SEQUENCE, CREATE VIEW, CREATE SYNONYM to BOB;
 
   318 
   368 
   319 Profiling
 
   369 Profiling
 
   320 =========
 
   370 =========
 
   321 
   371 
       
   372 ``explain plan for`` can be used to show probable execution plan for query::
 
       
   373 
       
   374   explain plan for select 1 from dual;
 
       
   375 
       
   376 ``plan_table`` should be examined to display result::
 
       
   377 
       
   378   select * from table(dbms_xplan.display);
 
       
   379 
       
   380 To get actual execution plan run following after executing query::
 
       
   381 
       
   382   select * from table(dbms_xplan.display_cursor);
 
       
   383   select * from table(dbms_xplan.display_cursor(format => 'basic'));
 
       
   384   select * from table(dbms_xplan.display_cursor(format => 'typical'));
 
       
   385   select * from table(dbms_xplan.display_cursor(format => 'all'));
 
       
   386 
   322 Last queries' execution stats are available through ``v$sql`` view::
 
   387 Last queries' execution stats are available through ``v$sql`` view::
 
   323 
   388 
   324   grant select on v_$sql to BOB;
 
   389   grant select on v_$sql to BOB;
 
   325 
   390 
   326   select * from v$sql;
 
   391   select * from v$sql;
 
       
   392   select * from v$sql_plan;
 
   327 
   393 
   328   select LAST_LOAD_TIME, ELAPSED_TIME, MODULE, SQL_TEXT elasped from v$sql
 
   394   select LAST_LOAD_TIME, ELAPSED_TIME, MODULE, SQL_TEXT elasped from v$sql
 
   329     order by LAST_LOAD_TIME desc
 
   395     order by LAST_LOAD_TIME desc
 
   330 
   396 
   331 Improved version of above code::
 
   397 Improved version of above code::
 
   363 press ``Ctrl+End``.
 
   429 press ``Ctrl+End``.
 
   364 
   430 
   365 Alternatively you may wrap you query with (and optionally use hint to disable
 
   431 Alternatively you may wrap you query with (and optionally use hint to disable
 
   366 optimizations??)::
 
   432 optimizations??)::
 
   367 
   433 
   368   select (*) from ( ... ORIGINAL QUERY ... );
 
   434   select count(*) from ( ... ORIGINAL QUERY ... );
 
   369 
   435 
   370 Another option is::
 
   436 Another option is::
 
   371 
   437 
   372   delete plan_table;
 
   438   delete plan_table;
 
   373   explain plan for ... SQL statement ...;
 
   439   explain plan for ... SQL statement ...;
 
   401   select timestamp from all_tab_modifications where table_name = 'TABLE';
 
   467   select timestamp from all_tab_modifications where table_name = 'TABLE';
 
   402 
   468 
   403 List of Oracle Reserved Words.
 
   469 List of Oracle Reserved Words.
 
   404 ==============================
 
   470 ==============================
 
   405 
   471 
   406  * http://docs.oracle.com/cd/B19306_01/em.102/b40103/app_oracle_reserved_words.htm
 
   472 * http://docs.oracle.com/cd/B19306_01/em.102/b40103/app_oracle_reserved_words.htm
 
   407 
   473 
   408 Find time zone
 
   474 Find time zone
 
   409 ==============
 
   475 ==============
 
   410 
   476 
   411 Set TZ data formt::
 
   477 Set TZ data formt::
 
   437   select DBTIMEZONE from dual;
 
   503   select DBTIMEZONE from dual;
 
   438 
   504 
   439 Find time at timezone::
 
   505 Find time at timezone::
 
   440 
   506 
   441   select SYSTIMESTAMP at time zone 'GMT' from dual;
 
   507   select SYSTIMESTAMP at time zone 'GMT' from dual;
 
   442 
       
   443 Adjust date format.
 
       
   444 ===================
 
       
   445 ::
 
       
   446 
       
   447   column parameter format a32;
 
       
   448   column value format a32;
 
       
   449   select parameter, value from v$nls_parameters;
 
       
   450 
       
   451   alter session set NLS_DATE_FORMAT = 'yyyy-mm-dd HH:MI:SS';
 
       
   452   alter session set NLS_TIMESTAMP_FORMAT = 'MI:SS.FF6';
 
       
   453   alter session set NLS_TIME_FORMAT = 'HH24:MI:SS.FF6';
 
       
   454 
       
   455   alter session set TIME_ZONE = '+06:00';
 
       
   456 
       
   457   select sysdate from dual;
 
       
   458 
       
   459 Working with SQL/Plus.
 
       
   460 ======================
 
       
   461 
       
   462 Show error details::
 
       
   463 
       
   464   show errors;
 
       
   465 
       
   466 Modify output format::
 
       
   467 
       
   468   set pagesize 40;
 
       
   469   set linesize 400;
tips