Using the DBMS_METADATA to extract DLL from table and Index

SQL> set long 4000
SQL> select dbms_metadata.get_DDL( 'TABLE','EMP','HR') from dual;

DBMS_METADATA.GET_DDL('TABLE','EMP','HR')
-----------------------------------------------------------------------------

CREATE TABLE "HR"."EMP"
( "ID" NUMBER(10,0),
"NAME" VARCHAR2(30),
"EMAIL" VARCHAR2(100),
"SALARY" NUMBER(10,2)
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS"

SQL> select dbms_metadata.get_DDL( 'INDEX','EMP_EMP_ID_PK','HR') from dual;

DBMS_METADATA.GET_DDL('INDEX','EMP_EMP_ID_PK','HR')
--------------------------------------------------------------------------------

CREATE UNIQUE INDEX "HR"."EMP_EMP_ID_PK" ON "HR"."EMPLOYEES" ("EMPLOYEE_ID")

Add second listener on a database in different port

Document Scope: This document describes how to create and add a second listener
Document Type: How to
Last Update: 14-11-2006
References: http://download-uk.oracle.com/docs/cd/B19306_01/network.102/b14212/listenercfg.htm#sthref946
Metalink doc:
Created: 14-11-2006
Database: 10.2.0.1
Platform: Solaris SPARC 64Bit
Application Server: none

Author: Michael Georgiou
Comments: Also covers and dbconsole issues , because the dbconsole is configure to listener to LISTENER on port 1521.

=================================================================================
Contents

I need a second listener because the node that I worked has two oracle software one for oracle9i and for oracle10g. I leave the listener LISTENER for the oracle9i software and I create a new one LISTERNER10G for oracle10g software.
I create the second listener via netca and I statically configure the database sid via netmgr .After that I made the following changes in init.ora and tnsnames.ora file.
tnsnames.ora

LISTENER10G=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.40.250)(PORT=1526)))


alter system set LOCAL_LISTENER= LISTENER10G scope=both;





Some very importand database parameters are the following.

service_names=DB_SISII
db_name=DB_SISII
instance_name=DBSISII

Note that when I create the database I set database name as DB_SISII and SID as DBSISII


Lsnrctl>set current_listener LISTENER10G
Lsnrctl>services
Service "DB_SISII" has 2 instance(s).
Instance "DBSISII", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
Instance "DBSISII", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER

I can access the database via the following tnsnames.ora entry

DBSISII =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.40.250)(PORT = 1526))
)
(CONNECT_DATA =
(SERVICE_NAME = DB_SISII)
)
)


Some issues exist for database console and may be able to be solved by:

/home/oracle10g/product/10.2.0/netudevsrv1_DBSISII/sysman/emd

Edit the target.xml





















/home/oracle10g/product/10.2.0/netudevsrv1_DBSISII/sysman/config

Edit the following line in emoms.properties

oracle.sysman.eml.mntr.emdRepConnectDescriptor=(DESCRIPTION\=(ADDRESS_LIST\=(ADDRESS\=(PROTOCOL\=TCP)(HOST\=netudevsrv1)(PORT
\=1526)))(CONNECT_DATA\=(SERVICE_NAME\=DB_SISII)))

emctl stop dbconsole
emctl start dbconsole