DBA Blogs

12c MultiTenant Posts -- 7 : Adding Custom Service to PDB (nonRAC/GI)

Hemant K Chitale - Sat, 2017-08-05 10:20
Earlier I have already demonstrated adding and managing custom services in a RAC environment in a blog post and a video.

But what if you are running Single Instance and not using Grid Infrastructure?  The srvctl command in Grid Infrastructure is what you'd use to add and manage services in RAC and Oracle Restart environments.  But with Grid Infrastructure, you can fall back on DBMS_SERVICE.

The DBMS_SERVICE API has been available since Oracle 8i -- when Services were introduced.

Here is a quick demo of some facilities with DBMS_SERVICE.

1.  Adding a Custom Service into a PDB :

$sqlplus system/oracle@NEWPDB

SQL*Plus: Release 12.2.0.1.0 Production on Sat Aug 5 22:52:21 2017

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

Last Successful login time: Mon Jul 10 2017 22:22:30 +08:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> show con_id

CON_ID
------------------------------
4
SQL>
SQL> execute dbms_service.create_service('HR','HR');

PL/SQL procedure successfully completed.

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

PL/SQL procedure successfully completed.

SQL>


Connecting to the service via tnsnames.

SQL> connect hemant/hemant@HR
Connected.
SQL> show con_id

CON_ID
------------------------------
4
SQL>


2.  Disconnecting all connected users on the Service

$sqlplus system/oracle@NEWPDB

SQL*Plus: Release 12.2.0.1.0 Production on Sat Aug 5 23:02:47 2017

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

Last Successful login time: Sat Aug 05 2017 23:02:28 +08:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL>
SQL> execute dbms_service.disconnect_session(-
> service_name=>'HR',disconnect_option=>DBMS_SERVICE.IMMEDIATE);

PL/SQL procedure successfully completed.

SQL>
In the HEMANT session connected to HR :
SQL> show con_id
ERROR:
ORA-03113: end-of-file on communication channel
Process ID: 5062
Session ID: 67 Serial number: 12744


SP2-1545: This feature requires Database availability.
SQL>


(Instead of DBMS_SERVICE.IMMEDIATE, we could also specify DBMS_SERVICE.POST_TRANSACTION).


3.  Shutting down a Service without closing the PDB :

SQL> execute dbms_service.stop_service('HR');

PL/SQL procedure successfully completed.

SQL>
SQL> connect hemant/hemant@HR
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor


Warning: You are no longer connected to ORACLE.
SQL>


Does restarting the Database, restart this custom service?

SQL> connect / as sysdba
Connected.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 838860800 bytes
Fixed Size 8798312 bytes
Variable Size 343936920 bytes
Database Buffers 478150656 bytes
Redo Buffers 7974912 bytes
Database mounted.
Database opened.
SQL> alter pluggable databas all open;
alter pluggable databas all open
*
ERROR at line 1:
ORA-02000: missing DATABASE keyword


SQL> alter pluggable database all open;

Pluggable database altered.

SQL> connect hemant/hemant@NEWPDB
Connected.
SQL> connect hemant/hemant@HR
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor


Warning: You are no longer connected to ORACLE.
SQL>
SQL> connect system/oracle@NEWPDB
Connected.
SQL> execute dbms_service.start_service('HR');

PL/SQL procedure successfully completed.

SQL> connect hemant/hemant@HR
Connected.
SQL>


I had to reSTART this custom service ('HR') after the PDB was OPENed.

Services is a facility that has been available since 8i non-OPS.  However, Services were apparently only being used by most sites in RAC environments.

Services allow you to run multiple "applications" (each application advertised as a Service) within the same (one) database.

Note that, in a RAC environment, srvctl configuration of Services can configure auto-restart of the Service.
.
.
.

Categories: DBA Blogs

Database links without specifying password (using Oracle Wallet)

Tom Kyte - Sat, 2017-08-05 05:46
Is it possible to create a database link without specifying the password (say somehow using a oracle wallet)? As of now we use passwords for everything - JDBC connection database connections (languages other than Java) Creating database links ...
Categories: DBA Blogs

listagg gives ORA-01427: single-row subquery returns more than one row

Tom Kyte - Sat, 2017-08-05 05:46
I need to concatenate row field into one field and I'm trying to use LISTAGG, but I need values to be distinct in the list. I was able to do almost everything with regexp_replace as alternative, but when I have too many orders for a customer I would...
Categories: DBA Blogs

How to run a update query without commit at the end , inside my pl/sql block multiple times without waiting for lock ?

Tom Kyte - Sat, 2017-08-05 05:46
I have an update query inside a pl/SQL block. The pl/SQL block is optimised to execute within 800 ms.I have tested the code and it executes fine. However, if my code is put to test on regression it is taking huge time to complete. My code is bein...
Categories: DBA Blogs

Reports - web.show_document userid password contains character .#

Tom Kyte - Sat, 2017-08-05 05:46
We use web.show_document to view reports and there are users who have a # character in their password. For these users comes a message rep-0501, for the other users who do not have that character in their password everything works fine. Here I show t...
Categories: DBA Blogs

Alternative for CLOB data type in oracle 12g

Tom Kyte - Sat, 2017-08-05 05:46
I wanted to know what is the best alternative data type for CLOB? My current database have a few CLOB data type. Does CLOB Data type is going to be deprecated in newer version? I tried to search around and seems like varchar2 will be the alte...
Categories: DBA Blogs

Is there a UTL_MAIL connection limit?

Tom Kyte - Sat, 2017-08-05 05:46
Hi, We recently encountered a connection limit on UTL_SMTP of 16 open connections. This is not because connections are being left open its just that we have reached a threshold of the number of applications utilising the UTL_SMTP package on our o...
Categories: DBA Blogs

LiveSQL: Accepting Input From User

Tom Kyte - Sat, 2017-08-05 05:46
I am not able to accept input from user on Live SQL Platform I have tried & and : both but i am not able to accept the input from user. Please suggest me the syntax for the same. Thanks in Advance
Categories: DBA Blogs

SELECT CASE INTO

Tom Kyte - Fri, 2017-08-04 11:26
How can I use a SELECT CASE INTO to store a value in a local variable?
Categories: DBA Blogs

Poor performance got worse after using alter table shrink space cascade, why?

Tom Kyte - Fri, 2017-08-04 11:26
We have an Oracle 12g database without partitioning (another issue due to cost) which has one table that got very large before we deleted unwanted rows. The table grew to over 150 million rows and was over 50% of the database (200GB allocated). Use...
Categories: DBA Blogs

How to make a use of Pipelined Table functions (that implement ODCI table interface) in PL/SQL?

Tom Kyte - Fri, 2017-08-04 11:26
I have created a pipelined table function similar to the well known Anton Scheffer's PIVOT function and it works as expected (in SQL*Plus) When I tried to use it in PL/SQL to open a cursor or bulk fetch, it fails with the following exception: <b> ...
Categories: DBA Blogs

Identity Column Next Value

Tom Kyte - Fri, 2017-08-04 11:26
Hi Tom, I just wanted to know, How to find next value from identity for any particular column ?
Categories: DBA Blogs

Names of Identity Column

Tom Kyte - Fri, 2017-08-04 11:26
Hi TOM, if I want to create one table with identity column so can i define name for identity ?
Categories: DBA Blogs

How to identify null columns vs null rows in left join

Tom Kyte - Fri, 2017-08-04 11:26
Hi Chris/Connor, I have a below query output: <code>select tab1.usr, tab2.salary from tab1 LEFT JOIN tab2 ON (tab1.usr = tab2.usr and tab1.name = tab2.name); USR salary ----- ------- 1111 5001 2222 NULL 3333 NULL Since usr: 22...
Categories: DBA Blogs

Oracle FY18 Partners Immersion Training

This Oracle fiscal year, the FY18 Partners Immersion Training to our OPN partners promises to be a modern learning engagement designed to provide the building blocks to: Articulate the...

We share our skills to maximize your revenue!
Categories: DBA Blogs

Duplicate records in MLOG

Tom Kyte - Thu, 2017-08-03 17:06
Is it possible to restrict creation of duplicate record in MLOG. In our scenario, same record is updated twice then it is creating two record in MLOG and fast refresh is taking longer time. Is it possible if it create only one record in MLOG n...
Categories: DBA Blogs

Materialized view

Tom Kyte - Thu, 2017-08-03 17:06
Hi Tom Just wanted to know whether materialized view will be available for querying during refresh if refresh is performed using atomic refresh=>false..?
Categories: DBA Blogs

SQL_Target,tablespace Question

Tom Kyte - Thu, 2017-08-03 17:06
1)What happen when SGA_MAX_SIZE & SGA_TARGET not define under Init parameter file? 2)When extending datafile with Auto extend on command without specifying size what will happnen?
Categories: DBA Blogs

ENABLE PARALLEL DML Vs FORCE PARALLEL DML

Tom Kyte - Thu, 2017-08-03 17:06
Hi Chris/Connor, I came across below two piece of codes where only difference is "ALTER SESSION FORCE PARALLEL DML PARALLEL 16" Can you please help to understand if we need to perform DELTE using parallelism, do i need to write both ALTER SESSI...
Categories: DBA Blogs

update bulk row based on case statement

Tom Kyte - Thu, 2017-08-03 17:06
Hello Experts, We have a requirement as per below example, For Example, Need to create a procedure which fulfill below condition, create or replace procedure as <declaration part> Begin select emp_id,emp_name,mgr_id, (case when dep...
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs