dbms_service.create_service :
We can call dbms_service.create_service procedure to create new service names , then start these service names for user connections. The procedure dbms_service.create_service requires the service name and service network name, use the service name to manage the service name.
oracle@LINUX201:[~] $ sqlplus /"As sysdba"
SQL*Plus: Release 11.2.0.4.0 Production on Tue Apr 5 13:17:50 2016
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select SERVICE_ID,name from V$SERVICES;
SERVICE_ID NAME
---------- ----------------------------------------------------------------
4 ORCL
1 SYS$BACKGROUND
2 SYS$USERS
************************************************************
LEST CREATE A NEW DB SERVICE
************************************************************
SAMPLE :
BEGIN
DBMS_SERVICE.CREATE_SERVICE(service_name=>'QPDEV',
network_name=>'QPDEV.WORLD'); (or) network_name=>'QPDEV');
END;
/
SQL> begin
dbms_service.create_service('ORCLTEST','ORCLTEST');
end;
/
PL/SQL procedure successfully completed.
SQL> select SERVICE_ID,name from V$SERVICES;
SERVICE_ID NAME
---------- ----------------------------------------------------------------
4 ORCL
1 SYS$BACKGROUND
2 SYS$USERS
SQL> begin
DBMS_SERVICE.START_SERVICE('ORCL');
end;
/ 2 3 4
PL/SQL procedure successfully completed.
************************************************************
CHECK IF THE SERVICE IS CREATED AND STARTED
************************************************************
SQL> select SERVICE_ID,name from V$SERVICES;
SERVICE_ID NAME
---------- ----------------------------------------------------------------
7 ORCLTEST
4 ORCL
1 SYS$BACKGROUND
2 SYS$USERS
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
oracle@LINUX201:[~] $ cd $ORACLE_HOME/network/admin
oracle@LINUX201:[/u01/app/oracle/product/11.2.0/db_1/network/admin] $ ls
samples shrept.lst tnsnames.ora
************************************************************
LETS ADD THIS NEW SERVICE IN TO TNSNAMES FILE:
************************************************************
oracle@LINUX201:[/u01/app/oracle/product/11.2.0/db_1/network/admin] $ vi tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = LINUX201.world.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORCLTEST)
)
)
************************************************************
LETS TEST IT :
************************************************************
oracle@LINUX201:[/u01/app/oracle/product/11.2.0/db_1/network/admin] $ tnsping ORCLTEST
TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 05-APR-2016 13:21:31
Copyright (c) 1997, 2013, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = LINUX201)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ORCLTEST)))
OK (10 msec)
oracle@LINUX201:[/u01/app/oracle/product/11.2.0/db_1/network/admin] $ sqlplus atoorpu@ORCLTEST
SQL*Plus: Release 11.2.0.4.0 Production on Tue Apr 5 13:21:46 2016
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Enter password:
No comments :
Post a Comment