Hemant K Chitale

Subscribe to Hemant K Chitale feed
I am an Oracle Database Specialist in Singapore.
get an rss feed of this blog at http://hemantoracledba.blogspot.com/feeds/posts/default?alt=rss
follow me on twitter : @HemantKChitale
Updated: 3 hours 18 min ago

SQLLoader DIRECT option and Unique Index

Mon, 2016-09-26 09:59
The DIRECT parameter for SQLLoader Command-Line enables Direct Path Load which uses a Direct Path API instead of a regular INSERT statement to load data into the target table.

However, one needs to know how it handles a Unique Index on the target table.
It actually leaves the  UNUSABLE if, duplicate values are loaded. The Index rebuild fails but the duplicate values remain in the table.

Here is a quick demo  (this in 12.1.0.2 MultiTenant). I first setup the target table with a Unique Index.

[oracle@ora12102 Desktop]$ sqlplus hemant/hemant@PDB1

SQL*Plus: Release 12.1.0.2.0 Production on Mon Sep 26 22:36:51 2016

Copyright (c) 1982, 2014, Oracle. All rights reserved.

Last Successful login time: Mon Sep 26 2016 22:26:16 +08:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

SQL> show con_id

CON_ID
------------------------------
3
SQL> create table test_sqlldr_direct
2 (id_column number,
3 data_column varchar2(15))
4 /

Table created.

SQL> create unique index test_sqlldr_direct_u1 on test_sqlldr_direct(id_column);

Index created.

SQL> insert into test_sqlldr_direct values (1, 'First Row');

1 row created.

SQL> commit;

Commit complete.

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
[oracle@ora12102 Desktop]$

Next, I setup the datafile with a duplicate record and the controlfile.

[oracle@ora12102 Desktop]$ ls -l
total 8
-rw-r--r-- 1 oracle oinstall 40 Sep 26 22:40 load_data.dat
-rw-r--r-- 1 oracle oinstall 165 Sep 26 22:45 load_control.ctl
[oracle@ora12102 Desktop]$ cat load_data.dat
2,'Second Row'
3,'Third Row'
3,'Oops !'
[oracle@ora12102 Desktop]$ cat load_control.ctl
LOAD DATA
INFILE load_data.dat
APPEND INTO TABLE TEST_SQLLDR_DIRECT
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
id_column,
data_column)
[oracle@ora12102 Desktop]$

I am now ready to run a Direct Path Load.

[oracle@ora12102 Desktop]$ sqlldr hemant/hemant@pdb1 control=load_control.ctl direct=TRUE

SQL*Loader: Release 12.1.0.2.0 - Production on Mon Sep 26 22:47:09 2016

Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.

Path used: Direct

Load completed - logical record count 3.

Table TEST_SQLLDR_DIRECT:
3 Rows successfully loaded.

Check the log file:
load_control.log
for more information about the load.
[oracle@ora12102 Desktop]$

What is that ? 3 rows loaded successfully ?  So, the duplicate row also did get loaded ?  Let's check the log file.

[oracle@ora12102 Desktop]$ ls -ltr
total 12
-rw-r--r-- 1 oracle oinstall 40 Sep 26 22:40 load_data.dat
-rw-r--r-- 1 oracle oinstall 165 Sep 26 22:45 load_control.ctl
-rw-r--r-- 1 oracle oinstall 1833 Sep 26 22:47 load_control.log
[oracle@ora12102 Desktop]$ cat load_control.log

SQL*Loader: Release 12.1.0.2.0 - Production on Mon Sep 26 22:47:09 2016

Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.

Control File: load_control.ctl
Data File: load_data.dat
Bad File: load_data.bad
Discard File: none specified

(Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Continuation: none specified
Path used: Direct

Table TEST_SQLLDR_DIRECT, loaded from every logical record.
Insert option in effect for this table: APPEND
TRAILING NULLCOLS option in effect

Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
ID_COLUMN FIRST * , O(") CHARACTER
DATA_COLUMN NEXT * , O(") CHARACTER

The following index(es) on table TEST_SQLLDR_DIRECT were processed:
index HEMANT.TEST_SQLLDR_DIRECT_U1 was made unusable due to:
ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found

Table TEST_SQLLDR_DIRECT:
3 Rows successfully loaded.
0 Rows not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.

Bind array size not used in direct path.
Column array rows : 5000
Stream buffer bytes: 256000
Read buffer bytes: 1048576

Total logical records skipped: 0
Total logical records read: 3
Total logical records rejected: 0
Total logical records discarded: 0
Total stream buffers loaded by SQL*Loader main thread: 1
Total stream buffers loaded by SQL*Loader load thread: 0

Run began on Mon Sep 26 22:47:09 2016
Run ended on Mon Sep 26 22:47:11 2016

Elapsed time was: 00:00:01.88
CPU time was: 00:00:00.01
[oracle@ora12102 Desktop]$

Did you notice the section in the log file that says :
The following index(es) on table TEST_SQLLDR_DIRECT were processed:
index HEMANT.TEST_SQLLDR_DIRECT_U1 was made unusable due to:
ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found

Apparently, the Index is left UNUSABLE.

[oracle@ora12102 Desktop]$ sqlplus hemant/hemant@pdb1

SQL*Plus: Release 12.1.0.2.0 Production on Mon Sep 26 22:50:51 2016

Copyright (c) 1982, 2014, Oracle. All rights reserved.

Last Successful login time: Mon Sep 26 2016 22:47:09 +08:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

SQL> select status from user_indexes
2 where index_name = 'TEST_SQLLDR_DIRECT_U1'
3 /

STATUS
--------
UNUSABLE

SQL> select * from test_sqlldr_direct order by 1;

ID_COLUMN DATA_COLUMN
---------- ---------------
1 First Row
2 'Second Row'
3 'Third Row'
3 'Oops !'

SQL> alter index test_sqlldr_direct_u1 rebuild;
alter index test_sqlldr_direct_u1 rebuild
*
ERROR at line 1:
ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found


SQL>

We can see the duplicated row for ID_COLUMN=3 and find that the Index cannot be rebuilt.  Oracle has allowed duplicate rows to load and left the Index UNUSABLE.

So, if you are planning to use DIRECT=TRUE and have a Unique Index, make sure you check the status of the Index and/or check the Log file before you proceed with processing the data.


Conversely, here is how the data is handled without DIRECT=TRUE :.

SQL> truncate table test_sqlldr_direct;

Table truncated.

SQL> insert into test_sqlldr_direct values (1,'First Row');

1 row created.

SQL> select status from user_indexes
2 where index_name = 'TEST_SQLLDR_DIRECT_U1'
3 /

STATUS
--------
VALID

SQL>

[oracle@ora12102 Desktop]$ rm load_control.log
[oracle@ora12102 Desktop]$ sqlldr hemant/hemant@PDB1 control=load_control.ctl

SQL*Loader: Release 12.1.0.2.0 - Production on Mon Sep 26 22:59:58 2016

Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.

Path used: Conventional
Commit point reached - logical record count 3

Table TEST_SQLLDR_DIRECT:
2 Rows successfully loaded.

Check the log file:
load_control.log
for more information about the load.
[oracle@ora12102 Desktop]$
[oracle@ora12102 Desktop]$ ls -ltr
total 16
-rw-r--r-- 1 oracle oinstall 40 Sep 26 22:40 load_data.dat
-rw-r--r-- 1 oracle oinstall 165 Sep 26 22:45 load_control.ctl
-rw-r--r-- 1 oracle oinstall 11 Sep 26 22:59 load_data.bad
-rw-r--r-- 1 oracle oinstall 1668 Sep 26 22:59 load_control.log
[oracle@ora12102 Desktop]$ cat load_data.bad
3,'Oops !'
[oracle@ora12102 Desktop]$ cat load_control.log

SQL*Loader: Release 12.1.0.2.0 - Production on Mon Sep 26 22:59:58 2016

Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.

Control File: load_control.ctl
Data File: load_data.dat
Bad File: load_data.bad
Discard File: none specified

(Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array: 64 rows, maximum of 256000 bytes
Continuation: none specified
Path used: Conventional

Table TEST_SQLLDR_DIRECT, loaded from every logical record.
Insert option in effect for this table: APPEND
TRAILING NULLCOLS option in effect

Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
ID_COLUMN FIRST * , O(") CHARACTER
DATA_COLUMN NEXT * , O(") CHARACTER

Record 3: Rejected - Error on table TEST_SQLLDR_DIRECT.
ORA-00001: unique constraint (HEMANT.TEST_SQLLDR_DIRECT_U1) violated


Table TEST_SQLLDR_DIRECT:
2 Rows successfully loaded.
1 Row not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.


Space allocated for bind array: 33024 bytes(64 rows)
Read buffer bytes: 1048576

Total logical records skipped: 0
Total logical records read: 3
Total logical records rejected: 1
Total logical records discarded: 0

Run began on Mon Sep 26 22:59:58 2016
Run ended on Mon Sep 26 22:59:58 2016

Elapsed time was: 00:00:00.07
CPU time was: 00:00:00.00
[oracle@ora12102 Desktop]$

SQL> select * from test_sqlldr_direct
2 order by id_column
3 /

ID_COLUMN DATA_COLUMN
---------- ---------------
1 First Row
2 'Second Row'
3 'Third Row'

SQL>
SQL> select status from user_indexes
2 where index_name = 'TEST_SQLLDR_DIRECT_U1'
3 /

STATUS
--------
VALID

SQL>

The duplicate row was rejected and went to the BAD file and was REJECTED.
.
.
.
So, the next time you chose to use SQLLDR DIRECT=TRUE for its performance benefits, make sure you know how to validate the status of Unique Indexes and check the log file ! Else, you might allow duplicates if the incoming data has not been cleansed before loading into the database.
.
.
.
If you are running 11g, the behaviour is the same in 11g/
.
.
.
.

Categories: DBA Blogs

SQL*Net Message Waits

Sun, 2016-09-18 10:10
Here are some extracts from an 11.2.0.4 AWR Report  I ran a simulated workload on this server for about 40minutes and generated this report.  I understand that some DBAs may be misinterpreting SQL*Net message time.

Host Name        Platform                         CPUs Cores Sockets Memory(GB)
---------------- -------------------------------- ---- ----- ------- ----------
ora11204 Linux x86 64-bit 2 2 1 3.04

Snap Id Snap Time Sessions Curs/Sess
--------- ------------------- -------- ---------
Begin Snap: 158 18-Sep-16 21:42:34 36 1.0
End Snap: 159 18-Sep-16 22:23:01 33 1.0
Elapsed: 40.45 (mins)
DB Time: 42.04 (mins)

Load Profile Per Second Per Transaction Per Exec Per Call
~~~~~~~~~~~~~~~ --------------- --------------- --------- ---------
DB Time(s): 1.0 10.2 0.12 0.07
DB CPU(s): 0.0 0.1 0.00 0.00
Redo size (bytes): 2,523.6 24,793.8
Logical read (blocks): 41.7 409.6


Top 10 Foreground Events by Total Wait Time
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Tota Wait % DB
Event Waits Time Avg(ms) time Wait Class
------------------------------ ------------ ---- ------- ------ ----------
log file sync 1,095 2508 2291 99.5 Commit
db file sequential read 162 37.5 231 1.5 User I/O
DB CPU 19.1 .8
SQL*Net message to client 31,579 .1 0 .0 Network
Disk file operations I/O 103 0 0 .0 User I/O


Wait Classes by Total Wait Time
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Avg Avg
Total Wait Wait % DB Active
Wait Class Waits Time (sec) (ms) time Sessions
---------------- ---------------- ---------------- -------- ------ --------
Commit 1,095 2,509 2291 99.5 1.0
System I/O 12,899 2,210 171 87.6 0.9
User I/O 1,866 38 20 1.5 0.0
DB CPU 19 .8 0.0
Network 33,651 9 0 .4 0.0


Foreground Wait Class DB/Inst: ORCL/orcl Snaps: 158-159
-> s - second, ms - millisecond - 1000th of a second
-> ordered by wait time desc, waits desc
-> %Timeouts: value of 0 indicates value was < .5%. Value of null is truly 0
-> Captured Time accounts for 101.7% of Total DB time 2,522.36 (s)
-> Total FG Wait Time: 2,546.18 (s) DB CPU time: 19.14 (s)


Avg
%Time Total Wait wait
Wait Class Waits -outs Time (s) (ms) %DB time
-------------------- ---------------- ----- ---------------- -------- ---------
Commit 1,095 0 2,509 2291 99.5
User I/O 276 0 38 136 1.5
DB CPU 19 0.8
Network 31,579 0 0 0 0.0
Concurrency 21 0 0 0 0.0

Foreground Wait Events DB/Inst: ORCL/orcl Snaps: 158-159
-> s - second, ms - millisecond - 1000th of a second
-> Only events with Total Wait Time (s) >= .001 are shown
-> ordered by wait time desc, waits desc (idle events last)
-> %Timeouts: value of 0 indicates value was < .5%. Value of null is truly 0


Avg
%Time Total Wait wait Waits % DB
Event Waits -outs Time (s) (ms) /txn time
-------------------------- ------------ ----- ---------- ------- -------- ------
log file sync 1,095 0 2,509 2291 4.4 99.5
db file sequential read 162 0 37 231 0.7 1.5
SQL*Net message to client 31,579 0 0 0 127.9 .0
Disk file operations I/O 103 0 0 0 0.4 .0
latch: shared pool 2 0 0 4 0.0 .0
direct path sync 2 0 0 2 0.0 .0
db file scattered read 6 0 0 0 0.0 .0
jobq slave wait 5,522 100 2,770 502 22.4
SQL*Net message from clien 31,577 0 2,404 76 127.8


Wait Event Histogram DB/Inst: ORCL/orcl Snaps: 158-159
-> Units for Total Waits column: K is 1000, M is 1000000, G is 1000000000
-> % of Waits: value of .0 indicates value was <.05%; value of null is truly 0
-> % of Waits: column heading of <=1s is truly <1024ms>1s is truly >=1024ms
-> Ordered by Event (idle events last)

% of Waits
-----------------------------------------------
Total
Event Waits <1ms ms="" s="">1s
-------------------------- ----- ----- ----- ----- ----- ----- ----- ----- -----
ARCH wait on ATTACH 37 97.3 2.7
ARCH wait on DETACH 37 100.0
Disk file operations I/O 920 99.9 .1
LGWR wait for redo copy 54 100.0
Parameter File I/O 640 100.0
SQL*Net break/reset to cli 6 100.0
SQL*Net message to client 33.6K 100.0
...
SQL*Net message from clien 34.2K 82.7 9.5 2.7 1.6 .7 2.4 .3 .0


Other Instance Activity Stats DB/Inst: ORCL/orcl Snaps: 158-159
-> Ordered by statistic name

Statistic Total per Second per Trans
-------------------------------- ------------------ -------------- -------------
SQL*Net roundtrips to/from clien 31,579 13.0 127.9



Should I be worried about the SQL*Net message waits (to client, from client) ? How should I interpret them ?  I have more than 30K SQL*Net messages and roundtrips.  Apparently, messages from client are very slow -- at 76ms/message.  Apparently, round-trips are very slow -- at 13 round-trips per second.

SQL*Net message to client waits are not really measured in terms of the time it took for the message to reach the client.  Oracle doesn't know how long the transmission took.  It only knows the time taken to put the message onto the TCP stack on the database server itself.  Subsequent network transmission time is unknown. That is why SQL*Net message to client will always be an extremely low figure in terms of time -- because it isn't a true measure of elapsed time sending a message to a client.

SQL*Net message from client is not  just the time spent on the network.  It is the time between the last message to the client upto the next message from the client.  Thus, it also includes client overheads (like "think time", CPU time, network stack on the client) besides transmission over the network.  In most cases, the major component of SQL*Net message from the client is client "think time" -- how long the client spent before formatting and sending the next SQL call to the database server.   This should also mean that it is not always true that SQL*Net message from client is an idle event.

If I have a single client that is either or some or all of :
a. Loading data in sets of rows (e.g. an ETL client)
b. Extracting data in sets of rows
c. Retrieving results and formatting the results for presentation to a user
b. Running a batch job that majorly consists of SQL calls, not PLSQL
the SQL*Net message from client is majorly the time spent by the client  (--- unless you really have a high latency network.).  In most such cases, when tracing the individual session, this wait event is NOT an idle event.  However, when reading an AWR, you cannot isolate such a session from the other sessions that are doing a mix of activity -- some with real interactive end-users, some sending periodic "heart-beat" messages, some completely idle waiting for a request from a user.  In this AWR report, there are a mix of clients with different "think-times", some completely idle for 40minutes.  We can't identify them in the AWR report.

Can you use the SQL*Net roundtrips to/from client figure from the AWR ?  Not if you have a mix of different clients doing different forms of activity.  Idle clients will have very few roundtrips in the 40minutes while clients extracting data row-by-row (not in PLSQL, but SQL or some other client like Java) would have a high number of roundtrips.  So, you can't separate the two types of behaviour in an AWR.

If you are really concerned about identifying SQL*Net overheads and/or round-trips, you should *trace* the specific individual session of interest and extract figures from the trace file.
.
.
.


Categories: DBA Blogs

CODE : View My Source Code -- a Function

Mon, 2016-09-05 09:53
If you need to view the source code of a stored program you need to either :
a.  Be the owner of the program
b.  Have EXECUTE privilege on the program
c.  Have EXECUTE ANY ... privilege or the DBA role

If you are not the owner o the program, the owner can grant you access to view but not modify the program.

Here's code for a PL/SQL Function that allows this.  (I wrote this as a Function --- and not as a PL/SQL Procedure -- to be similar to the DBMS_METADATA.GET_DDL Function).

Imagine  that HR is the Application Schema and the owner of Tables and Programs.  Imagine that HEMANT has not been granted the DBA role or an EXECUTE privilege but needs access to view the source code.

Here's a quick method.  ("Quick" meaning that this code can be improved further -- e.g. by adding an Audit Trail and by better Error Handling).

SQL> connect hr/oracle
Connected.
SQL>
SQL> drop table authorized_view_source purge;

Table dropped.

SQL>
SQL> create table authorized_view_source
2 (username varchar2(30),
3 object_type varchar2(23),
4 object_name varchar2(30))
5 /

Table created.

SQL>
QL> create or replace function view_my_source(object_type_in in varchar2, object_name_in in varchar2)
2 return clob
3 as
4 return_clob clob;
5 line_out varchar2(4000);
6 line_count pls_integer;
7 line_no pls_integer;
8 verify_count pls_integer;
9 return_source clob;
10
11 begin
12 select count(*) into verify_count from authorized_view_source
13 -- check if any of these three predicates fail
14 where username = user
15 and object_type = object_type_in
16 and object_name = object_name_in;
17
18 if verify_count = 0 then
19 -- don't tell if the object exists or not
20 raise_application_error(-20001,'You are not authorized to view the source code of this object');
21 return('FAILURE');
22
23 else
24
25 select count(*) into line_count from user_source
26 where 1=1
27 and type = object_type_in
28 and name = object_name_in;
29
30 return_clob := ' ';
31
32 for line_no in 1..line_count
33 loop
34 return_clob := return_clob || line_out;
35 select text into line_out from user_source
36 where 1=1
37 and type = object_type_in
38 and name = object_name_in
39 and line = line_no;
40 end loop;
41 return_clob := return_clob || line_out;
42
43 return return_clob;
44 end if;
45
46 end view_my_source;
47 /

Function created.
92,1 40%
SQL>
SQL> show errors
No errors.
SQL> grant execute on view_my_source to hemant;

Grant succeeded.

SQL>
SQL> -- list all code objects
SQL> col object_name format a30
SQL> select object_type, object_name
2 from user_objects
3 where object_type not in ('TABLE','INDEX','VIEW')
4 order by object_type, object_name
5 /

OBJECT_TYPE OBJECT_NAME
----------------------- ------------------------------
FUNCTION VIEW_MY_SOURCE
PACKAGE ANOTHER_PKG
PACKAGE DEFINE_MY_VARIABLES
PACKAGE DUMMY_PKG
PACKAGE BODY ANOTHER_PKG
PACKAGE BODY DUMMY_PKG
PROCEDURE ADD_JOB_HISTORY
PROCEDURE SECURE_DML
SEQUENCE DEPARTMENTS_SEQ
SEQUENCE EMPLOYEES_SEQ
SEQUENCE LOCATIONS_SEQ
TRIGGER SECURE_EMPLOYEES
TRIGGER UPDATE_JOB_HISTORY

13 rows selected.

SQL>
SQL> -- store list of authorzed access
SQL> -- e.g. HEMANT can't view the source for
SQL> -- "ANOTHER_PKG" and "VIEW_MY_SOURCE"
SQL> insert into authorized_view_source
2 select 'HEMANT', object_type, object_name
3 from user_objects
4 where object_type not in ('TABLE','INDEX','VIEW')
5 and object_name not in ('ANOTHER_PKG','VIEW_MY_SOURCE')
6 /

10 rows created.

SQL> commit;

Commit complete.

SQL>
SQL> select count(*) from authorized_view_source
2 where username = 'HEMANT'
3 /

COUNT(*)
----------
10

SQL>
SQL>


So, HR has created a Table to list the Users that are authorized to view a certain list of stored programs.  This table, AUTHORIZED_VIEW_SOURCE excludes "ANOTHER_PKG" and "VIEW_MY_SOURCE" from the authorized list  for HEMANT.

Let's see what HEMANT can do :

SQL>
SQL> connect hemant/hemant
Connected.
SQL> -- the return type is a CLOB, so we SET LOMG
SQL> set long 1000000
SQL>
SQL> select hr.view_my_source('PACKAGE','DEFINE_MY_VARIABLES') from dual ;

HR.VIEW_MY_SOURCE('PACKAGE','DEFINE_MY_VARIABLES')
--------------------------------------------------------------------------------
package
define_my_variables
authid definer
is
my_application varchar2(25) := 'Human Resources';
my_base_schema varchar2(25) := 'HR';
end;


SQL>
SQL> select hr.view_my_source('PACKAGE BODY','DUMMY_PKG') from dual ;

HR.VIEW_MY_SOURCE('PACKAGEBODY','DUMMY_PKG')
--------------------------------------------------------------------------------
package body dummy_pkg as
procedure dummy_proc is
begin
raise_application_error (-20001,'Dummy Procedure');
null;
end;
end;


SQL>
SQL> select hr.view_my_source('TRIGGER','SECURE_EMPLOYEES') from dual;

HR.VIEW_MY_SOURCE('TRIGGER','SECURE_EMPLOYEES')
--------------------------------------------------------------------------------
TRIGGER secure_employees
BEFORE INSERT OR UPDATE OR DELETE ON employees
BEGIN
secure_dml;
END secure_employees;


SQL>
SQL> -- these two should raise an error
SQL> select hr.view_my_source('PACKAGE','ANOTHER_PKG') from dual;
ERROR:
ORA-20001: You are not authorized to view the source code of this object
ORA-06512: at "HR.VIEW_MY_SOURCE", line 20



no rows selected

SQL> select hr.view_my_source('FUNCTION','VIEW_MY_SOURCE') from dual;
ERROR:
ORA-20001: You are not authorized to view the source code of this object
ORA-06512: at "HR.VIEW_MY_SOURCE", line 20



no rows selected

SQL>
SQL> select hr.view_my_source('PACKAGE','NONEXISTENT') from dual;
ERROR:
ORA-20001: You are not authorized to view the source code of this object
ORA-06512: at "HR.VIEW_MY_SOURCE", line 20



no rows selected

SQL>


This shows that HEMANT can view the source code of programs listed in the AUTHORIZED_VIEW_SOURCE table and not any others.   The way I have defined the "Your are not authroized ..." message, it doesn't even show whether the requested program exists or not.
.
.
.

Categories: DBA Blogs

Index of Posts

Sun, 2016-09-04 22:43
My friend, Ravi Muthupalani has created an Index of my Blog Posts.

p1 {font-size:smaller;}
Hemant's Oracle DBA BlogAs on 1-Sep-162016-07
CODE : Persistent Variables via PL/SQL Package and DBMS_APPLICATION_INFO
Loading SQL*Plus HELP into the Database
ACS, SQL Patch and SQL Plan Baseline

2016-06
Services -- 4 : Using the SERVICE_NAMES parameter (non-RAC, PDB)
Services -- 3 : Monitoring Usage of Custom Services
Services -- 2 : Starting and Connecting to Services (non-RAC)
Services -- 1 : Services in non-RAC 12c MultiTenant
Data Recovery Advisor (11g)
Blog Series on 11gR2 RAC, GI, ASM
Compression -- 8 : DROPping a Column of a Compressed Table

2016-05
Restore and Recovery from Incremental Backups : Video
Recent Blog Series on Partition Storage
TRUNCATEing a Table makes an UNUSABLE Index VALID again
Partition Storage -- 8 : Manually Sizing Partitions
Compression -- 7 : Updating after BASIC Compression
Compression -- 6b : Advanced Index Compression (revisited)
Compression -- 6 : Advanced Index Compression
FBDA -- 7 : Maintaining Partitioned Source Table
Partition Storage -- 7 : Revisiting HWM - 2 (again)

2016-04
Partition Storage -- 6 : Revisiting Partition HWM
Partition Storage -- 5 : Partitioned Table versus Non-Partitioned Table ? (in 12.1)
Partition Storage -- 4 : Resizing Partitions
Partition Storage -- 3 : Adding new Range Partitions with SPLIT
Partition Storage -- 2 : New Rows Inserted in 12.1 Partitioned Table
Partition Storage -- 1 : Default Partition Sizes in 12c
Online Relocation of Database File : ASM to FileSystem and FileSystem to ASM
FBDA -- 6 : Some Bug Notes
Recent Blog Series on Compression
FBDA -- 5 : Testing AutoPurging
FBDA -- 4 : Partitions and Indexes
FBDA -- 3 : Support for TRUNCATEs
FBDA -- 2 : FBDA Archive Table Structure
FBDA -- 1 : Testing Flashback Data Archive in 12c (NonCDB)

2016-03
Now an OCP 12c
Compression -- 5 : OLTP Compression
Compression -- 4 : RMAN (BASIC) Compression
Compression -- 3 : Index (Key) Compression
COMPRESSION -- 2 : Compressed Table Partitions
Recent Blog Series on (SQL) Tracing
Recent Blog Series on RMAN

2016-02
Compression -- 1b : (more on) BASIC Table Compression
Compression -- 1 : BASIC Table Compression
RMAN : Unused Block Compression and Null Block Compression
Trace Files -- 12 : Tracing a Particular Process
Trace Files -- 11b : Using DBMS_SQLDIAG to trace the Optimization of an SQL Statement

2016-01
Trace Files -- 11 : Tracing the Optimization of an SQL Statement
Trace Files -- 10c : Query and DML (INSERT)

2015-12
Oracle High Availability Demonstrations
Trace Files -- 10b : More DML Tracing
Trace Files -- 10 : Tracing DML
Trace Files -- 9 : Advantages
Trace Files -- 8d : Full Table Scans
Auditing DBMS_STATS usage

2015-11
Trace Files -- 8c : Still More Performance Evaluation from Trace File
Trace Files -- 8b : More Performance Evaluation from Trace File
Trace Files -- 8a : Using SQL Trace for Performance Evaluations
Trace Files -- 7 : SQL in PL/SQL
SSL Support
Trace Files -- 6 : Multiple Executions of the same SQL

2015-10
Trace Files -- 5.2 : Interpreting the SQL Trace Summary level
Trace Files -- 5.1 : Reading an SQL Trace
Trace Files -- 4 : Identifying a Trace File
Trace Files -- 3 : Tracing for specific SQLs

2015-09
Trace Files -- 2 : Generating SQL Traces (another session)
Trace Files -- 1 : Generating SQL Traces (own session)
My YouTube Videos as introductions to Oracle SQL and DBA
RMAN -- 10 : VALIDATE
RMAN -- 9 : Querying the RMAN Views / Catalog

2015-08
RMAN -- 8 : Using a Recovery Catalog Schema
RMAN -- 7 : Recovery Through RESETLOGS -- how are the ArchiveLogs identified ?
RMAN -- 6 : RETENTION POLICY and CONTROL_FILE_RECORD_KEEP_TIME

2015-07
RMAN -- 5c : (Some More) Useful KEYWORDs and SubClauses
RMAN -- 5b : (More) Useful KEYWORDs and SubClauses
Monitoring and Diagnostics without Oracle Enterprise Manager
RMAN -- 5 : Useful KEYWORDs and SubClauses
RMAN -- 4b : Recovering from an Incomplete Restore with OMF Files
RMAN -- 4 : Recovering from an Incomplete Restore

2015-06
RMAN - 3 : The DB_UNIQUE_NAME in Backups to the FRA
RMAN -- 2 : ArchiveLog Deletion Policy
RMAN -- 1 : Backup Job Details

2015-05
Parallel Execution -- 6 Parallel DML Restrictions
Parallel Execution -- 5b Parallel INSERT Execution Plan
Status Of My SlideShare Material
Parallel Execution -- 5 Parallel INSERT

2015-04
Parallel Execution -- 4 Parsing PX Queries
Parallel Execution -- 3b Limiting PX Servers with Resource Manager

2015-03
1 million page views in less than 5 years
Parallel Execution -- 3 Limiting PX Servers
Parallel Execution -- 2c PX Servers
Parallel Execution -- 2b PX Servers
Parallel Execution -- 2 PX Servers
Parallel Execution -- 1b The PARALLEL Hint and AutoDoP (contd)

2015-02
Parallel Execution -- 1 The PARALLEL Hint and AutoDoP
Database Flashback -- 5
Database Flashback -- 4
Database Flashback -- 3
Database Flashback -- 2
Database Flashback -- 1

2015-01
A blog on Oracle Standard Edition
Inserting into a table with potentially long rows

2014-12
Statistics on this blog
StatsPack and AWR Reports -- Bits and Pieces -- 4

2014-11
StatsPack and AWR Reports -- Bits and Pieces -- 3
StatsPack and AWR Reports -- Bits and Pieces -- 2

2014-10
StatsPack and AWR Reports -- Bits and Pieces -- 1
Bandwidth and Latency
11g Adaptive Cursor Sharing --- does it work only for SELECT statements ? Using the BIND_AWARE Hint for DML

2014-09
The ADMINISTER SQL MANAGEMENT OBJECT Privilege
EXECUTE Privilege on DBMS_SPM not sufficient
Index Growing Larger Than The Table
RAC Database Backups

2014-08
ASM Commands : 2 -- Migrating a DiskGroup to New Disk(s)
ASM Commands : 1 -- Adding and Using a new DiskGroup for RAC
GI Commands : 2 -- Managing the Local and Cluster Registry

2014-07
GI Commands : 1 -- Monitoring Status of Resources
RAC Commands : 2 -- Updating Configuration for Services
RAC Commands : 1 -- Viewing Configuration
Installing OEL 6 and Database 12c
Passed the 11g RAC and Grid Expert Exam

2014-06
Gather Statistics Enhancements in 12c
Getting your Transaction ID
Guenadi Jilevski's posts on building RAC Clusters on VM Virtual Box

2014-05
Oracle Diagnostics Presentations
Partitions and Segments and Data Objects
(Slightly Off Topic) Spurious Correlations

2014-04
PageView Count
Upgrading Certification to 12c

2014-03
Storing Trailing NULLs in a table
Plan HASH_VALUE remains the same for the same Execution Plan, even if ROWS and COST change
My slideshare site has had 1000 views
Dropping an Index Partition

2014-02
RMAN Image Copy File Names
SQL Analytics
An SQL Performance Quiz
login.sql does not require a login
Database Technology Index
The difference between SELECT ANY DICTIONARY and SELECT_CATALOG_ROLE

2014-01
My first Backup and Recovery Quiz
LAST_CALL_ET in V$SESSION
OTNYathra 2014

2013-12
INTERVAL Partitioning
DEFAULT ON NULL on INSERT
GATHER_TABLE_STATS : What SQLs does it call ?. 12c

2013-11
Gather Statistics Enhancements in 12c -- 5
AIOUG Sangam'13 Day Two 09-Nov-13
AIOUG Sangam'13 Day One 08-Nov-13

2013-10
Gather Statistics Enhancements in 12c -- 4
The DEFAULT value for a column

2013-09
AIOUG Sangam 13
RMAN (and DataPump) book

2013-08
Sins of Software Deployment
Gather Statistics Enhancements in 12c -- 3
Common Mistakes Java Developers make when writing SQL
Gather Statistics Enhancements in 12c -- 2
Gather Statistics Enhancements in 12c -- 1
12c New Features to "Watch Out For"
Re-CATALOGing BackupPieces ??

2013-07
What happens if a database (or tablespace) is left in BACKUP mode
Interesting Bugs in 12cR1
Concepts / Features overturned in 12c
12c RMAN Restrictions -- when connected to a PDB
Information on 12c
Dynamic SQL

2013-06
A Function executing DML in a View
Oracle Database 12c Learning Library
Networking in Oracle Virtual Box
Upcoming Blog Post : DML when querying a View
Getting the ROWIDs present in a Block
DROP A Tablespace After a Backup
Bug 10013177 running Aggregation on Expression indexed by an FBI

2013-05
BACKUP CURRENT CONTROLFILE creates a Snapshot Controlfile
Games for DBAs

2013-04
Preparing for Oracle Certification
SSD Performance for Oracle Databases
Single Row Fetch from a LOB
Oracle Forums due for Upgrade

2013-03
Useful Oracle Youtube videos
Segment Size of a Partition (11.2.0.2 and above)
Short-Circuiting the COST

2013-02
Moving a Partition to an Archival Schema and Tablespace
Backup and Recovery with intermediate NOARCHIVELOG

2013-01
Oracle 11g Anti-Hackers Cookbook
Podcast on the Oracle ACE program
Oracle's Advisory On Certification Integrity

2012-12
Book on OEM 12c

2012-11
Oracle 11g Anti-Hackers Cookbook

2012-10
Separate Child Cursor with varying bind allocation length

2012-09
Cardinality Decay
IT Contracting in Singapore
Open Invitation from Packt Publishing

2012-08
Storage Allocation
Issue a RECOVER for a Tablespace/Datafile that does not need recovery

2012-07
How to use Oracle Virtual Box templates
Database Specialists in Singapore
ON COMMIT Refresh without a Primary Key
Materialized View Refresh ON COMMIT
WizIQ Tutorials
An Oracle Installer that automatically switches to Console mode

2012-06
OOW 2012 Content Catalog
CONTROLFILE AUTOBACKUPs are OBSOLETE[d]
RMAN BACKUP AS COPY
OEM 12c : New Book
Java for PLSQL Developers

2012-05
SQL written by Lisbeth Salander
CHECKPOINT_CHANGE#
CURRENT_SCN and CHECKPOINT_CHANGE#
Index Block Splits
RMAN Tips -- 4
Debugging stories
A Poll on the usage of SQL Plan Management
USER_TAB_MODIFICATIONS -- 1

2012-04
Create Histogram without having to gather Table Stats
AIOUG Sangam '12 -- CFP
When is an ArchiveLog created ?
Parameters for COMMIT operations
Primary Key name appears to be different
TOO_MANY_ROWS and Variable Assignment

2012-03
The Hot Backup "myth" about Datafiles not being updated
Relocating a datafile using RMAN
More than 250K page views
OOW 2012 CFP now open.
Oracle Database Performance Diagnostics -- before you begin
Another example of COST in an Explain Plan
Packt Publishing's Oracle Packtpot

2012-02
Two Partitioned Indexes with different HIGH_VALUEs
CURSOR_SHARING FORCE and Child Cursors
Archived Logs after RESETLOGS
SLOB
RESETLOGS

2012-01
Understanding RESETLOGS
Oracle Wiki Relaunched
Departmental Analytics -- a "pro-local" approach ?
Refreshing an MV on a Prebuilt Table
SQL in Functions
Growing Materialized View (Snapshot) Logs
Datafiles not Restored -- using V$DATAFILE and V$DATAFILE_HEADER

2011-12
Does a STARTUP MOUNT verify datafiles ?
DROP TABLESPACE INCLUDING CONTENTS drops segments
(Off-Topic) How NOT to make a chart
AIOUG Sangam '11 photographs
AIOUG Sangam 11 content

2011-11
Constraints and Indexes
Oracle PreConfigured Templates
SSDs for Oracle
ROWIDs from an Index
RESTORE, RECOVER and RESETLOGS
Grid and RAC Notes
Oracle's Best-Of-Breed Strategy
Tablespace Recovery in a NOARCHIVELOG database
CTAS in a NOARCHIVELOG database is a NOLOGGING operation
Index Organized Table(s) -- IOT(s)
An ALTER USER to change password updates the timestamp of the password file
Handling Exceptions in PLSQL

2011-10
AIOUG : Sangam '11
The impact of ASSM on Clustering of data -- 2
DBMS_REDEFINITION to redefine a Partition -- and the impact of deferred_segment_creation
The impact of ASSM on Clustering of data
Controlfiles : Number and Size
Oracle OpenWorld and JavaOne Announcements
RMAN Tips -- 3

2011-09
Another example of GATHER_TABLE_STATS and a Histogram
Oracle Android App
An Index that is a "subset" of a pre-existing Index
RMAN Tips -- 2
Very successful Golden Gate workshop at SG RACSIG
RMAN Tips -- 1
Outer Join Queries
Splitting a Range Partitioned Table
Understanding Obsolescence of RMAN Backups

2011-08
CREATE INDEX ..... PARALLEL
Gather Column (Histogram) Stats can use an Index
Does GATHER_TABLE_STATS update Index Statistics ?
Reading an AWR Report -- 3
Singapore RACSIG meeting today
Reading an AWR -- 2
Oracle 11g RAC Essentials

2011-07
More on COUNT()s -- 2
More on COUNT()s
Data Quality Issues cannot always be addressed by programming
Running a COUNT(column) versus COUNT(*)
Oracle Database "Performance" - A Diagnostics Method
ENABLE ROW MOVEMENT with MSSM
Virtathon Sessions Schedule
ENABLE ROW MOVEMENT
Using WGET to download Patches
Reading an AWR - 1
Multiple Channels in RMAN are not always balanced

2011-06
DDL Triggers
Are you ready ? (to buy bigger hardware or review your code ?)
How Are Students Learning Programming ?
Oracle APAC Developer Program
OOW 2011 Content Catalog
(OT) : Dead Media Never Really Die
Precedence in Parallel Query specifications
Inequality and NULL
SQL Injection
New Presentation : On Nested Loop and Hash Join
Getting the right statistics
Nested Loop and Consistent Gets

2011-05
Interpreting an AWR report when the ArchiveLog Dest or FRA was full
Deleting SQL Plan Baselines
Database Audit setup -- 2 interesting findings
Capturing SQL PLAN Baselines
11g OCP
Getting all the instance parameters
RMAN's COPY command
Collection of my Oracle Blog posts on Backup and Recovery

2011-04
SELECT FOR UPDATE with SubQuery (and "Write Consistency")
ArchiveLogs in the controlfile
DETERMINISTIC Functions - 3
Standby Databases (aka "DataGuard") -1
DETERMINISTIC Functions -- 2
DETERMINISTIC Functions

2011-03
OuterJoin with Filter Predicate
I/O for OutOfLine LOBs
Oracle Enterprise Cloud Summit in Singapore
Cardinality Estimates in Dynamic Partition Pruning
Primary Key and Index
Most Popular Posts - Feb 11

2011-02
ITIL v3 Foundation
Index Block Splits --- with REVERSE KEY Index
Qualifying Column/Object names to set the right scope
Cardinality Feedback in 11.2
Oracle Diagnostics Presentations
Locks and Lock Trees
Most Popular Posts - Jan 11

2011-01
Gather Stats Concurrently
Synchronising Recovery of two databases
Transaction Failure --- when is the error returned ?
GLOBAL TEMPORARY TABLEs and GATHER_TABLE_STATS
Rollback of Transaction(s) after SHUTDOWN ABORT
Latches and Enqueues
Incomplete Recovery
ZDNet Asia IT Salary Benchmark 2010
Most Popular Posts - Dec 10

2010-12
Using V$SESSION_LONGOPS
Most Popular Posts - Nov 10

2010-11
Oracle VM Templates released
Some Common Errors - 7 - "We killed the job because it was hung"
SET TIME ON in RMAN
Most Popular Posts - Oct 10

2010-10
How the Optimizer can use Constraint Definitions
Featured in Oracle Magazine
Data Skew and Cardinality Changing --- 2
Most Popular Posts

2010-09
Data Skew changing over time --- and the Cardinality Estimate as well !
Index Skip Scan
Deadlocks : 2 -- Deadlock on INSERT
Deadlocks

2010-08
Adding a DataFile that had been excluded from a CREATE CONTROLFILE
Trying to understand LAST_CALL_ET -- 3
Trying to understand LAST_CALL_ET -- 2
Trying to understand LAST_CALL_ET -- 1
Oracle Mergers and Acquisitions
Creating a "Sparse" Index

2010-07
Oracle switching to non-sequential logs
(Off Topic): "What's the body count ?"
Preserving the Index when dropping the Constraint
V$DATABASE.CREATED -- is this the Database Creation timestamp ?
(Off Topic) : "Now Is That Architecture ?"

2010-06
Some Common Errors - 6 - Not collecting Metrics
Know your data and write a better query
RECOVER DATABASE starts with an update -- 2

2010-05
RECOVER DATABASE starts with an update
Database Links
Cardinality Estimation
Read Only Tablespaces and BACKUP OPTIMIZATION
Database and SQL Training

2010-04
Oracle Database History
AutoTune Undo
Data Warehousing Performance
SQLs in PLSQL -- 2
SQLs in PLSQL
Partitions and Statistics
11gR2 Recursive SubQuery Factoring

2010-03
Extracting Application / User SQLs from a TraceFile
A large index for an empty table ?
ALTER INDEX indexname REBUILD.
Adaptive Cursor Sharing explained
An "unknown" error ?
Misinterpreting RESTORE DATABASE VALIDATE

2010-02
Some Common Errors - 5 - Not reviewing the alert.log and trace files
Something Unique about Unique Indexes
Some Common Errors - 4 - Not using ARRAYSIZE
Using Aliases for Columns and Tables in SQLs
Table and Index Statistics with MOVE/REBUILD/TRUNCATE
Some Common Errors - 3 - NOLOGGING and Indexes
An Oracle DBA Interview
Some Common Errors - 2 - NOLOGGING as a Hint
Multiple Block Sizes

2010-01
Common Errors Series
DDL on Empty Partitions -- are Global Indexes made UNUSABLE ?
Some Common Errors - 1 - using COUNT(*)
Adding a PK Constraint sets the key column to NOT NULL

2009-11
MOS Survey Results
SIZE specification for Column Histograms
Sample Sizes : Table level and Column level

2009-10
Some MORE Testing on Intra-Block Row Chaining
Some Testing on Intra-Block Row Chaining
Indexes Growing Larger After Rebuilds

2009-09
SQLs in Functions : Performance Impact
SQLs in Functions : Each Execution is Independent
RMAN can identify and catalog / use ArchiveLogs automagically
Table and Partition Statistics
I am an Oracle ACE, officially

2009-08
Histograms on "larger" columns
Counting the Rows in a Table
Using an Index created by a different user

2009-07
A PACKT book on Oracle Database Utilities
Direct Path Read cannot do delayed block cleanouts
The difference between NOT IN and NOT EXISTS
Simple Tracing
Sizing OR Growing a Table in AUTOALLOCATE

2009-06
AUTOEXTEND ON Next Size
Why EXPLAIN PLAN should not be used with Bind Variables

2009-05
Backup Online Redo Logs ? (AGAIN ?!)
Index Block Splits and REBUILD
Index Block Splits : 50-50
Database Recovery with new datafile not present in the controfile
Index Block Splits : 90-10
Rename Database while Cloning it.
Incorrectly using AUTOTRACE and EXPLAIN PLAN
Ever wonder "what if this database contains my data ?"

2009-04
Bringing ONLINE a Datafile that is in RECOVER mode because it was OFFLINE
Controlfile Backup older than the ArchiveLogs
RMAN Backup and Recovery for Loss of ALL files
Incorrect Cardinality Estimate of 1 : Bug 5483301

2009-03
Database Independence Anyone ?
Columnar Databases
Materialized View on Prebuilt Table
Materialized Views and Tables
Checking the status of a database
Logical and Physical Storage in Oracle

2009-02
CLUSTERING_FACTOR
RDBMS Software, Database and Instance
Restore or Create Controlfile
Full Table Scan , Arraysize etc
Array Processing, SQL*Net RoundTrips and consistent gets
MIN/MAX Queries, Execution Plans and COST

2009-01
Faulty Performance Diagnostics based on initial set of rows returned
When NOT to use V$SESSION_LONGOPS

2008-11
Database Event Trigger and SYSOPER
Tracing a Process -- Tracing DBWR
expdp to the default directory without the DBA role
Data Pump using default directory
Histogram (skew) on Unique Values
OPEN RESETLOGS without really doing a Recovery
Numbers and NULLs

2008-10
Using STATISTICS_LEVEL='ALL' and 10046, level 8
Delayed Block Cleanout -- through Instance Restart
Delayed Block Cleanout

2008-09
Relational Theory and SQL

2008-08
ASSM or MSSM ? -- DELETE and INSERT
The once again new forums.oracle.com
Testing Bug 4260477 Fix for Bug 4224840
ASSM or MSSM ? -- The impact on INSERTS
VMWare Bug presents a nightmare scenario
Preventing a User from changing his password
More Tests of COL_USAGE
Testing Gather Stats behaviour based on COL_USAGE

2008-07
More Tests on DBMS_STATS GATHER AUTO
Testing the DBMS_STATS option GATHER AUTO
Cardinality Estimate : Dependent Columns --- Reposted
Table Elimination (aka "Join Elimination")
Bind Variable Peeking

2008-06
Cardinality Estimates : Dependent Columns
Delete PARENT checks every CHILD row for Parent Key !
Monitoring "free memory" on Linux
A long forums discussion on Multiple or Different Block Sizes
Tuning Very Large SELECTs in SQLPlus
MVs with Refresh ON COMMIT cannot be used for Synchronous Replication

2008-05
Ever heard of "_simple_view_merging" ?
Tracing a DBMS_STATS run
Creating a COMPRESSed Table
Passwords are One Way Hashes, Not Encrypted
APPEND, NOLOGGING and Indexes
RMAN Consistent ("COLD" ?) Backup and Restore
DBAs working long hours
One Thing Leads to Another ....
TEMPORARY Segments in Data/Index Tablespaces

2008-04
Row Sizes and Sort Operations
Using SYS
Indexed column (unique or not) -- What if it is NULLable
The Worst Ever SQL Rewrite
Complex View Merging -- 7
Complex View Merging - 4,5,6
Programming for MultiCore architectures
Complex View Merging -- 3
Complex View Merging -- 2
Complex View Merging -- 1

2008-03
Example "sliced" trace files and tkprof
tkprof on partial trace files
Backup the Online Redo Logs ?
Rebuilding Indexes - When and Why ?
Rebuilding Indexes
ALTER TABLE ... SHRINK SPACE

2008-02
OS Statistics from AWR Reports
Database Recovery : RollForward from a Backup Controlfile
Indexing NULLs -- Update

2008-01
Is RAID 5 bad ? Always bad ?
The Impact of the Clustering Factor
Examples Of Odd Extent Sizes In Tablespaces With AUTOALLOCATE
When Should Indexes Be Rebuilt

2007-12
Using an Index for a NOT EQUALS Query
Always Explicitly Convert DataTypes

2007-11
Are ANALYZE and DBMS_STATS also DDLs ?

2007-10
Flush Buffer_Cache -- when Tracing doesn't show anything happening
Inserts waiting on Locks ? Inserts holding Locks ?

2007-09
More on Bind Variable Peeking and Execution Plans
ATOMIC_REFRESH=>FALSE causes TRUNCATE and INSERT behaviour in 10g ??

2007-08
When "COST" doesn't indicate true load
NULLs are not Indexed, Right ? NOT !
NLS_DATE_FORMAT
Shared Nothing or Shared Disks (RAC) ?
LGWR and 'log file sync waits'

2007-07
Using ARRAYSIZE to reduce RoundTrips and number of FETCH calls
Parse "Count"

2007-06
Read Consistency across Statements
Some observations from the latest Oracle-HP Benchmark
A Bug in OWI
Oracle Books in the Library

2007-05
AUTOALLOCATE and Undo Segments
Where's the Problem ? Not in the Database !
RollForward from a Cold Backup
Another Recovery from Hell story
SQL Statement Execution Times
Recovery in Cold Backup
Stress Testing

2007-04
DBA Best Practices
Recovery without UNDO Tablespace DataFiles
Programs that expect strings to be of a certain length !
UNDO and REDO for INSERTs and DELETEs
Snapshot Too Old or Rollback Segment Too Small

2007-03
Backups and Recoveries, SANs and Clones, and Murphy
Optimizer Index Cost Parameters
Understanding "Timed Events" in a StatsPack Report
Database Error Exposed on the Internet
Throughput v Scalability
Using Normal Tables for Temporary Data

2007-02
Interpreting Explain Plans
Creating Database Links
Buffer Cache Hit Ratio GOOD or BAD ?

2007-01
Sequences : Should they be Gap Free ?
Using Partial Recoveries to test Backups
Deleting data doesn't reduce the size of the backup
Large (Growing) Snapshot Logs indicate that you have a problem
Sometimes you trip up on Triggers
Views with ORDER BY
Building Materialized Views and Indexes

2006-12
ArchiveLogs and Transaction Volumes
ORA-1555 and UNDO_RETENTION
Why an Oracle DBA Blog ?


Categories: DBA Blogs

CODE : Persistent Variables via PL/SQL Package and DBMS_APPLICATION_INFO

Sat, 2016-07-23 10:43
I am now introducing some code samples in my blog.  I won't restrict myself to SQL but will also include PL/SQL  (C ? Bourne/Korn Shell ? what else ?)

This is the first of such samples.


Here I demonstrate using a PL/SQL Package to define persistent variables and then using them with DBMS_APPLICATION_INFO.  This demo consists of only 2 variables being used by 1 session.  But we could have a number of variables in this Package and invoked by multiple client sessions in the real workd.

I first :

SQL> grant create procedure to hr;

Grant succeeded.

SQL>


Then, in the HR schema, I setup a Package to define variables that can persist throughout a session.  Public Variables defined in a Package, once invoked, persist throughout the session that invoked them.

create or replace package
define_my_variables
authid definer
is
my_application varchar2(25) := 'Human Resources';
my_base_schema varchar2(25) := 'HR';
end;
/

grant execute on define_my_variables to hemant;
grant select on employees to hemant;


As HEMANT, I then execute :

SQL> connect hemant/hemant  
Connected.
SQL> execute dbms_application_info.set_module(-
> module_name=>HR.define_my_variables.my_application,-
> action_name=>NULL);

PL/SQL procedure successfully completed.

SQL>


As SYSTEM, the DBA can monitor HEMANT

QL> show user
USER is "SYSTEM"
SQL> select sid, serial#, to_char(logon_time,'DD-MON HH24:MI:SS') Logon_At, module, action
2 from v$session
3 where username = 'HEMANT'
4 order by 1
5 /

SID SERIAL# LOGON_AT MODULE
---------- ---------- ------------------------ ----------------------------------------------------------------
ACTION
----------------------------------------------------------------
1 63450 23-JUL 23:24:03 Human Resources



SQL>


Then, HEMANT intends to run a query on the EMPLOYEES Table.

SQL> execute dbms_application_info.set_action(-
> action_name=>'Query EMP');

PL/SQL procedure successfully completed.

SQL> select count(*) from hr.employees where job_id like '%PROG%'
2 /

COUNT(*)
----------
5

SQL>


SYSTEM can see what he is doing with

SQL> l
1 select sid, serial#, to_char(logon_time,'DD-MON HH24:MI:SS') Logon_At, module, action
2 from v$session
3 where username = 'HEMANT'
4* order by 1
SQL> /

SID SERIAL# LOGON_AT MODULE
---------- ---------- ------------------------ ----------------------------------------------------------------
ACTION
----------------------------------------------------------------
1 63450 23-JUL 23:24:03 Human Resources
Query EMP


SQL>


Returning, to the HR login, I can see :

SQL> show user
USER is "HEMANT"
SQL> execute dbms_output.put_line(-
> 'I am running ' || hr.define_my_variables.my_application || ' against ' || hr.define_my_variables.my_base_schema);
I am running Human Resources against HR

PL/SQL procedure successfully completed.

SQL>


So, I have demonstrated :
1.  Using a PLSQL Package Specification (without the need for a Package Body) to define variables that are visible to another session.

2.  The possibility of using this across schemas.  HR could be my "master schema" that setups all variables and HEMANT is one of many "client" schemas (or users) that use these variables..

3. The variables defined will persist throughout the client session once they are invoked.

4.  Using DBMS_APPLICATION_INFO to call these variables and setup client information.


Note :  SYSTEM can also trace HEMANT's session using DBMS_MONITOR as demonstrated in Trace Files -- 2 : Generating SQL Traces (another session)

.
.
.

Categories: DBA Blogs

Loading SQL*Plus HELP into the Database

Sun, 2016-07-10 22:39
Oracle provides scripts to load the HELP command for SQL*Plus.

See $ORACLE_HOME/sqlplus/admin/help

The schema to use is SYSTEM, not SYS.

I demonstrate
(a) How to load SQLPlus Help  into the database
(b) How to customise the Help (e.g. add new commands)

[oracle@ora11204 help]$ cd $ORACLE_HOME/sqlplus/admin/help
[oracle@ora11204 help]$ ls -l
total 84
-rwxrwxrwx. 1 oracle oracle 265 Feb 17 2003 helpbld.sql
-rwxrwxrwx. 1 oracle oracle 366 Jan 4 2011 helpdrop.sql
-rwxrwxrwx. 1 oracle oracle 71817 Aug 17 2012 helpus.sql
-rwxrwxrwx. 1 oracle oracle 2154 Jan 4 2011 hlpbld.sql
[oracle@ora11204 help]$ sqlplus -S system/oracle @helpbld.sql `pwd` helpus.sql
...
...
...
View created.


58 rows created.


Commit complete.


PL/SQL procedure successfully completed.

[oracle@ora11204 help]$


The 'pwd`  (note the back-quote character, not the single quote character) is a way of specifying the current directory in Unix and Linux shells.   This specifies where the help datafile is located.  helpus.sql is the help data in English (US-English).

The scripts create a table called "HELP" in the SYSTEM schema.  SQL*Plus's "HELP" command then uses this table.

Examples :

SQL> connect hemant/hemant
Connected.
SQL> help

HELP
----

Accesses this command line help system. Enter HELP INDEX or ? INDEX
for a list of topics.

You can view SQL*Plus resources at
http://www.oracle.com/technology/documentation/

HELP|? [topic]


SQL>
SQL> help set

SET
---

Sets a system variable to alter the SQL*Plus environment settings
for your current session. For example, to:
- set the display width for data
- customize HTML formatting
- enable or disable printing of column headings
- set the number of lines per page

SET system_variable value

where system_variable and value represent one of the following clauses:

APPI[NFO]{OFF|ON|text} NEWP[AGE] {1|n|NONE}
ARRAY[SIZE] {15|n} NULL text
AUTO[COMMIT] {OFF|ON|IMM[EDIATE]|n} NUMF[ORMAT] format
AUTOP[RINT] {OFF|ON} NUM[WIDTH] {10|n}
AUTORECOVERY {OFF|ON} PAGES[IZE] {14|n}
AUTOT[RACE] {OFF|ON|TRACE[ONLY]} PAU[SE] {OFF|ON|text}
[EXP[LAIN]] [STAT[ISTICS]] RECSEP {WR[APPED]|EA[CH]|OFF}
BLO[CKTERMINATOR] {.|c|ON|OFF} RECSEPCHAR {_|c}
CMDS[EP] {;|c|OFF|ON} SERVEROUT[PUT] {ON|OFF}
COLSEP {_|text} [SIZE {n | UNLIMITED}]
CON[CAT] {.|c|ON|OFF} [FOR[MAT] {WRA[PPED] |
COPYC[OMMIT] {0|n} WOR[D_WRAPPED] |
COPYTYPECHECK {ON|OFF} TRU[NCATED]}]
DEF[INE] {&|c|ON|OFF} SHIFT[INOUT] {VIS[IBLE] |
DESCRIBE [DEPTH {1|n|ALL}] INV[ISIBLE]}
[LINENUM {OFF|ON}] [INDENT {OFF|ON}] SHOW[MODE] {OFF|ON}
ECHO {OFF|ON} SQLBL[ANKLINES] {OFF|ON}
EDITF[ILE] file_name[.ext] SQLC[ASE] {MIX[ED] |
EMB[EDDED] {OFF|ON} LO[WER] | UP[PER]}
ERRORL[OGGING] {ON|OFF} SQLCO[NTINUE] {> | text}
[TABLE [schema.]tablename] SQLN[UMBER] {ON|OFF}
[TRUNCATE] [IDENTIFIER identifier] SQLPLUSCOMPAT[IBILITY] {x.y[.z]}
ESC[APE] {\|c|OFF|ON} SQLPRE[FIX] {#|c}
ESCCHAR {@|?|%|$|OFF} SQLP[ROMPT] {SQL>|text}
EXITC[OMMIT] {ON|OFF} SQLT[ERMINATOR] {;|c|ON|OFF}
FEED[BACK] {6|n|ON|OFF} SUF[FIX] {SQL|text}
FLAGGER {OFF|ENTRY|INTERMED[IATE]|FULL} TAB {ON|OFF}
FLU[SH] {ON|OFF} TERM[OUT] {ON|OFF}
HEA[DING] {ON|OFF} TI[ME] {OFF|ON}
HEADS[EP] {||c|ON|OFF} TIMI[NG] {OFF|ON}
INSTANCE [instance_path|LOCAL] TRIM[OUT] {ON|OFF}
LIN[ESIZE] {80|n} TRIMS[POOL] {OFF|ON}
LOBOF[FSET] {1|n} UND[ERLINE] {-|c|ON|OFF}
LOGSOURCE [pathname] VER[IFY] {ON|OFF}
LONG {80|n} WRA[P] {ON|OFF}
LONGC[HUNKSIZE] {80|n} XQUERY {BASEURI text|
MARK[UP] HTML [OFF|ON] ORDERING{UNORDERED|
[HEAD text] [BODY text] [TABLE text] ORDERED|DEFAULT}|
[ENTMAP {ON|OFF}] NODE{BYVALUE|BYREFERENCE|
[SPOOL {OFF|ON}] DEFAULT}|
[PRE[FORMAT] {OFF|ON}] CONTEXT text}


SQL>
SQL> help show

SHOW
----

Shows the value of a SQL*Plus system variable, or the current
SQL*Plus environment. SHOW SGA requires a DBA privileged login.

SHO[W] option

where option represents one of the following terms or clauses:
system_variable
ALL
BTI[TLE]
ERR[ORS] [{FUNCTION | PROCEDURE | PACKAGE | PACKAGE BODY | TRIGGER
| VIEW | TYPE | TYPE BODY | DIMENSION | JAVA CLASS} [schema.]name]
LNO
PARAMETERS [parameter_name]
PNO
RECYC[LEBIN] [original_name]
REL[EASE]
REPF[OOTER]
REPH[EADER]
SGA
SPOO[L]
SPPARAMETERS [parameter_name]
SQLCODE
TTI[TLE]
USER


SQL>
SQL> help connect

CONNECT
-------

Connects a given username to the Oracle Database. When you run a
CONNECT command, the site profile, glogin.sql, and the user profile,
login.sql, are processed in that order. CONNECT does not reprompt
for username or password if the initial connection does not succeed.

CONN[ECT] [{logon|/|proxy} [AS {SYSOPER|SYSDBA|SYSASM}] [edition=value]]

where logon has the following syntax:
username[/password][@connect_identifier]

where proxy has the syntax:
proxyuser[username][/password][@connect_identifier]
NOTE: Brackets around username in proxy are required syntax


SQL>


Remember !  These are SQL*Plus commands, not SQL Language commands.  So you won't see help about CREATE or ALTER or SELECT and other such commands.

Since, it uses a plain-text file (helpus.sql in this case) to load the help information, it is possible to extend this.

For example, I copy helpus.sql as helpcustom.sql and add these lines into the scrip file :

INSERT INTO SYSTEM.HELP VALUES ('DBINFO', 1, NULL);
INSERT INTO SYSTEM.HELP VALUES ('DBINFO', 2, 'This Hemant''s Test Database');
INSERT INTO SYSTEM.HELP VALUES ('DBINFO', 3, 'A Playground database');
INSERT INTO SYSTEM.HELP VALUES ('DBINFO', 4, 'Running 11.2.0.4 on Linux');

INSERT INTO SYSTEM.HELP VALUES ('OWNERINFO', 1, NULL);
INSERT INTO SYSTEM.HELP VALUES ('OWNERINFO', 2, 'Test Database owned by Hemant');
INSERT INTO SYSTEM.HELP VALUES ('CONTENTS', 1, NULL);
INSERT INTO SYSTEM.HELP VALUES ('CONTENTS', 2, 'Various Experiments by Hemant');

INSERT INTO SYSTEM.HELP VALUES ('WHO IS HEMANT', 1, NULL);
INSERT INTO SYSTEM.HELP VALUES ('WHO IS HEMANT', 2, 'Hemant K Chitale');
INSERT INTO SYSTEM.HELP VALUES ('WHO IS HEMANT', 3, 'https://hemantoracledba.blogspot.com');

COMMIT;


and then I run the command :

sqlplus -S system/oracle @helpbld.sql `pwd` helpcustom.sql


And view the results :

SQL> connect hemant/hemant
Connected.
SQL> help dbinfo

This Hemant's Test Database
A Playground database
Running 11.2.0.4 on Linux

SQL> help ownerinfo

Test Database owned by Hemant

SQL> help who is hemant

Hemant K Chitale
https://hemantoracledba.blogspot.com

SQL>
SQL> help startup

STARTUP
-------

Starts an Oracle instance with several options, including mounting,
and opening a database.

STARTUP options | upgrade_options

where options has the following syntax:
[FORCE] [RESTRICT] [PFILE=filename] [QUIET] [ MOUNT [dbname] |
[ OPEN [open_options] [dbname] ] |
NOMOUNT ]

where open_options has the following syntax:
READ {ONLY | WRITE [RECOVER]} | RECOVER

and where upgrade_options has the following syntax:
[PFILE=filename] {UPGRADE | DOWNGRADE} [QUIET]


SQL> help shutdown

SHUTDOWN
--------

Shuts down a currently running Oracle Database instance, optionally
closing and dismounting a database.

SHUTDOWN [ABORT|IMMEDIATE|NORMAL|TRANSACTIONAL [LOCAL]]


SQL>


And, so, the SQL*Plus HELP command can be customised !

.
.
.

Categories: DBA Blogs

ACS, SQL Patch and SQL Plan Baseline

Mon, 2016-07-04 03:49
Marko Sutic's blog post on Adaptive Cursor Sharing and SQL Plan Baselines, with an example of SQL Patch as well.
.
.
.
Categories: DBA Blogs

Services -- 4 : Using the SERVICE_NAMES parameter (non-RAC, PDB)

Mon, 2016-06-27 09:55
In my previous blog post, I have demonstrated using DBMS_SERVICE to create and start services.

But there is another way.  Using the SERVICE_NAMES parameter.  Going by most google references, SERVICE_NAME seems to be more popular than DBMS_SERVICE.

Can it be used in a Pluggable Database ?  Let' try.

[oracle@ora12102 Desktop]$ . oraenv
ORACLE_SID = [oracle] ? CDB1
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@ora12102 Desktop]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Mon Jun 27 22:39:59 2016

Copyright (c) 1982, 2014, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 1644167168 bytes
Fixed Size 2925024 bytes
Variable Size 973082144 bytes
Database Buffers 654311424 bytes
Redo Buffers 13848576 bytes
Database mounted.
Database opened.
SQL> alter pluggable database pdb1 open;

Pluggable database altered.

SQL>

[grid@ora12102 ~]$ lsnrctl services

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 27-JUN-2016 22:43:36

Copyright (c) 1991, 2014, Oracle. All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
Services Summary...
Service "+ASM" has 1 instance(s).
Instance "+ASM", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
Service "CDB1" has 1 instance(s).
Instance "CDB1", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
Service "CDB1XDB" has 1 instance(s).
Instance "CDB1", status READY, has 1 handler(s) for this service...
Handler(s):
"D000" established:0 refused:0 current:0 max:1022 state:ready
DISPATCHER
(ADDRESS=(PROTOCOL=tcp)(HOST=ora12102)(PORT=14129))
Service "pdb1" has 1 instance(s).
Instance "CDB1", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
The command completed successfully
[grid@ora12102 ~]$


SQL> connect system/oracle@PDB1
Connected.
SQL> show con_id

CON_ID
------------------------------
3
SQL> show con_name

CON_NAME
------------------------------
PDB1
SQL> alter system set service_names='NEW_SVC_1,NEW_SVC_2';
alter system set service_names='NEW_SVC_1,NEW_SVC_2'
*
ERROR at line 1:
ORA-65040: operation not allowed from within a pluggable database


SQL> !oerr ora 65040
65040, 00000, "operation not allowed from within a pluggable database"
// *Cause: An operation was attempted that can only be performed in the root
// container.
// *Action: Switch to the root container to perform the operation.
//

SQL>


So, apparently, ORA-65040 means that we can't define SERVICE_NAMES for a PDB.

SQL> connect sys/oracle as sysdba
Connected.
SQL> show con_id

CON_ID
------------------------------
1
SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
SQL> alter system set service_names='NEW_SVC_1,NEW_SVC_2';

System altered.

SQL>

[grid@ora12102 ~]$ lsnrctl services

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 27-JUN-2016 22:53:56

Copyright (c) 1991, 2014, Oracle. All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
Services Summary...
Service "+ASM" has 1 instance(s).
Instance "+ASM", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
Service "CDB1" has 1 instance(s).
Instance "CDB1", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
Service "CDB1XDB" has 1 instance(s).
Instance "CDB1", status READY, has 1 handler(s) for this service...
Handler(s):
"D000" established:0 refused:0 current:0 max:1022 state:ready
DISPATCHER
(ADDRESS=(PROTOCOL=tcp)(HOST=ora12102)(PORT=14129))
Service "NEW_SVC_1" has 1 instance(s).
Instance "CDB1", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
Service "NEW_SVC_2" has 1 instance(s).
Instance "CDB1", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
The command completed successfully
[grid@ora12102 ~]$

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
[oracle@ora12102 Desktop]$ tnsping NEW_SVC_1

TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 27-JUN-2016 22:54:57

Copyright (c) 1997, 2014, Oracle. All rights reserved.

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = ora12102)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = NEW_SVC_1)))
OK (30 msec)
[oracle@ora12102 Desktop]$ sqlplus hemant/hemant@new_svc_1

SQL*Plus: Release 12.1.0.2.0 Production on Mon Jun 27 22:55:04 2016

Copyright (c) 1982, 2014, Oracle. All rights reserved.

ERROR:
ORA-01017: invalid username/password; logon denied


Enter user-name: system/oracle@new_svc_1
Last Successful login time: Mon Jun 27 2016 22:44:13 +08:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

SQL> show con_id

CON_ID
------------------------------
1
SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
SQL>


The new Services are actually running in the ROOT, not in the PDB .  Unlike my earlier blog post where I used the new services to connect to the PDB.

Therefore, for a PDB environment, I'd use DBMS_SERVICE as I've demonstrated here and here.
.
.
.



Categories: DBA Blogs

Services -- 3 : Monitoring Usage of Custom Services

Mon, 2016-06-20 10:04
In my previous blog post, I had demonstrated a few custom services created and started with DBMS_SERVICE.

Let's look at a couple of examples of monitoring usage of these services.

[oracle@ora12102 Desktop]$ sqlplus system/oracle@PDB1

SQL*Plus: Release 12.1.0.2.0 Production on Mon Jun 20 22:51:08 2016

Copyright (c) 1982, 2014, Oracle. All rights reserved.

Last Successful login time: Thu Jun 16 2016 23:23:50 +08:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

SQL> execute dbms_service.start_service('NEW_APP1');

PL/SQL procedure successfully completed.

SQL> execute dbms_service.start_service('FINANCE');

PL/SQL procedure successfully completed.

SQL> grant create table to hemant;

Grant succeeded.

SQL> grant select_Catalog_role to hemant;

Grant succeeded.

SQL>

[oracle@ora12102 Desktop]$ sqlplus hemant/hemant@NEW_APP1

SQL*Plus: Release 12.1.0.2.0 Production on Mon Jun 20 22:52:27 2016

Copyright (c) 1982, 2014, Oracle. All rights reserved.

Last Successful login time: Thu Jun 16 2016 23:28:01 +08:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

SQL> create table obj_t1 tablespace hemant as select * from dba_objects;

Table created.

SQL> insert into obj_t1 select * from obj_t1;

90935 rows created.

SQL>

[oracle@ora12102 Desktop]$ sqlplus hemant/hemant@FINANCE

SQL*Plus: Release 12.1.0.2.0 Production on Mon Jun 20 22:53:54 2016

Copyright (c) 1982, 2014, Oracle. All rights reserved.

Last Successful login time: Mon Jun 20 2016 22:52:27 +08:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

SQL> create table obj_t2_small tablespace hemant as select * from obj_T1 where rownum < 11;

Table created.

SQL>

SQL> show user
USER is "SYSTEM"
SQL> select sid,serial#, to_char(logon_time,'DD-MON HH24:MI:SS'), service_name
2 from v$session
3 where username = 'HEMANT'
4 order by logon_time
5 /

SID SERIAL# TO_CHAR(LOGON_TIME,'DD-M
---------- ---------- ------------------------
SERVICE_NAME
----------------------------------------------------------------
61 50587 20-JUN 22:52:27
NEW_APP1

76 43919 20-JUN 22:53:54
FINANCE


SQL>


Thus, we can see that V$SESSION tracks the SERVICE_NAME in use --- even though the USERNAME is the same in both sessions, the SERVICE_NAME is different.

SQL> col svc_name format a10
SQL> col stat_name format a25 trunc
SQL> select
2 con_id, service_name SVC_NAME, stat_name, value
3 from v$service_stats
4 where service_name in ('NEW_APP1','FINANCE')
5 and
6 (stat_name like 'DB%' or stat_name like '%block%' or stat_name like 'redo%')
7 order by 1,2,3
8 /

CON_ID SVC_NAME STAT_NAME VALUE
---------- ---------- ------------------------- ----------
3 FINANCE DB CPU 168973
3 FINANCE DB time 771742
3 FINANCE db block changes 653
3 FINANCE gc cr block receive time 0
3 FINANCE gc cr blocks received 0
3 FINANCE gc current block receive 0
3 FINANCE gc current blocks receive 0
3 FINANCE redo size 100484

CON_ID SVC_NAME STAT_NAME VALUE
---------- ---------- ------------------------- ----------
3 NEW_APP1 DB CPU 869867
3 NEW_APP1 DB time 17415363
3 NEW_APP1 db block changes 11101
3 NEW_APP1 gc cr block receive time 0
3 NEW_APP1 gc cr blocks received 0
3 NEW_APP1 gc current block receive 0
3 NEW_APP1 gc current blocks receive 0
3 NEW_APP1 redo size 25057520

16 rows selected.

SQL>


So, even some statistics (unfortunately, not all -- the last time I checked in 11.2) are reported at the Service Level.  Thus, I can see that the users of NEW_APP1 consumed more CPU and DB Time and generated more changes and redo than users of FINANCE !  (Obviously, V$SERVICE_STATS reports statistics from the beginning of the instance so you should either user StatsPack (I haven't verified StatsPack reporting of statistics by individual service) or AWR (if you have the Diagnostic Pack licence) or your own collection scripts to report statistics for a specific window of time).
.
.
.


Categories: DBA Blogs

Services -- 2 : Starting and Connecting to Services (non-RAC)

Thu, 2016-06-16 10:22
Continuing with the 12.1.0.2 non-RAC MultiTenant environment and two services demonstrated earlier,

I have restarted the environment today :

[grid@ora12102 ~]$ lsnrctl services

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 16-JUN-2016 22:57:17

Copyright (c) 1991, 2014, Oracle. All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
Services Summary...
Service "+ASM" has 1 instance(s).
Instance "+ASM", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
The command completed successfully
[grid@ora12102 ~]$

[oracle@ora12102 Desktop]$ sqlplus '/ as sysdba'

SQL*Plus: Release 12.1.0.2.0 Production on Thu Jun 16 22:57:31 2016

Copyright (c) 1982, 2014, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 1644167168 bytes
Fixed Size 2925024 bytes
Variable Size 1040191008 bytes
Database Buffers 587202560 bytes
Redo Buffers 13848576 bytes
Database mounted.
Database opened.
SQL> alter pluggable database open;
alter pluggable database open
*
ERROR at line 1:
ORA-65000: missing or invalid pluggable database name


SQL> alter pluggable database pdb1 open;

Pluggable database altered.

SQL>

grid@ora12102 ~]$ lsnrctl services

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 16-JUN-2016 23:00:11

Copyright (c) 1991, 2014, Oracle. All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
Services Summary...
Service "+ASM" has 1 instance(s).
Instance "+ASM", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
Service "CDB1" has 1 instance(s).
Instance "CDB1", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
Service "CDB1XDB" has 1 instance(s).
Instance "CDB1", status READY, has 1 handler(s) for this service...
Handler(s):
"D000" established:0 refused:0 current:0 max:1022 state:ready
DISPATCHER
(ADDRESS=(PROTOCOL=tcp)(HOST=ora12102)(PORT=9213))
Service "pdb1" has 1 instance(s).
Instance "CDB1", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
The command completed successfully
[grid@ora12102 ~]$


The two manually created services (NEW_APP1 and NEW_APP2) don't startup automatically.

SQL> exec dbms_service.start_service('NEW_APP1');
BEGIN dbms_service.start_service('NEW_APP1'); END;

*
ERROR at line 1:
ORA-44773: Cannot perform requested service operation.
ORA-06512: at "SYS.DBMS_SERVICE_ERR", line 63
ORA-06512: at "SYS.DBMS_SERVICE", line 395
ORA-06512: at line 1


SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
[oracle@ora12102 Desktop]$ sqlplus sys/oracle@PDB1 as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Thu Jun 16 23:03:05 2016

Copyright (c) 1982, 2014, Oracle. All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

SQL> exec dbms_service.start_service('NEW_APP1');

PL/SQL procedure successfully completed.

SQL> exec dbms_service.start_service('NEW_APP2');

PL/SQL procedure successfully completed.

SQL>


[grid@ora12102 ~]$ lsnrctl services

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 16-JUN-2016 23:04:36

Copyright (c) 1991, 2014, Oracle. All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
Services Summary...
Service "+ASM" has 1 instance(s).
Instance "+ASM", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
Service "CDB1" has 1 instance(s).
Instance "CDB1", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
Service "CDB1XDB" has 1 instance(s).
Instance "CDB1", status READY, has 1 handler(s) for this service...
Handler(s):
"D000" established:0 refused:0 current:0 max:1022 state:ready
DISPATCHER
(ADDRESS=(PROTOCOL=tcp)(HOST=ora12102)(PORT=9213))
Service "NEW_APP1" has 1 instance(s).
Instance "CDB1", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
Service "NEW_APP2" has 1 instance(s).
Instance "CDB1", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
Service "pdb1" has 1 instance(s).
Instance "CDB1", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
The command completed successfully
[grid@ora12102 ~]$


When I attempted to start the custom service (that, per the previous post, was created in PDB1) when still connected to CDB$ROOT, the command fails.  I had to connect to PDB1 to start the service.

SQL> create tablespace hemant ;                           

Tablespace created.

SQL> create user hemant identified by hemant default tablespace hemant quota unlimited on hemant;

User created.

SQL> grant create session to hemant;

Grant succeeded.

SQL>
[oracle@ora12102 Desktop]$ tnsping NEW_APP1

TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 16-JUN-2016 23:09:00

Copyright (c) 1997, 2014, Oracle. All rights reserved.

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = ora12102)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = NEW_APP1)))
OK (0 msec)
[oracle@ora12102 Desktop]$ tnsping NEW_APP2

TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 16-JUN-2016 23:09:05

Copyright (c) 1997, 2014, Oracle. All rights reserved.

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = ora12102)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = NEW_APP2)))
OK (0 msec)
[oracle@ora12102 Desktop]$
[oracle@ora12102 Desktop]$ sqlplus hemant/hemant@NEW_APP1

SQL*Plus: Release 12.1.0.2.0 Production on Thu Jun 16 23:09:29 2016

Copyright (c) 1982, 2014, Oracle. All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

SQL> show con_id

CON_ID
------------------------------
3
SQL> show con_name

CON_NAME
------------------------------
PDB1

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
[oracle@ora12102 Desktop]$ sqlplus hemant/hemant@NEW_APP2

SQL*Plus: Release 12.1.0.2.0 Production on Thu Jun 16 23:09:48 2016

Copyright (c) 1982, 2014, Oracle. All rights reserved.

Last Successful login time: Thu Jun 16 2016 23:09:29 +08:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

SQL> show con_id

CON_ID
------------------------------
3
SQL> show conn_name
SP2-0158: unknown SHOW option "conn_name"
SQL> show con_name

CON_NAME
------------------------------
PDB1
SQL>


The newly created user HEMANT belongs to the Database.  The user is not tied to a Service.  He can use either Service (whichever is running) to connect to the Database.  But the two Services have two different TNSNAMES.ORA entries --- differing by the SERVICE_NAME specification.

Thus, the DBA could configure some application servers to use one service name and other application servers to use another service name.

An example of such a configuration is where the first set of application servers could be for Finance Applications called "FINANCE" and the second set of servers could be for HR Applications (in the *same* database) called "HR".  Here I create the two services but start only the FINANCE service.

[oracle@ora12102 Desktop]$ sqlplus system/oracle@pdb1 

SQL*Plus: Release 12.1.0.2.0 Production on Thu Jun 16 23:13:55 2016

Copyright (c) 1982, 2014, Oracle. All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

SQL> exec dbms_service.create_service('FINANCE','FINANCE');

PL/SQL procedure successfully completed.

SQL> exec dbms_service.start_service('FINANCE');

PL/SQL procedure successfully completed.

SQL> exec dbms_service.create_service('HR','HR');

PL/SQL procedure successfully completed.

SQL>

[grid@ora12102 ~]$ lsnrctl services

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 16-JUN-2016 23:15:45

Copyright (c) 1991, 2014, Oracle. All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
Services Summary...
Service "+ASM" has 1 instance(s).
Instance "+ASM", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
Service "CDB1" has 1 instance(s).
Instance "CDB1", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
Service "CDB1XDB" has 1 instance(s).
Instance "CDB1", status READY, has 1 handler(s) for this service...
Handler(s):
"D000" established:0 refused:0 current:0 max:1022 state:ready
DISPATCHER
(ADDRESS=(PROTOCOL=tcp)(HOST=ora12102)(PORT=9213))
Service "FINANCE" has 1 instance(s).
Instance "CDB1", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
Service "NEW_APP1" has 1 instance(s).
Instance "CDB1", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
Service "NEW_APP2" has 1 instance(s).
Instance "CDB1", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
Service "pdb1" has 1 instance(s).
Instance "CDB1", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
The command completed successfully
[grid@ora12102 ~]$


Now the user should be able to connect to FINANCE, but not to HR.

[oracle@ora12102 Desktop]$ tnsping FINANCE

TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 16-JUN-2016 23:18:16

Copyright (c) 1997, 2014, Oracle. All rights reserved.

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = ora12102)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = FINANCE)))
OK (0 msec)
[oracle@ora12102 Desktop]$ tnsping HR

TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 16-JUN-2016 23:18:18

Copyright (c) 1997, 2014, Oracle. All rights reserved.

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = ora12102)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = HR)))
OK (0 msec)
[oracle@ora12102 Desktop]$
[oracle@ora12102 Desktop]$ sqlplus hemant/hemant@FINANCE

SQL*Plus: Release 12.1.0.2.0 Production on Thu Jun 16 23:18:57 2016

Copyright (c) 1982, 2014, Oracle. All rights reserved.

Last Successful login time: Thu Jun 16 2016 23:09:48 +08:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
[oracle@ora12102 Desktop]$
[oracle@ora12102 Desktop]$ sqlplus hemant/hemant@HR

SQL*Plus: Release 12.1.0.2.0 Production on Thu Jun 16 23:19:23 2016

Copyright (c) 1982, 2014, Oracle. All rights reserved.

ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor


Enter user-name:


The user could connect to FINANCE but the request to HR returned ORA-12514.
(Notice how "tnsping HR" is successful but the connection is not ?  That is because tnsping only tests if the listener is running, it does not test if the database instance and service are both running).

So, using multiple services, the DBA can "provision" the same database to multiple applications.  The entry-point is the SERVICE_NAME,  not the USERNAME.   Users and Services are independent of each other.

.
.
.
Categories: DBA Blogs

Services -- 1 : Services in non-RAC 12c MultiTenant

Tue, 2016-06-14 10:22
It is generally accepted that service definition is required in RAC environments.

However, the concept of Services was made available in 8i --- predating RAC.   Services can be defined in non-OPS / non-RAC / non-MultiTenant / MultiTenant environments.  A single PDB in a 12c MultiTenant database can host multiple services.

A quick start to the implementation.

Note : srvctl is to be used to create and manage services in a RAC environment.  srvctl registers the services with the Cluster Registry.  In a Non-RAC environment, use DBMS_SERVICE.

First, no services are running :

[grid@ora12102 ~]$ lsnrctl status

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 14-JUN-2016 23:14:48

Copyright (c) 1991, 2014, Oracle. All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date 14-JUN-2016 23:14:28
Uptime 0 days 0 hr. 0 min. 19 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Log File /u01/app/oracle/diag/tnslsnr/ora12102/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ora12102)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
Instance "+ASM", status READY, has 1 handler(s) for this service...
The command completed successfully
[grid@ora12102 ~]$


Next, I startup the MultiTenant CDB database.

[oracle@ora12102 ~]$ . oraenv
ORACLE_SID = [CDB1] ? CDB1
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@ora12102 ~]$ sqlplus '/ as sysdba'

SQL*Plus: Release 12.1.0.2.0 Production on Tue Jun 14 23:15:47 2016

Copyright (c) 1982, 2014, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 1644167168 bytes
Fixed Size 2925024 bytes
Variable Size 1056968224 bytes
Database Buffers 570425344 bytes
Redo Buffers 13848576 bytes
Database mounted.
Database opened.
SQL> alter pluggable database pdb1 open;

Pluggable database altered.

SQL>


[grid@ora12102 ~]$ lsnrctl status

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 14-JUN-2016 23:17:23

Copyright (c) 1991, 2014, Oracle. All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date 14-JUN-2016 23:14:28
Uptime 0 days 0 hr. 2 min. 54 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Log File /u01/app/oracle/diag/tnslsnr/ora12102/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ora12102)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=ora12102)(PORT=5501))(Security=(my_wallet_directory=/u01/app/oracle/admin/CDB1/xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "+ASM" has 1 instance(s).
Instance "+ASM", status READY, has 1 handler(s) for this service...
Service "CDB1" has 1 instance(s).
Instance "CDB1", status READY, has 1 handler(s) for this service...
Service "CDB1XDB" has 1 instance(s).
Instance "CDB1", status READY, has 1 handler(s) for this service...
Service "pdb1" has 1 instance(s).
Instance "CDB1", status READY, has 1 handler(s) for this service...
The command completed successfully
[grid@ora12102 ~]$


So, now I have the default pdb1 srevice for pluggable database PDB1 running.

Next, I manually create two new services and start them.

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
[oracle@ora12102 ~]$ sqlplus 'sys/oracle@PDB1 as sysdba'

SQL*Plus: Release 12.1.0.2.0 Production on Tue Jun 14 23:20:30 2016

Copyright (c) 1982, 2014, Oracle. All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

SQL> show con_id

CON_ID
------------------------------
3
SQL> show con_name

CON_NAME
------------------------------
PDB1
SQL>
SQL> exec dbms_service.create_service(service_name=>'NEW_APP1',network_name=>'NEW_APP1');

PL/SQL procedure successfully completed.

SQL> exec dbms_service.create_service('NEW_APP2','NEW_APP2');

PL/SQL procedure successfully completed.

SQL> exec dbms_service.start_service('NEW_APP1');

PL/SQL procedure successfully completed.

SQL> exec dbms_service.start_service('NEW_APP2');

PL/SQL procedure successfully completed.

SQL>
[grid@ora12102 ~]$ lsnrctl status

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 14-JUN-2016 23:22:54

Copyright (c) 1991, 2014, Oracle. All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date 14-JUN-2016 23:14:28
Uptime 0 days 0 hr. 8 min. 26 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Log File /u01/app/oracle/diag/tnslsnr/ora12102/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ora12102)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=ora12102)(PORT=5501))(Security=(my_wallet_directory=/u01/app/oracle/admin/CDB1/xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "+ASM" has 1 instance(s).
Instance "+ASM", status READY, has 1 handler(s) for this service...
Service "CDB1" has 1 instance(s).
Instance "CDB1", status READY, has 1 handler(s) for this service...
Service "CDB1XDB" has 1 instance(s).
Instance "CDB1", status READY, has 1 handler(s) for this service...
Service "NEW_APP1" has 1 instance(s).
Instance "CDB1", status READY, has 1 handler(s) for this service...
Service "NEW_APP2" has 1 instance(s).
Instance "CDB1", status READY, has 1 handler(s) for this service...
Service "pdb1" has 1 instance(s).
Instance "CDB1", status READY, has 1 handler(s) for this service...
The command completed successfully
[grid@ora12102 ~]$



The two new services NEW_APP1 and NEW_APP2 that I created in PDB1 are now registered with the listener.  Remember that these services are in the Database Instance CDB1.

My next blog post will be about using these services.

.
.
.

Categories: DBA Blogs

Data Recovery Advisor (11g)

Mon, 2016-06-13 03:13
Here's my YouTube video on using the 11g Data Recovery Advisor from the RMAN command line.

Other videos are in my YouTube Channel.

.
.
.

Categories: DBA Blogs

Compression -- 8 : DROPping a Column of a Compressed Table

Thu, 2016-06-02 09:56
Building on the series on Compression .....

What happens if we try to DROP a column in a Compressed Table ?  How can we execute the DROP ?

Starting with BASIC Compression.


SQL> connect hemant/hemant
Connected.
SQL> create table compress_basic as select * from source_data where 1=2;

Table created.

SQL> alter table compress_basic compress;

Table altered.

SQL> insert /*+ APPEND */ into compress_basic
2 select * from source_data
3 where rownum < 100001;

100000 rows created.

SQL> commit;

Commit complete.

SQL> select compression, compress_for
2 from user_tables
3 where table_name = 'COMPRESS_BASIC'
4 /

COMPRESS COMPRESS_FOR
-------- ------------------------------
ENABLED BASIC

SQL> alter table compress_basic drop (object_name);
alter table compress_basic drop (object_name)
*
ERROR at line 1:
ORA-39726: unsupported add/drop column operation on compressed tables


SQL>
SQL> !oerr ora 39726
39726, 00000, "unsupported add/drop column operation on compressed tables"
// *Cause: An unsupported add/drop column operation for compressed table
// was attemped.
// *Action: When adding a column, do not specify a default value.
// DROP column is only supported in the form of SET UNUSED column
// (meta-data drop column).

SQL>


So, I would have to set the column to UNUSED !

SQL> alter table compress_basic set unused column object_name;

Table altered.

SQL> alter table compress_basic drop (object_name);
alter table compress_basic drop (object_name)
*
ERROR at line 1:
ORA-00904: "OBJECT_NAME": invalid identifier


SQL> alter table compress_basic drop unused columns;
alter table compress_basic drop unused columns
*
ERROR at line 1:
ORA-39726: unsupported add/drop column operation on compressed tables


SQL>


I can't drop a column from a table with Compression enabled.

Is there another way ?

SQL> alter table compress_basic move nocompress;

Table altered.

SQL> alter table compress_basic drop unused columns;

Table altered.

SQL>


To actually execute the DROP, I have to Uncompress the table !

 So : Remember : You have to be careful when designing a table that you intend to Compress.  You won't be able to DROP columns !


Repeating the test case with OLTP Compression :

SQL> create table compress_oltp as select * from source_data where 1=2;

Table created.

SQL> alter table compress_oltp compress for oltp;

Table altered.

SQL> select compression, compress_for
2 from user_tables
3 where table_name = 'COMPRESS_OLTP'
4 /

COMPRESS COMPRESS_FOR
-------- ------------------------------
ENABLED ADVANCED

SQL>
SQL> insert into compress_oltp
2 select * from source_data
3 where rownum < 100001;

100000 rows created.

SQL> commit;

Commit complete.

SQL> alter table compress_oltp drop (object_name);

Table altered.

SQL>


Much easier, I could DROP the column.  But, wait.  Is there a catch ?

SQL> drop table compress_oltp purge;

Table dropped.

SQL> create table compress_oltp as select * from source_data where 1=2;

Table created.

SQL> alter table compress_oltp compress for oltp;

Table altered.

SQL> select compression, compress_for
2 from user_tables
3 where table_name = 'COMPRESS_OLTP'
4 /

COMPRESS COMPRESS_FOR
-------- ------------------------------
ENABLED ADVANCED

SQL> alter table compress_oltp nocompress;

Table altered.

SQL> select compression, compress_for
2 from user_tables
3 where table_name = 'COMPRESS_OLTP'
4 /

COMPRESS COMPRESS_FOR
-------- ------------------------------
DISABLED

SQL> alter table compress_oltp drop (object_name);
alter table compress_oltp drop (object_name)
*
ERROR at line 1:
ORA-39726: unsupported add/drop column operation on compressed tables


SQL>


If I ALTER the table to NOCOMPRESS (which can take effect only on *new* rows, not existing rows), I cannot DROP a column.  This is because Oracle is unsure if there is a mix of Comressed and Non-Compressed rows in the table now.

What I'd have to do is to rebuild it as a NOCOMPRESS table.

SQL> alter table compress_oltp move nocompress;

Table altered.

SQL> alter table compress_oltp drop (object_name);

Table altered.

SQL>


So, once a table is set to COMPRESS OLTP and then set to NOCOMPRESS, you can't simply DROP a column.


This test-case came out of an issue a friend of mine faced today.  He found that he couldn't drop a column from a table that was formerly set to COMPRESS FOR OLTP.

I pointed him to Support Document 1288918.1

.
.
.

Categories: DBA Blogs

Restore and Recovery from Incremental Backups : Video

Mon, 2016-05-30 03:04
A Youtube Video on Restore and Recovery from Incremental Backups.


Categories: DBA Blogs

TRUNCATEing a Table makes an UNUSABLE Index VALID again

Sun, 2016-05-22 10:54
Here's something I learned from Jonathan Lewis sometime ago.

If you set an Index to be UNUSABLE but later issue a TRUNCATE TABLE, the Index becomes VALID again --- i.e. the Index gets updated with rows subsequently inserted.

SQL> connect hemant/hemant
Connected.
SQL> drop table target_data purge;

Table dropped.

SQL> create table target_data as select * from source_data where 1=2;

Table created.

SQL> create index target_data_ndx_1
2 on target_data(owner, object_type, object_name);

Index created.

SQL> insert /*+ APPEND */ into target_data
2 select * from source_data;

367156 rows created.

SQL> commit;

Commit complete.

SQL> col segment_name format a30
SQL> select segment_name, segment_type, bytes/1048576
2 from user_segments
3 where segment_name like 'TARGET_DATA%'
4 order by 1;

SEGMENT_NAME SEGMENT_TYPE BYTES/1048576
------------------------------ ------------------ -------------
TARGET_DATA TABLE 49
TARGET_DATA_NDX_1 INDEX 19

SQL>
SQL> col index_name format a30
SQL> select index_name, status
2 from user_indexes
3 where table_name = 'TARGET_DATA';

INDEX_NAME STATUS
------------------------------ --------
TARGET_DATA_NDX_1 VALID

SQL>


So, I have a VALID Index on my Table.

I now make it UNUSABLE and add rows to it.

SQL> alter index target_Data_ndx_1 unusable;

Index altered.

SQL> select status
2 from user_indexes
3 where index_name = 'TARGET_DATA_NDX_1';

STATUS
--------
UNUSABLE

SQL> insert /*+ APPEND */ into target_data
2 select * from source_data;

367156 rows created.

SQL> commit;

Commit complete.

SQL> select index_name, status
2 from user_indexes
3 where table_name = 'TARGET_DATA';

INDEX_NAME STATUS
------------------------------ --------
TARGET_DATA_NDX_1 UNUSABLE

SQL> select segment_name, segment_type, bytes/1048576
2 from user_segments
3 where segment_name like 'TARGET_DATA%'
4 order by 1;

SEGMENT_NAME SEGMENT_TYPE BYTES/1048576
------------------------------ ------------------ -------------
TARGET_DATA TABLE 104

SQL>


Oracle actually drops the Index segment (so you don't see it in USER_SEGMENTS) when it is set to UNUSABLE alhough the Index definition is still present.  The Index doesn't "grow" as the Segment doesn't exist.

Let me TRUNCATE the table.

SQL> truncate table target_data;

Table truncated.

SQL> select segment_name, segment_type, bytes/1048576
2 from user_segments
3 where segment_name like 'TARGET_DATA%'
4 order by 1;

SEGMENT_NAME SEGMENT_TYPE BYTES/1048576
------------------------------ ------------------ -------------
TARGET_DATA TABLE .0625
TARGET_DATA_NDX_1 INDEX .0625

SQL> select index_name, status
2 from user_indexes
3 where table_name = 'TARGET_DATA';

INDEX_NAME STATUS
------------------------------ --------
TARGET_DATA_NDX_1 VALID

SQL>


Immediately after the TRUNCATE TABLE, the Index Segment is instantiated and the Index becomes VALID again.  So inserting rows will update the Index.  My last explicit command against the Index was ALTER INDEX ... UNUSABLE but that seems to be not the current state now !

SQL> insert /*+ APPEND */ into target_data
2 select * from source_data;

367156 rows created.

SQL> commit;

Commit complete.

SQL> select segment_name, segment_type, bytes/1048576
2 from user_segments
3 where segment_name like 'TARGET_DATA%'
4 order by 1;

SEGMENT_NAME SEGMENT_TYPE BYTES/1048576
------------------------------ ------------------ -------------
TARGET_DATA TABLE 49
TARGET_DATA_NDX_1 INDEX 19

SQL>


So, repopulating the Table has expanded the Index again.
.
.
.


Categories: DBA Blogs

Partition Storage -- 8 : Manually Sizing Partitions

Wed, 2016-05-18 10:44
As demonstrated in the first post in this series, the  default size for Table Partitions is to start with an I nitial8MB Extent.  The fourth post demonstrated how to resize a Partition that has already been created and populated.

Here is a method to pre-size the Initial Extent. I start with 3 partitions of different sizes.

SQL> create table my_part_tbl_init_sized (id_column number(6), data_column varchar2(100))
2 partition by range (id_column)
3 (partition p_100 values less than (101) segment creation immediate
4 storage (initial 64K next 64K) tablespace hemant,
5 partition p_200 values less than (201) segment creation immediate
6 storage (initial 1M next 1M) tablespace hemant,
7 partition p_max values less than (maxvalue) segment creation immediate
8 storage (initial 8M next 1M) tablespace hemant)
9 /

Table created.

SQL>
SQL> select partition_name, initial_extent, next_extent, tablespace_name
2 from user_tab_partitions
3 where table_name = upper('my_part_tbl_init_sized')
4 order by partition_position
5 /

PARTITION_NA INITIAL_EXTENT NEXT_EXTENT TABLESPACE_NAME
------------ -------------- ----------- ------------------------------
P_100 65536 65536 HEMANT
P_200 1048576 1048576 HEMANT
P_MAX 8388608 1048576 HEMANT

SQL>
SQL> select partition_name, bytes/1024, extents
2 from user_segments
3 where segment_name = upper('my_part_tbl_init_sized')
4 and segment_type = 'TABLE PARTITION'
5 order by 1
6 /

PARTITION_NA BYTES/1024 EXTENTS
------------ ---------- ----------
P_100 64 1
P_200 1024 1
P_MAX 8192 1

SQL>


However, the caveat about Partition Extent Sizes when SPLIT is used as shown in the third post still applies.

SQL> alter table my_part_tbl_init_sized
2 split partition p_max
3 at (301)
4 into (partition p_300, partition p_max)
5 /

Table altered.

SQL>
SQL> select partition_name, initial_extent, next_extent, tablespace_name
2 from user_tab_partitions
3 where table_name = upper('my_part_tbl_init_sized')
4 order by partition_position
5 /

PARTITION_NA INITIAL_EXTENT NEXT_EXTENT TABLESPACE_NAME
------------ -------------- ----------- ------------------------------
P_100 65536 65536 HEMANT
P_200 1048576 1048576 HEMANT
P_300 8388608 1048576 HEMANT
P_MAX 8388608 1048576 HEMANT

SQL>
SQL> select partition_name, bytes/1024, extents
2 from user_segments
3 where segment_name = upper('my_part_tbl_init_sized')
4 and segment_type = 'TABLE PARTITION'
5 order by 1
6 /

PARTITION_NA BYTES/1024 EXTENTS
------------ ---------- ----------
P_100 64 1
P_200 1024 1
P_300 8192 1
P_MAX 8192 1

SQL>


So, SPLITting P_MAX to create P_300 created P_300 with an 8MB extent because the sizing is inherited from P_MAX.  How do I change the Partition Extent sizes on SPLIT ?

SQL> alter table my_part_tbl_init_sized
2 split partition p_max
3 at (501)
4 into (partition p_500 storage (initial 64K next 64K) tablespace hemant, partition p_max)
5 /

Table altered.

SQL>
SQL> alter table my_part_tbl_init_sized
2 split partition p_500
3 at (401)
4 into (partition p_400, partition p_500)
5 /

Table altered.

SQL>
SQL> select partition_name, initial_extent, next_extent, tablespace_name
2 from user_tab_partitions
3 where table_name = upper('my_part_tbl_init_sized')
4 order by partition_position
5 /

PARTITION_NA INITIAL_EXTENT NEXT_EXTENT TABLESPACE_NAME
------------ -------------- ----------- ------------------------------
P_100 65536 65536 HEMANT
P_200 1048576 1048576 HEMANT
P_300 8388608 1048576 HEMANT
P_400 65536 65536 HEMANT
P_500 65536 65536 HEMANT
P_MAX 8388608 1048576 HEMANT

6 rows selected.

SQL> select partition_name, bytes/1024, extents
2 from user_segments
3 where segment_name = upper('my_part_tbl_init_sized')
4 and segment_type = 'TABLE PARTITION'
5 order by 1
6 /

PARTITION_NA BYTES/1024 EXTENTS
------------ ---------- ----------
P_100 64 1
P_200 1024 1
P_300 8192 1
P_400 64 1
P_500 64 1
P_MAX 8192 1

6 rows selected.

SQL>


This time, when I split P_MAX I deliberately specified a size for P_500.  Then, when I split P_500 further, the new P_400 inherited the sizing from P_500.
,
,
,

Categories: DBA Blogs

Compression -- 7 : Updating after BASIC Compression

Mon, 2016-05-09 09:57
In the first blog post on compression, I had shown BASIC Compression and also the effect of executing an UPDATE on a table with BASIC compression.

To expand on the them of UPDATEs of BASIC compression blocks ....

SQL> select count(*) from source_data;

COUNT(*)
----------
367156

SQL> create table target_comp row store compress basic as select * from source_data where 1=2;

Table created.

SQL> select pct_free from user_tables where table_name = 'TARGET_COMP';

PCT_FREE
----------
0

SQL> insert /*+ APPEND */ into target_comp
2 select * from source_data;

367156 rows created.

SQL> commit;

Commit complete.

SQL> insert /*+ APPEND */ into target_comp
2 select * from source_data;

367156 rows created.

SQL> commit;

Commit complete.

SQL> insert /*+ APPEND */ into target_comp
2 select * from source_data;

367156 rows created.

SQL> commit;

Commit complete.

SQL>
SQL> analyze table target_comp compute statistics;

Table analyzed.

SQL> select chain_cnt, blocks from user_tables where table_name = 'TARGET_COMP';

CHAIN_CNT BLOCKS
---------- ----------
0 4452

SQL>
SQL> exec dbms_stats.gather_table_stats('','TARGET_COMP');

PL/SQL procedure successfully completed.

SQL> select num_rows, chain_cnt, blocks from user_tables where table_name = 'TARGET_COMP';

NUM_ROWS CHAIN_CNT BLOCKS
---------- ---------- ----------
1101468 0 4452

SQL>


So we have a table with 1.1million rows and no Row Chaining.

What happens if we update about 20% of the rows ?

SQL> begin
2 for rec in (select rowid from target_comp where rownum < 220001)
3 loop
4 update target_comp set owner=owner where rowid=rec.rowid;
5 commit;
6 end loop;
7 end;
8 /

PL/SQL procedure successfully completed.

SQL>
SQL> commit;

Commit complete.

SQL> analyze table target_comp compute statistics;

Table analyzed.

SQL> select chain_cnt, blocks from user_tables where table_name = 'TARGET_COMP';

CHAIN_CNT BLOCKS
---------- ----------
202189 7882

SQL> exec dbms_stats.gather_table_stats('','TARGET_COMP');

PL/SQL procedure successfully completed.

SQL> select num_rows, chain_cnt, blocks from user_tables where table_name = 'TARGET_COMP';

NUM_ROWS CHAIN_CNT BLOCKS
---------- ---------- ----------
1101468 202189 7882

SQL>


I have updated 220,000 rows without actually increasing the notional length of each row (I set OWNER=OWNER).  Yet, The CHAIN_CNT is now 202K and the table's HighWaterMark has expanded from 4,452 blocks to 7,882 blocks.  A significant increase !
(YMMV may vary in your tests !)

It was Jonathan Lewis who suggested getting the Chain Count (or LIST CHAINED ROWS) to understand the impact of UPDATEs on a table with BASIC compression.
.
.
.


Categories: DBA Blogs

Compression -- 6b : Advanced Index Compression (revisited)

Thu, 2016-05-05 09:09
Following up on my earlier post on 12.1.0.2 Advanced Index Compression, one of my readers asked what would be the difference if I reversed the order of columns in the chosen index.

My defined index was on (OWNER, OBJECT_TYPE, OBJECT_NAME) --- defined as being from the column with the fewest distinct values to the most.  This ordering is best compressible with Index Key Compression (also known as Prefix Compression).  If I reverse the order, Index Key Compression for the two leading columns wouldn't deliver the same level of compression.  The question is whether Advanced Index Compression can intelligently handle the reversal.

SQL> create index target_data_ndx_3_comp on
2 target_data(object_name, object_type, owner) compress 2;

Index created.

SQL> exec dbms_stats.gather_index_stats('','TARGET_DATA_NDX_3_COMP');

PL/SQL procedure successfully completed.

SQL> select leaf_blocks
2 from user_indexes
3 where index_name = 'TARGET_DATA_NDX_3_COMP'
4 /

LEAF_BLOCKS
-----------
3091

SQL>


Surprisingly, this index with Prefix 2 on (OBJECT_NAME, OBJECT_TYPE) is, at 3,091 leaf blocks, smaller than the previous  index with Prefix 2 on (OWNER, OBJECT_TYPE) at 5,508 leaf blocks.

Continuing with Prefix 3

SQL> drop index target_data_ndx_3_comp;

Index dropped.

SQL> create index target_data_ndx_3_comp on
2 target_data(object_name, object_type, owner) compress 3;

Index created.

SQL> exec dbms_stats.gather_index_stats('','TARGET_DATA_NDX_3_COMP');

PL/SQL procedure successfully completed.

SQL> select leaf_blocks
2 from user_indexes
3 where index_name = 'TARGET_DATA_NDX_3_COMP'
4 /

LEAF_BLOCKS
-----------
2277

SQL>


At 2,277 leaf blocks it is, as expected, the same size with Prefix 3 on (OWNER, OBJECT_TYPE, OBJECT_NAME).  Since the entire index key is specified as the Prefix, both indexes would be the same size.

Going on to Advanced Index Compression

SQL> drop index target_data_ndx_3_comp;

Index dropped.

SQL> create index target_data_ndx_4_advcomp on
2 target_data(object_name, object_type, owner)
3 compress advanced low
4 /

Index created.

SQL> exec dbms_stats.gather_index_stats('','TARGET_DATA_NDX_4_ADVCOMP');

PL/SQL procedure successfully completed.

SQL> select leaf_blocks
2 from user_indexes
3 where index_name = 'TARGET_DATA_NDX_4_ADVCOMP'
4 /

LEAF_BLOCKS
-----------
2277

SQL>


This is, again, as expected.  Advanced Index Compression results in the same size irrespective of the ordering of the columns.

The advantage of Advanced Index Compression over Key or Prefix Compression is that the DBA does not need to determine the Prefix for compression.  He does not have to spend time to analyze the data and compare the number of distinct values for each of the columns in the composite index.
.
.
.

Categories: DBA Blogs

Pages