author | Oleksandr Gavenko <gavenkoa@gmail.com> |
Sat, 25 May 2013 11:23:08 +0300 | |
changeset 1492 | 2f1d56eb36ca |
parent 1486 | f3be7476145d |
child 1494 | f7e956de0cd7 |
permissions | -rw-r--r-- |
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 |
|
1462
27d4d6c15cb4
Информация о таблицах в БД Oracle.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
1403
diff
changeset
|
30 |
Информация о таблицах в БД Oracle. |
27d4d6c15cb4
Информация о таблицах в БД Oracle.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
1403
diff
changeset
|
31 |
================================== |
27d4d6c15cb4
Информация о таблицах в БД Oracle.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
1403
diff
changeset
|
32 |
|
27d4d6c15cb4
Информация о таблицах в БД Oracle.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
1403
diff
changeset
|
33 |
Список таблиц:: |
27d4d6c15cb4
Информация о таблицах в БД Oracle.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
1403
diff
changeset
|
34 |
|
27d4d6c15cb4
Информация о таблицах в БД Oracle.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
1403
diff
changeset
|
35 |
select * from user_tables; |
27d4d6c15cb4
Информация о таблицах в БД Oracle.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
1403
diff
changeset
|
36 |
|
27d4d6c15cb4
Информация о таблицах в БД Oracle.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
1403
diff
changeset
|
37 |
Занимаемый размер таблиц и индексов:: |
27d4d6c15cb4
Информация о таблицах в БД Oracle.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
1403
diff
changeset
|
38 |
|
27d4d6c15cb4
Информация о таблицах в БД Oracle.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
1403
diff
changeset
|
39 |
select segment_name, segment_type, sum(bytes) from user_extents |
27d4d6c15cb4
Информация о таблицах в БД Oracle.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
1403
diff
changeset
|
40 |
group by segment_name, segment_type order by sum(bytes); |
27d4d6c15cb4
Информация о таблицах в БД Oracle.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
1403
diff
changeset
|
41 |
|
27d4d6c15cb4
Информация о таблицах в БД Oracle.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
1403
diff
changeset
|
42 |
select sum(bytes) from user_extents; |
27d4d6c15cb4
Информация о таблицах в БД Oracle.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
1403
diff
changeset
|
43 |
|
27d4d6c15cb4
Информация о таблицах в БД Oracle.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
1403
diff
changeset
|
44 |
Список индексов по таблицам:: |
27d4d6c15cb4
Информация о таблицах в БД Oracle.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
1403
diff
changeset
|
45 |
|
27d4d6c15cb4
Информация о таблицах в БД Oracle.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
1403
diff
changeset
|
46 |
select * from user_indexes order by table_name; |
27d4d6c15cb4
Информация о таблицах в БД Oracle.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
1403
diff
changeset
|
47 |
|
27d4d6c15cb4
Информация о таблицах в БД Oracle.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
1403
diff
changeset
|
48 |
Список размеров индексов по таблицам:: |
27d4d6c15cb4
Информация о таблицах в БД Oracle.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
1403
diff
changeset
|
49 |
|
27d4d6c15cb4
Информация о таблицах в БД Oracle.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
1403
diff
changeset
|
50 |
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
|
51 |
left outer join user_extents on user_extents.segment_name = table_name |
27d4d6c15cb4
Информация о таблицах в БД Oracle.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
1403
diff
changeset
|
52 |
group by index_name, table_name |
27d4d6c15cb4
Информация о таблицах в БД Oracle.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
1403
diff
changeset
|
53 |
order by table_name; |
27d4d6c15cb4
Информация о таблицах в БД Oracle.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
1403
diff
changeset
|
54 |
|
27d4d6c15cb4
Информация о таблицах в БД Oracle.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
1403
diff
changeset
|
55 |
Список ограничений:: |
27d4d6c15cb4
Информация о таблицах в БД Oracle.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
1403
diff
changeset
|
56 |
|
27d4d6c15cb4
Информация о таблицах в БД Oracle.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
1403
diff
changeset
|
57 |
select * from user_constraints; |
27d4d6c15cb4
Информация о таблицах в БД Oracle.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
1403
diff
changeset
|
58 |
|
27d4d6c15cb4
Информация о таблицах в БД Oracle.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
1403
diff
changeset
|
59 |
Используемое пространство таблиц:: |
27d4d6c15cb4
Информация о таблицах в БД Oracle.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
1403
diff
changeset
|
60 |
|
27d4d6c15cb4
Информация о таблицах в БД Oracle.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
1403
diff
changeset
|
61 |
select distinct tablespace_name from user_tables; |
27d4d6c15cb4
Информация о таблицах в БД Oracle.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
1403
diff
changeset
|
62 |
|
1483
1475d464e8a8
Timing info about last queries.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
1482
diff
changeset
|
63 |
Profiling. |
1475d464e8a8
Timing info about last queries.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
1482
diff
changeset
|
64 |
========== |
1475d464e8a8
Timing info about last queries.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
1482
diff
changeset
|
65 |
|
1475d464e8a8
Timing info about last queries.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
1482
diff
changeset
|
66 |
Timing info about last queries:: |
1475d464e8a8
Timing info about last queries.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
1482
diff
changeset
|
67 |
|
1475d464e8a8
Timing info about last queries.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
1482
diff
changeset
|
68 |
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
|
69 |
order by LAST_LOAD_TIME desc |
1475d464e8a8
Timing info about last queries.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
1482
diff
changeset
|
70 |
|
1486
f3be7476145d
Improved version of code.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
1485
diff
changeset
|
71 |
Improved version of above code:: |
f3be7476145d
Improved version of code.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
1485
diff
changeset
|
72 |
|
f3be7476145d
Improved version of code.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
1485
diff
changeset
|
73 |
column LAST_LOAD_TIME format a20; |
f3be7476145d
Improved version of code.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
1485
diff
changeset
|
74 |
column TIME format a20; |
f3be7476145d
Improved version of code.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
1485
diff
changeset
|
75 |
column MODULE format a10; |
f3be7476145d
Improved version of code.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
1485
diff
changeset
|
76 |
column SQL_TEXT format a60; |
f3be7476145d
Improved version of code.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
1485
diff
changeset
|
77 |
|
f3be7476145d
Improved version of code.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
1485
diff
changeset
|
78 |
set autotrace off; |
f3be7476145d
Improved version of code.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
1485
diff
changeset
|
79 |
set timing off; |
f3be7476145d
Improved version of code.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
1485
diff
changeset
|
80 |
|
f3be7476145d
Improved version of code.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
1485
diff
changeset
|
81 |
select * from ( |
f3be7476145d
Improved version of code.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
1485
diff
changeset
|
82 |
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
|
83 |
where SQL_TEXT like '%BATCH_BRANCHES%' |
f3be7476145d
Improved version of code.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
1485
diff
changeset
|
84 |
order by LAST_LOAD_TIME desc |
f3be7476145d
Improved version of code.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
1485
diff
changeset
|
85 |
) where ROWNUM <= 5; |
f3be7476145d
Improved version of code.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
1485
diff
changeset
|
86 |
|
1484 | 87 |
In SQL/Plus:: |
88 |
||
89 |
SET TIMING ON; |
|
90 |
-- do stuff |
|
91 |
SET TIMING OFF; |
|
92 |
||
93 |
or:: |
|
94 |
||
95 |
set serveroutput on |
|
96 |
variable n number |
|
97 |
exec :n := dbms_utility.get_time; |
|
98 |
select ...... |
|
99 |
exec dbms_output.put_line( (dbms_utility.get_time-:n)/100) || ' seconds....' ); |
|
100 |
||
101 |
See: |
|
102 |
||
103 |
http://docs.oracle.com/cd/B19306_01/server.102/b14237/dynviews_2113.htm |
|
104 |
$SQL lists statistics on shared SQL area without the GROUP BY |
|
105 |
clause. |
|
106 |
||
1485
752e99dbb016
Last table modification time.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
1484
diff
changeset
|
107 |
Last table modification time. |
752e99dbb016
Last table modification time.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
1484
diff
changeset
|
108 |
============================= |
752e99dbb016
Last table modification time.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
1484
diff
changeset
|
109 |
:: |
752e99dbb016
Last table modification time.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
1484
diff
changeset
|
110 |
|
752e99dbb016
Last table modification time.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
1484
diff
changeset
|
111 |
select max(scn_to_timestamp(ora_rowscn)) from TBL; |
752e99dbb016
Last table modification time.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
1484
diff
changeset
|
112 |
|
752e99dbb016
Last table modification time.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
1484
diff
changeset
|
113 |
select timestamp from all_tab_modifications where table_owner = 'OWNER'; |
752e99dbb016
Last table modification time.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
1484
diff
changeset
|
114 |
select timestamp from all_tab_modifications where table_name = 'TABLE'; |
1484 | 115 |
|
1482
1a012d9fe613
List of Oracle Reserved Words.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
1462
diff
changeset
|
116 |
List of Oracle Reserved Words. |
1a012d9fe613
List of Oracle Reserved Words.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
1462
diff
changeset
|
117 |
============================== |
1a012d9fe613
List of Oracle Reserved Words.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
1462
diff
changeset
|
118 |
|
1a012d9fe613
List of Oracle Reserved Words.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
1462
diff
changeset
|
119 |
* http://docs.oracle.com/cd/B19306_01/em.102/b40103/app_oracle_reserved_words.htm |