oracle.rst
author Oleksandr Gavenko <gavenkoa@gmail.com>
Tue, 30 Sep 2014 19:09:41 +0300
changeset 1623 4496f9e49b7b
parent 1622 dec1fd4222e8
child 1624 baf11017516f
permissions -rw-r--r--
Reformat code.
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
1403
8f86324134d6 Oracle database development environment.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
diff changeset
     1
.. -*- coding: utf-8; -*-
8f86324134d6 Oracle database development environment.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
diff changeset
     2
.. include:: HEADER.rst
8f86324134d6 Oracle database development environment.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
diff changeset
     3
8f86324134d6 Oracle database development environment.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
diff changeset
     4
==================
8f86324134d6 Oracle database development environment.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
diff changeset
     5
 Oracle database.
8f86324134d6 Oracle database development environment.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
diff changeset
     6
==================
8f86324134d6 Oracle database development environment.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
diff changeset
     7
.. contents::
8f86324134d6 Oracle database development environment.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
diff changeset
     8
8f86324134d6 Oracle database development environment.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
diff changeset
     9
Oracle database development environment.
8f86324134d6 Oracle database development environment.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
diff changeset
    10
========================================
8f86324134d6 Oracle database development environment.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
diff changeset
    11
8f86324134d6 Oracle database development environment.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
diff changeset
    12
  http://en.wikipedia.org/wiki/Oracle_SQL_Developer
8f86324134d6 Oracle database development environment.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
diff changeset
    13
                Integrated development environment (IDE) for working with
8f86324134d6 Oracle database development environment.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
diff changeset
    14
                SQL/PLSql in Oracle databases.
8f86324134d6 Oracle database development environment.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
diff changeset
    15
  http://en.wikipedia.org/wiki/SQL*Plus
8f86324134d6 Oracle database development environment.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
diff changeset
    16
                An Oracle database client that can run SQL and PL/SQL commands
8f86324134d6 Oracle database development environment.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
diff changeset
    17
                and display their results.
8f86324134d6 Oracle database development environment.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
diff changeset
    18
  http://en.wikipedia.org/wiki/Oracle_Forms
8f86324134d6 Oracle database development environment.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
diff changeset
    19
                Is a software product for creating screens that interact with an
8f86324134d6 Oracle database development environment.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
diff changeset
    20
                Oracle database. It has an IDE including an object navigator,
8f86324134d6 Oracle database development environment.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
diff changeset
    21
                property sheet and code editor that uses PL/SQL.
8f86324134d6 Oracle database development environment.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
diff changeset
    22
  http://en.wikipedia.org/wiki/Oracle_JDeveloper
8f86324134d6 Oracle database development environment.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
diff changeset
    23
                JDeveloper is a freeware IDE supplied by Oracle Corporation. It
8f86324134d6 Oracle database development environment.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
diff changeset
    24
                offers features for development in Java, XML, SQL and PL/SQL,
8f86324134d6 Oracle database development environment.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
diff changeset
    25
                HTML, JavaScript, BPEL and PHP.
8f86324134d6 Oracle database development environment.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
diff changeset
    26
  http://en.wikipedia.org/wiki/Oracle_Reports
8f86324134d6 Oracle database development environment.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
diff changeset
    27
                Oracle Reports is a tool for developing reports against data
8f86324134d6 Oracle database development environment.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
diff changeset
    28
                stored in an Oracle database.
8f86324134d6 Oracle database development environment.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
diff changeset
    29
1494
f7e956de0cd7 Useful PL/SQL stub.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1486
diff changeset
    30
Useful PL/SQL stub.
f7e956de0cd7 Useful PL/SQL stub.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1486
diff changeset
    31
===================
f7e956de0cd7 Useful PL/SQL stub.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1486
diff changeset
    32
::
f7e956de0cd7 Useful PL/SQL stub.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1486
diff changeset
    33
f7e956de0cd7 Useful PL/SQL stub.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1486
diff changeset
    34
  set serveroutput on;
f7e956de0cd7 Useful PL/SQL stub.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1486
diff changeset
    35
  set autotrace on statistics;
f7e956de0cd7 Useful PL/SQL stub.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1486
diff changeset
    36
  set timing on;
f7e956de0cd7 Useful PL/SQL stub.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1486
diff changeset
    37
f7e956de0cd7 Useful PL/SQL stub.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1486
diff changeset
    38
  declare
f7e956de0cd7 Useful PL/SQL stub.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1486
diff changeset
    39
  begin
f7e956de0cd7 Useful PL/SQL stub.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1486
diff changeset
    40
    null;
f7e956de0cd7 Useful PL/SQL stub.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1486
diff changeset
    41
  end;
f7e956de0cd7 Useful PL/SQL stub.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1486
diff changeset
    42
  /
f7e956de0cd7 Useful PL/SQL stub.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1486
diff changeset
    43
1623
4496f9e49b7b Reformat code.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1622
diff changeset
    44
Database info.
4496f9e49b7b Reformat code.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1622
diff changeset
    45
==============
1462
27d4d6c15cb4 Информация о таблицах в БД Oracle.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1403
diff changeset
    46
1623
4496f9e49b7b Reformat code.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1622
diff changeset
    47
List of current user owned tables::
1462
27d4d6c15cb4 Информация о таблицах в БД Oracle.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1403
diff changeset
    48
1623
4496f9e49b7b Reformat code.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1622
diff changeset
    49
  select * from USER_TABLES;
1462
27d4d6c15cb4 Информация о таблицах в БД Oracle.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1403
diff changeset
    50
1623
4496f9e49b7b Reformat code.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1622
diff changeset
    51
List of current user table sizes::
1462
27d4d6c15cb4 Информация о таблицах в БД Oracle.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1403
diff changeset
    52
1623
4496f9e49b7b Reformat code.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1622
diff changeset
    53
  select SEGMENT_NAME, SEGMENT_TYPE, sum(BYTES) from USER_EXTENTS
4496f9e49b7b Reformat code.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1622
diff changeset
    54
    group by SEGMENT_NAME, SEGMENT_TYPE order by sum(BYTES);
1462
27d4d6c15cb4 Информация о таблицах в БД Oracle.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1403
diff changeset
    55
1623
4496f9e49b7b Reformat code.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1622
diff changeset
    56
  select sum(BYTES) from USER_EXTENTS;
1462
27d4d6c15cb4 Информация о таблицах в БД Oracle.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1403
diff changeset
    57
1623
4496f9e49b7b Reformat code.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1622
diff changeset
    58
Tables indexes::
1462
27d4d6c15cb4 Информация о таблицах в БД Oracle.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1403
diff changeset
    59
1623
4496f9e49b7b Reformat code.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1622
diff changeset
    60
  select * from USER_INDEXES order by TABLE_NAME;
1462
27d4d6c15cb4 Информация о таблицах в БД Oracle.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1403
diff changeset
    61
1623
4496f9e49b7b Reformat code.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1622
diff changeset
    62
List of index sizes::
1462
27d4d6c15cb4 Информация о таблицах в БД Oracle.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1403
diff changeset
    63
27d4d6c15cb4 Информация о таблицах в БД Oracle.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1403
diff changeset
    64
  select index_name, table_name, sum(user_extents.bytes) as bytes from user_indexes
27d4d6c15cb4 Информация о таблицах в БД Oracle.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1403
diff changeset
    65
    left outer join user_extents on user_extents.segment_name = table_name
27d4d6c15cb4 Информация о таблицах в БД Oracle.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1403
diff changeset
    66
    group by index_name, table_name
27d4d6c15cb4 Информация о таблицах в БД Oracle.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1403
diff changeset
    67
    order by table_name;
27d4d6c15cb4 Информация о таблицах в БД Oracle.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1403
diff changeset
    68
1623
4496f9e49b7b Reformat code.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1622
diff changeset
    69
List of currenct user constraints::
1462
27d4d6c15cb4 Информация о таблицах в БД Oracle.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1403
diff changeset
    70
1623
4496f9e49b7b Reformat code.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1622
diff changeset
    71
  select * from USER_CONSTRAINTS;
1462
27d4d6c15cb4 Информация о таблицах в БД Oracle.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1403
diff changeset
    72
1623
4496f9e49b7b Reformat code.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1622
diff changeset
    73
List of tablespaces::
1462
27d4d6c15cb4 Информация о таблицах в БД Oracle.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1403
diff changeset
    74
1623
4496f9e49b7b Reformat code.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1622
diff changeset
    75
  select distinct TABLESPACE_NAME from USER_TABLES;
1462
27d4d6c15cb4 Информация о таблицах в БД Oracle.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1403
diff changeset
    76
1622
dec1fd4222e8 List of current user permissions.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1583
diff changeset
    77
List of current user permissions::
dec1fd4222e8 List of current user permissions.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1583
diff changeset
    78
1623
4496f9e49b7b Reformat code.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1622
diff changeset
    79
  select * from SESSION_PRIVS;
1622
dec1fd4222e8 List of current user permissions.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1583
diff changeset
    80
dec1fd4222e8 List of current user permissions.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1583
diff changeset
    81
List of user permissions to tables::
dec1fd4222e8 List of current user permissions.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1583
diff changeset
    82
dec1fd4222e8 List of current user permissions.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1583
diff changeset
    83
  select * from ALL_TAB_PRIVS where TABLE_SCHEMA not like '%SYS' and TABLE_SCHEMA not like 'SYS%';
dec1fd4222e8 List of current user permissions.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1583
diff changeset
    84
1483
1475d464e8a8 Timing info about last queries.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1482
diff changeset
    85
Profiling.
1475d464e8a8 Timing info about last queries.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1482
diff changeset
    86
==========
1475d464e8a8 Timing info about last queries.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1482
diff changeset
    87
1475d464e8a8 Timing info about last queries.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1482
diff changeset
    88
Timing info about last queries::
1475d464e8a8 Timing info about last queries.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1482
diff changeset
    89
1475d464e8a8 Timing info about last queries.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1482
diff changeset
    90
  select LAST_LOAD_TIME, ELAPSED_TIME, MODULE, SQL_TEXT elasped from v$sql
1475d464e8a8 Timing info about last queries.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1482
diff changeset
    91
    order by LAST_LOAD_TIME desc
1475d464e8a8 Timing info about last queries.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1482
diff changeset
    92
1486
f3be7476145d Improved version of code.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1485
diff changeset
    93
Improved version of above code::
f3be7476145d Improved version of code.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1485
diff changeset
    94
f3be7476145d Improved version of code.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1485
diff changeset
    95
  column LAST_LOAD_TIME format a20;
f3be7476145d Improved version of code.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1485
diff changeset
    96
  column TIME format a20;
f3be7476145d Improved version of code.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1485
diff changeset
    97
  column MODULE format a10;
f3be7476145d Improved version of code.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1485
diff changeset
    98
  column SQL_TEXT format a60;
f3be7476145d Improved version of code.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1485
diff changeset
    99
f3be7476145d Improved version of code.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1485
diff changeset
   100
  set autotrace off;
f3be7476145d Improved version of code.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1485
diff changeset
   101
  set timing off;
f3be7476145d Improved version of code.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1485
diff changeset
   102
f3be7476145d Improved version of code.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1485
diff changeset
   103
  select * from (
f3be7476145d Improved version of code.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1485
diff changeset
   104
    select LAST_LOAD_TIME, to_char(ELAPSED_TIME/1000, '999,999,999.000') || ' ms' as TIME, MODULE, SQL_TEXT from SYS."V_\$SQL"
f3be7476145d Improved version of code.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1485
diff changeset
   105
      where SQL_TEXT like '%BATCH_BRANCHES%'
f3be7476145d Improved version of code.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1485
diff changeset
   106
      order by LAST_LOAD_TIME desc
f3be7476145d Improved version of code.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1485
diff changeset
   107
    ) where ROWNUM <= 5;
f3be7476145d Improved version of code.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1485
diff changeset
   108
1484
20964d8677d7 Profiling.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1483
diff changeset
   109
In SQL/Plus::
20964d8677d7 Profiling.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1483
diff changeset
   110
20964d8677d7 Profiling.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1483
diff changeset
   111
  SET TIMING ON;
20964d8677d7 Profiling.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1483
diff changeset
   112
  -- do stuff
20964d8677d7 Profiling.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1483
diff changeset
   113
  SET TIMING OFF;
20964d8677d7 Profiling.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1483
diff changeset
   114
20964d8677d7 Profiling.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1483
diff changeset
   115
or::
20964d8677d7 Profiling.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1483
diff changeset
   116
20964d8677d7 Profiling.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1483
diff changeset
   117
  set serveroutput on
20964d8677d7 Profiling.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1483
diff changeset
   118
  variable n number
20964d8677d7 Profiling.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1483
diff changeset
   119
  exec :n := dbms_utility.get_time;
20964d8677d7 Profiling.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1483
diff changeset
   120
  select ......
20964d8677d7 Profiling.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1483
diff changeset
   121
  exec dbms_output.put_line( (dbms_utility.get_time-:n)/100) || ' seconds....' );
20964d8677d7 Profiling.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1483
diff changeset
   122
20964d8677d7 Profiling.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1483
diff changeset
   123
See:
20964d8677d7 Profiling.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1483
diff changeset
   124
20964d8677d7 Profiling.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1483
diff changeset
   125
  http://docs.oracle.com/cd/B19306_01/server.102/b14237/dynviews_2113.htm
20964d8677d7 Profiling.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1483
diff changeset
   126
                $SQL lists statistics on shared SQL area without the GROUP BY
20964d8677d7 Profiling.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1483
diff changeset
   127
                clause.
20964d8677d7 Profiling.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1483
diff changeset
   128
1485
752e99dbb016 Last table modification time.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1484
diff changeset
   129
Last table modification time.
752e99dbb016 Last table modification time.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1484
diff changeset
   130
=============================
752e99dbb016 Last table modification time.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1484
diff changeset
   131
::
752e99dbb016 Last table modification time.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1484
diff changeset
   132
752e99dbb016 Last table modification time.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1484
diff changeset
   133
  select max(scn_to_timestamp(ora_rowscn)) from TBL;
752e99dbb016 Last table modification time.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1484
diff changeset
   134
752e99dbb016 Last table modification time.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1484
diff changeset
   135
  select timestamp from all_tab_modifications where table_owner = 'OWNER';
752e99dbb016 Last table modification time.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1484
diff changeset
   136
  select timestamp from all_tab_modifications where table_name = 'TABLE';
1484
20964d8677d7 Profiling.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1483
diff changeset
   137
1482
1a012d9fe613 List of Oracle Reserved Words.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1462
diff changeset
   138
List of Oracle Reserved Words.
1a012d9fe613 List of Oracle Reserved Words.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1462
diff changeset
   139
==============================
1a012d9fe613 List of Oracle Reserved Words.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1462
diff changeset
   140
1a012d9fe613 List of Oracle Reserved Words.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1462
diff changeset
   141
 * http://docs.oracle.com/cd/B19306_01/em.102/b40103/app_oracle_reserved_words.htm
1496
7679a0100061 Adjust date format.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1495
diff changeset
   142
7679a0100061 Adjust date format.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1495
diff changeset
   143
Adjust date format.
7679a0100061 Adjust date format.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1495
diff changeset
   144
===================
7679a0100061 Adjust date format.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1495
diff changeset
   145
::
7679a0100061 Adjust date format.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1495
diff changeset
   146
7679a0100061 Adjust date format.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1495
diff changeset
   147
  column parameter format a32;
7679a0100061 Adjust date format.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1495
diff changeset
   148
  column value format a32;
7679a0100061 Adjust date format.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1495
diff changeset
   149
  select parameter, value from v$nls_parameters;
7679a0100061 Adjust date format.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1495
diff changeset
   150
7679a0100061 Adjust date format.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1495
diff changeset
   151
  alter session set NLS_DATE_FORMAT = 'yyyy-mm-dd HH:MI:SS';
7679a0100061 Adjust date format.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1495
diff changeset
   152
  alter session set NLS_TIMESTAMP_FORMAT = 'MI:SS.FF6';
7679a0100061 Adjust date format.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1495
diff changeset
   153
  alter session set NLS_TIME_FORMAT = 'HH24:MI:SS.FF6';
7679a0100061 Adjust date format.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1495
diff changeset
   154
7679a0100061 Adjust date format.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1495
diff changeset
   155
  select sysdate from dual;
7679a0100061 Adjust date format.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1495
diff changeset
   156
1569
650683205401 Working with SQL/Plus.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1496
diff changeset
   157
Working with SQL/Plus.
650683205401 Working with SQL/Plus.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1496
diff changeset
   158
======================
650683205401 Working with SQL/Plus.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1496
diff changeset
   159
650683205401 Working with SQL/Plus.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1496
diff changeset
   160
Show error details::
650683205401 Working with SQL/Plus.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1496
diff changeset
   161
650683205401 Working with SQL/Plus.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1496
diff changeset
   162
  show errors;
650683205401 Working with SQL/Plus.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1496
diff changeset
   163
1583
46a6bc1e9f2a Dump how exactly field stored
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1569
diff changeset
   164
Dump how exactly field stored::
1569
650683205401 Working with SQL/Plus.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1496
diff changeset
   165
1583
46a6bc1e9f2a Dump how exactly field stored
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1569
diff changeset
   166
  select dump(date '2009-08-07') from dual;
46a6bc1e9f2a Dump how exactly field stored
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1569
diff changeset
   167