MANAGE DATABASE SERVICES USING DBMS_SERVICE IN A SINGLE INSTANCE DATABASE- MANUALLY

 MANAGE DATABASE SERVICES USING DBMS_SERVICE IN A SINGLE INSTANCE DATABASE

===========================================================================

ENTRY IN TNS -

ORCLSVC =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST =  192.195.35.212)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = SHARED)

      (SID = ORCL)

    )

  )

=========================================================================

VALIDATE LISTENER'S ENTRY

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1)) 

      (ADDRESS = (PROTOCOL = TCP)(HOST =  19.195.35.212)(PORT = 1521))

    )

  )

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (GLOBAL_DBNAME = orcl)

      (ORACLE_HOME = C:\db_home\ORA_19C)

      (SID_NAME = ORCL)

    )

  )

==

SET LOCAL LISTENER -

SQL> alter system set local_listener='(ADDRESS=(PROTOCOL=tcp)(HOST=192.195.35.212)(PORT=1521))' scope=both;

System altered.

SQL> alter system register;

System altered.  

===========================================================================

CREATE A SERVICE - 

BEGIN

  DBMS_SERVICE.create_service(

    service_name => 'ORCLSVC',

    network_name => 'ORCLSVC'

  );

END;

/

============

COLUMN name FORMAT A30

COLUMN network_name FORMAT A30

SELECT name,

       network_name

FROM   dba_services

ORDER BY 1;

NAME                           NETWORK_NAME

------------------------------ ------------------------------

ORCLSVC                        ORCLSVC

SYS$BACKGROUND

==============================================

SELECT name,

       network_name

FROM   v$active_services

ORDER BY 1;

----------

ORCLSVC                        ORCLSVC

SYS$BACKGROUND

SYS$USERS

------------------------------

****START a Service

BEGIN

  DBMS_SERVICE.start_service(

    service_name => 'ORCLSVC'

  );

END;

/

SELECT name,

       network_name

FROM   v$active_services

ORDER BY 1;

---------------------------------------

***Modify a Service

BEGIN

  DBMS_SERVICE.modify_service(

    service_name => 'ORCLSVC',

    goal         => DBMS_SERVICE.goal_throughput

  );

END;

/

----------------------------

***STOP A SERVICE

BEGIN

  DBMS_SERVICE.stop_service(

    service_name => 'ORCLSVC'

  );

END;

/

COLUMN name FORMAT A30

COLUMN network_name FORMAT A30

SELECT name,

       network_name

FROM   dba_services

ORDER BY 1;

SELECT name,

       network_name

FROM   v$active_services

ORDER BY 1;

--------------------------------

***DELETE A SERVICE-

BEGIN

  DBMS_SERVICE.delete_service(

    service_name => 'ORCLSVC'

  );

END;

/

COLUMN name FORMAT A30

COLUMN network_name FORMAT A30

SELECT name,

       network_name

FROM   dba_services

ORDER BY 1;

-----------------------

DISCONNECT SESSIONS-

The DISCONNECT_SESSION procedure disconnects all sessions currently connected to the service. The disconnection can take one of three forms, indicated by package constants.

POST_TRANSACTION : Sessions disconnect once their current transaction ends with a commit or rollback. This is the default value (0).

IMMEDIATE : Sessions disconnect immediately. Value (1).

NOREPLAY : Sessions disconnect immediately, and are flagged not to be replayed by application continuity. Value (2).

BEGIN

  DBMS_SERVICE.disconnect_session(

   service_name      => 'ORCLSVC',

   disconnect_option => DBMS_SERVICE.immediate

  );

END;

/

=========================================================================