How to Get DDL for Function and Packages Body in oracle Database.

How to Get DDL for Function and  Packages Body in oracle Database.


SET LONG 200000000 LONGCHUNKSIZE 2000000 PAGESIZE 0 LINESIZE 10000000000 FEEDBACK OFF
spool nil1w4.sql
select dbms_metadata.get_ddl('FUNCTION','CCSAR','ERP') from dual;

SET LONG 20000

set pagesiz 180

select replace(dbms_metadata.get_ddl ('PACKAGE_BODY', 'GEN_EXT', 'nil_APP'),' ','') from dual;

set heading off

set echo off

set flush off

--set pagesize 8000

set pagesize 0

set linesize 80

--set linesize 980

--set long 3999

set long 99999

set longc 3999

spool Code_chkpb.sql

select replace(dbms_metadata.get_ddl ('PACKAGE_BODY', 'GEN_EXT', 'nilesh_APP'),' ',' ') from dual;

To Compile package.

ALTER PACKAGE BODY VT.JOHN COMPILE;   

To Drop a Package,function  in the Database.

Drop PACKAGE VTXUSR_APP.GEN_SALES_TAX_EXTRACT;

DROP FUNCTION  erp."nil";

           

How To download the OEL linux from oracle website

How To download the OEL linux from oracle website

Two ways.

1)Download Oracle Linux from Oracle Linux Yum Server
2)Download from Oracle Software Delivery Cloud


1)Download Oracle Linux from Oracle Linux Yum Server.

source to download :- http://yum.oracle.com/oracle-linux-isos.html
1) Please create your oracle account
https://www.oracle.com/index.html
2) Most the common way to download the oracle software
3)Its contain full ISO image and boot ISO images for latest OEL OS.
4) Generally i am used this link to download the latest OEL.


2)Download from Oracle Software Delivery Cloud

1) This is the Latest way to download the OEL OS software from oracle cloud
2) To download we have multiple way but generally i  used Using a Download Manager
3) Please use Download manager to download the OEL software.

Local Listener & Sqlnet.ora And NAMES.DEFAULT_DOMAIN.

Local Listener & Sqlnet.ora And NAMES.DEFAULT_DOMAIN.


DEMO :1  !!!!!

SQLNET.ORA file without NAMES.DEFAULT_DOMAIN=world parameter 


[oracle@rac1 admin]$ cat sqlnet.ora
# sqlnet.ora Network Configuration File: /oracle/DEA/12102/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.

SQLNET.USE_ZERO_COPY_IO = 0

NAMES.DIRECTORY_PATH= (TNSNAMES, HOSTNAME)

ADR_BASE = /oracle/DEA



No Local_listener value has been set.

SQL> show parameter listener

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
listener_networks                     string
local_listener                            string
remote_listener                        string



[oracle@rac1 admin]$ cat tnsnames.ora

LISTENER.WORLD=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=rac1.example.com)(PORT=1521)))

[oracle@rac1 admin]$

Lets set the Local_listener Value 

SQL> show parameter listener

NAME                                 TYPE        VALUE

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

listener_networks                    string

local_listener                       string

remote_listener                      string

SQL> alter system set local_listener='LISTENER' scope=both;

alter system set local_listener='LISTENER' scope=both

*

ERROR at line 1:

ORA-02097: parameter cannot be modified because specified value is invalid

ORA-00119: invalid specification for system parameter LOCAL_LISTENER

ORA-00132: syntax error or unresolved network name 'LISTENER'

Lets Update the tnsnames.ora  and remove the WORLD from Listener. 

[oracle@rac1 admin]$ cat tnsnames.ora

LISTENER=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=rac1.example.com)(PORT=1521))

SQL> show parameter listener

NAME                                 TYPE        VALUE

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

listener_networks                    string

local_listener                       string

remote_listener                      string

SQL> alter system set local_listener='LISTENER' scope=both;

System altered.

SQL>  show parameter listener

NAME                                 TYPE        VALUE

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

listener_networks                    string

local_listener                       string      LISTENER

remote_listener                      string

Oh yeah!!!! I am able to resolve the Local listener error Bravo.!!!!

DEMO 2:!!!!!

SQLNET.ORA file with Value  NAMES.DEFAULT_DOMAIN=world parameter .


[oracle@rac1 admin]$ pwd
/oracle/DEA/121/network/admin
[oracle@rac1 admin]$ cat sqlnet.ora
# sqlnet.ora Network Configuration File: /oracle/DEA/12102/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.

SQLNET.USE_ZERO_COPY_IO = 0

NAMES.DIRECTORY_PATH= (TNSNAMES, HOSTNAME)

ADR_BASE = /oracle/DEA
NAMES.DEFAULT_DOMAIN=world

[oracle@rac1 admin]$


And will use the same tnsnames.ora

[oracle@rac1 admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /oracle/DEA/12102/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

LISTENER=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=rac1.example.com)(PORT=1521))).


Lets Bounce a Database.

[oracle@rac1 admin]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Wed Jul 1 09:36:56 2020

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, OLAP, Advanced Analytics and Real Application Testing options


SQL> startup force
ORA-00119: invalid specification for system parameter LOCAL_LISTENER
ORA-00132: syntax error or unresolved network name 'LISTENER'
SQL> shut immediate
ORA-01012: not logged on
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
[oracle@rac1 admin]$
[oracle@rac1 admin]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Wed Jul 1 09:37:23 2020

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

Connected to an idle instance.

SQL> startup
ORA-00119: invalid specification for system parameter LOCAL_LISTENER
ORA-00132: syntax error or unresolved network name 'LISTENER'

SQL>

OH!!! Again Error Why!!!!!?? because the NAMES.DEFAULT_DOMAIN=world value set  and LISTENER is without WORLD.

[oracle@rac1 admin]$ cat sqlnet.ora
# sqlnet.ora Network Configuration File: /oracle/DEA/12102/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.

SQLNET.USE_ZERO_COPY_IO = 0

NAMES.DIRECTORY_PATH= (TNSNAMES, HOSTNAME)

ADR_BASE = /oracle/DEA

NAMES.DEFAULT_DOMAIN=world

So lets resolve this issue ,

Update the

[oracle@rac1 admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /oracle/DEA/12102/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

LISTENER.WORLD=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=rac1.example.com)(PORT=1521))).

[oracle@rac1 admin]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Wed Jul 1 09:41:16 2020

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>

[oracle@rac1 admin]$ tnsping LISTENER

TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 01-JUL-2020 09:46:40

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

Used parameter files:

/oracle/DEA/12102/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=rac1.example.com)(PORT=1521)))

OK (0 msec)

[oracle@rac1 admin]$

Oh!!! Bravo We Able to start up database.