Generate DDL for any Object using DMS_METADATA Oracle Package

You can get DDL for any object in Oracle Database using the package DBMS_METADATA.get_ddl procedure. Here is an example.

SQL> set serveroutput on;
SQL> DECLARE
  2     dummy                         CLOB;
  3  BEGIN
  4     dummy                      :=
  5                  DBMS_METADATA.get_ddl ('TABLE',        - Object Type
  6                                         'EMP',                        - Table Name
  7                                         'APP_D01'                        - Schema Name
  8                                        );
  9     DBMS_OUTPUT.put_line (DBMS_LOB.SUBSTR (dummy,
 10                                            4000,
 11                                            1
 12                                           ));
 13  END;
 14  /

  CREATE TABLE "APP_D01"."EMP"
   (    "PARAM_NM" VARCHAR2(10) NOT
NULL ENABLE,
        "DESC_TXT" VARCHAR2(50) NOT NULL ENABLE,
        "PARAM_TYP"
VARCHAR2(1) NOT NULL ENABLE,
        "PARAM_VAL" VARCHAR2(80) NOT NULL ENABLE,

CONSTRAINT "EMP_PK" PRIMARY KEY ("PARAM_NM")
  USING INDEX PCTFREE 10
INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576
MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT)
  TABLESPACE "CTS_VSD01"  ENABLE,
         CONSTRAINT
"ATLS_PRM_TYP_CK" CHECK (param_typ in ('V','I','F','B')  ) ENABLE
   ) PCTFREE
10 PCTUSED 70 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 "CTS_VSD01"


PL/SQL procedure successfully completed.

SQL>


Published with Blogger-droid v1.6.8

Get and Read file names in a directory using SQL - PL/SQL

I got this interesting method to list / access all files in a user specified directory using SQL and PL/SQL. Here is the reference and method

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:439619916584

First you need to grant JAVAUSERPRIV to schema user as sys

GRANT JAVAUSERPRIV to app_owner;


Connect as schema user and execute these scripts


SQL> CREATE GLOBAL TEMPORARY TABLE dir_list
  2       ( filename VARCHAR2(255) )
  3      ON COMMIT DELETE ROWS
  4  /

Table created.

SQL> create or replace
  2          and compile java source named "DirList"
  3      as
  4      import java.io.*;
  5      import java.sql.*;
  6
  7      public class DirList
  8      {
  9      public static void getList(String directory)
 10                        throws SQLException
 11     {
 12         File path = new File( directory );
 13         String[] list = path.list();
 14        String element;
 15
 16        for(int i = 0; i < list.length; i++)
 17         {
 18             element = list[i];
 19             #sql { INSERT INTO DIR_LIST (FILENAME)
 20                    VALUES (:element) };
 21         }
 22     }
 23
 24     }
 25     /

Java created.

SQL> create or replace
  2       procedure get_dir_list( p_directory in varchar2 )
  3    as language java
  4      name 'DirList.getList( java.lang.String )';
  5     /

Procedure created.

SQL> exec get_dir_list('/applns/oracle/dbs');

PL/SQL procedure successfully completed.

SQL> select * from dir_list;

FILENAME
--------------------------------------------------------------------------------

initdw.ora
init.ora
orapwCTSD1
lkCTSD1
hc_CTSD1.dat
spfileCTSD1.ora
orapwMVS01
orapwC9W01
lkC9W01
hc_C9W01.dat
spfileC9W01.ora

FILENAME
--------------------------------------------------------------------------------

lkMVS01
hc_MVS01.dat
spfileMVS01.ora
orapwCWY01
lkCWY01
hc_CWY01.dat
spfileCWY01.ora
orapwCVS01
orapwM9W01
lkM9W01
hc_M9W01.dat

FILENAME
--------------------------------------------------------------------------------

spfileM9W01.ora
lkCVS01
hc_CVS01.dat
spfileCVS01.ora
orapwCCX01
lkCCX01
hc_CCX01.dat
spfileCCX01.ora
lkinstMCSEK
hc_MNH01.dat
initCVS01.ora

FILENAME
--------------------------------------------------------------------------------

hc_MEK01.dat
hc_MCSEK.dat
initCTSD1.ora
test
cts.dmp
lkinstMEK01
hc_MVS02.dat
hc_CCM1.dat
hc_MCM1.dat
initCWY01.ora

43 rows selected.

SQL>

ORA-12518: TNS: listener could not hand off client connection

When you hit this error "ORA-12518: TNS: listener could not hand off client connection" ,Check the alert log.In my case the reason was , ORA 00020 – Maximum number of processes exceeded.When I queried the number of sessions, I can see that the number was more than 150, which I specified in my init.ora file.This prevents further connections to this service, resulting in ORA-12518: TNS: listener could not hand off client connection.

If my database runs in windows do this.

Work around :Restart the Oracle Service in windows

The reason for this error could be limited server resources or configuration. Try to check these

1. Database server memory were enough for new connection
2. Database connection parameters such as
       2.1 dedicated server, pls checked processes parameter or query from v$resource_limit where resource_name='processes'; checked max_utilization must less than Limit_value or
       2.2 shared server, pls verify large_pool_size or check dispatcher were enough for all connection.

3. You may check related log such as listener.log, alert log, trace file, etc

Oracle Index Rebuild and Table Write

A big table ( 10 GB ) which has an index on a number field. Index is almost same size as the table and I want to rebuild the index.The database is very active and the table is also very active read about 100K times in 20 minutes and few writes as well.Index rebuild may be needed when  there are a significant number of deletes, and also do significant index range scans . When index rebuild process, does oracle allow writing to the table ( insert into that table) ?

We can use the online keyword then DML can happen concurrently,if not, then not.

ALTER INDEX ... COALESCE instead of REBUILD.  It is transactional and can be executed concurrently with DML with much less risk (and less licensing) than ALTER INDEX ... REBUILD ... ONLINE

How to create Oracle Database Link

A database link is a schema object in one database that enables you to access objects on another database. The other database need not be an Oracle Database system. However, to access non-Oracle systems you must use Oracle Heterogeneous Services.

Once you have created a database link, you can use it to refer to tables and views on the other database. In SQL statements, you can refer to a table or view on the other database by appending @dblink to the table or view name. You can query a table or view on the other database with the SELECT statement. You can also access remote tables and views using any INSERT, UPDATE, DELETE, or LOCK TABLE statement.

To create a private database link, you must have the CREATE DATABASE LINK system privilege. To create a public database link, you must have the CREATE PUBLIC DATABASE LINK system privilege. Also, you must have the CREATE SESSION system privilege on the remote Oracle database.

Oracle Net must be installed on both the local and remote Oracle databases.

This is how you can create a database link .

CREATE DATABASE LINK prod_test
        CONNECT TO schema_user IDENTIFIED BY user_password
        USING '(DESCRIPTION=
                (ADDRESS_LIST=
                        (ADDRESS=(PROTOCOL=TCP)(HOST=<target database host>)(PORT=<target database  port>))
                )
                (CONNECT_DATA=(SID=<target database ORACLE_SID>))
                )'
;

Oracle SQL Query to get Date Range as Single row Block

Here is an Oracle SQL Query to return rows , with each day as a single row range value. This query can be used in reporting , when things on different day has to be queried as a date range and as a single row.

create table test_acy(acy_cd varchar2(5),start_dt date,end_dte date);

insert into test_acy values('AB','01Jan2010','02Jan2010');
insert into test_acy values('AB','02Jan2010','03Jan2010');
insert into test_acy values('AB','03Jan2010','04Jan2010');
insert into test_acy values('AB','06Jan2010','07Jan2010');
insert into test_acy values('AB','09Jan2010','10Jan2010');
insert into test_acy values('AB','10Jan2010','11Jan2010');

insert into test_acy values('CD','11Jan2010','12Jan2010');

If My table has rows like this, and would like to get rows as

AB        01Jan2010        04Jan2010
AB        06Jan2010        07Jan2010
AB        09Jan2010        11Jan2010
CD        11Jan2010        12Jan2010.

SQL Query for this
SELECT   acy_cd,
         MIN(start_dt),
         MAX(end_dte)
FROM     (SELECT acy_cd,
                 start_dt,
                 end_dte,
                 SUM(win) OVER(PARTITION BY acy_cd ORDER BY start_dt)
                                                                    win_group
          FROM   (SELECT   acy_cd,
                           start_dt,
                           end_dte,
                           CASE
                              WHEN start_dt !=
                                     LAG(end_dte) OVER(PARTITION BY acy_cd ORDER BY start_dt)
                                 THEN 1
                              ELSE 0
                           END win
                  FROM     test_acy
                  ORDER BY acy_cd, start_dt))
GROUP BY acy_cd, win_group

OR

SELECT   acy_cd,
         MIN(start_dt) start_dt,
         MAX(end_dte) end_dte
FROM     (SELECT     LEVEL lvl,
                     t.*,
                     SUBSTR(SYS_CONNECT_BY_PATH(TO_CHAR(t.start_dt,
                                                        'DDMONYYYY'),
                                                ','),
                            2,
                            9
                           ) path_root
          FROM       test_acy t
          CONNECT BY acy_cd = PRIOR acy_cd  AND
                     start_dt = PRIOR end_dte
          START WITH t.start_dt IN(SELECT t2.start_dt
                                   FROM   test_acy t2 LEFT OUTER JOIN test_acy t3
                                          ON t2.start_dt = t3.end_dte AND
                                            t2.acy_cd = t3.acy_cd
                                   WHERE  t3.end_dte IS NULL))
GROUP BY acy_cd, path_root
/

ACY_CD        START_DT        END_DTE

AB        1/6/2010        1/7/2010
AB        1/9/2010        1/11/2010
AB        1/1/2010        1/4/2010
CD        1/11/2010        1/12/2010



 
 

Move Oracle Datafile to a New location/Partition

You may want to move oracle datafiles to a new disk partition or a new location at some point of time, when your disk space is full or when you are doing a server administration.

Do the Steps shown below

1.Generate the move Data files command as shown below
        SELECT    'ALTER DATABASE RENAME FILE'
                       || NAME
                       || ' TO  <new location>'
                       || NAME
                FROM   v$datafile
2.Shutdown the database
3.Copy or move the data file to its new location. On Unix this can be done with the "dd" command.
        Example in Unix , you can use the dd command :
         dd if=/old/location of=/new/location bs=4096
4. Start up the database in mount state
        startup mount;
5.Move the datafile - Use the statements generated in first step for this.
6. ALTER DATABASE RENAME FILE '/old/location' TO '/new/location';
7. Open the database
ALTER DATABASE OPEN;



 
 

Oracle Views - Updatable Views

An updatable in Oralce is a  view  you can use to insert, update, or delete base table rows. You can create a view to be inherently updatable, or you can create an INSTEAD OF trigger on any view to make it updatable.

To learn whether and in what ways the columns of an inherently updatable view can be modified, query the USER_UPDATABLE_COLUMNS data dictionary view. The information displayed by this view is meaningful only for inherently updatable views. For a view to be inherently updatable, the following conditions must be met:

·        Each column in the view must map to a column of a single table. For example, if a view column maps to the output of a TABLE clause (an unnested collection), then the view is not inherently updatable.

·        The view must not contain any of the following constructs:

A set operator
A DISTINCT operator
An aggregate or analytic function
A GROUP BY, ORDER BY, MODEL, CONNECT BY, or START WITH clause
A collection expression in a SELECT list
A subquery in a SELECT list
A subquery designated WITH READ ONLY

 
 

OC4J Configuration issue, EM Configuration issue emctl - Oracle Enterprise Manager

This is how I solved this issue . I have mentioned below my server details and how I get around this issue

1.Linux x86 64 Bit Operating System

2. I have two Oracle Homes - One for Oracle 10G R2 and other Oracle 11G R2

I have seen this error, even when we set the correct ORACLE_HOME and ORACLE_SID and when we try to start emctl it shows the following error

OC4J Configuration issue...

Now, even if I rename the file OC4J_DBConsole ( as mentioned in some of the forums) , in any of your Oracle home$oc4j/j2ee/OC4J_DBConsole_<MachineName>_<InstanceName> , it will not work.

The correct solution is to set the correct ORACLE_HOME , go to your ORACLE_HOME and execute ./bin/emctl status dbconsole

-bash-3.00$ pwd
/applns/oracle11g/oracle/oc4j/j2ee
-bash-3.00$ cd $ORACLE_HOME
-bash-3.00$ ls
apex         config  dc_ocm                has               j2ee    lib         nls     oracore      plsql      slax             ucp
assistants   crs     deinstall             hs                javavm  log         oc4j    oraInst.loc  precomp    sqldeveloper     uix
bin          csmig   demo                  ide               jdbc    md          odbc    ord          racg       sqlj             utl
ccr          css     diagnostics           install           jdev    mesg        olap    oui          rdbms      sqlplus          wwg
cdata        ctx     dv                    install.platform  jdk     mgw         ons     owb          relnotes   srvm             xdk
cfgtoollogs  cv      emcli                 instantclient     jlib    N850_CBA04  OPatch  owm          root.sh    sysman
clone        dbs     EMStagePatches_CBA04  inventory         ldap    network     opmn    perl         scheduler  timingframework
-bash-3.00$ ./bin/emctl status dbconsole
Oracle Enterprise Manager 11g Database Control Release 11.2.0.1.0
Copyright (c) 1996, 2009 Oracle Corporation.  All rights reserved.
https://N850:1158/em/console/aboutApplication
Oracle Enterprise Manager 11g is running.
------------------------------------------------------------------
Logs are generated in directory /applns/oracle11g/oracle/N850_CBA04/sysman/log

 
 

Oracle Database Software Release Numbers Meaning

Here is what Each Oracle Database Software Release Number means.

 
 

What is an Oracle INDEXTYPE

An INDEXTYPE is  an object that specifies the routines that manage a domain (application-specific) index . Use the CREATE INDEXTYPE statement to create an indextype, which is. Indextypes reside in the same namespace as tables, views, and other schema objects. This statement binds the indextype name to an implementation type, which in turn specifies and refers to user-defined index functions and procedures that implement the indextype. Create any INDEXTYPE is a privilege in RESOURCE role.

 
 

Oracle SQL Parsing Steps Involved

Oracle SQL is parsed before execution, and a hard parse includes these steps:

1.        Loading into shared pool - The SQL source code is loaded into RAM for parsing. (the "hard" parse step)

2.        Syntax parse - Oracle parses the syntax to check for misspelled SQL keywords.

3.        Semantic parse - Oracle verifies all table & column names from the dictionary and checks to see if you are authorized to see the data.

4.        Query Transformation - If enabled (query_rewrite=true), Oracle will transform complex SQL into simpler, equivalent forms and replace aggregations with materialized views, as appropriate.

5.        Optimization - Oracle then creates an execution plan, based on your schema statistics (or maybe with statistics from dynamic sampling in 10g).

6.        Create executable - Oracle builds an executable file with native file calls to service the SQL query.

 
 

Disk Space used by a Table in Oracle

 How to find the Disk Space used by a table in Oracle, Here is the query

SELECT   segment_name table_name,
         SUM(BYTES) /(1024 * 1024) table_size_meg
FROM     user_extents
WHERE    segment_type = 'TABLE' AND
         segment_name = 'MY_TABLE'
GROUP BY segment_name

 
 

Error while Installing Oracle 11G on 64Bit Linux - You do not have sufficient permissions to access the inventory

/data2/Oracle11G_setup/database

-bash-3.00$ ./runInstaller

You do not have sufficient permissions to access the inventory '/applns/oracle9204/oraInventory'. Installation cannot continue. Make sure that you have read/write permissions to the inventory directory and restart the installer.: Permission denied

If you get this error, rename  /etc/oraInst.loc  to oraInst.loc.bak

 
 

Unix Command to Display Directories as Tree

Here is the command you can use to Display  Unix Directories like Tree structure, exactly like you use Tree command in windows which will give a similar out put like

N20:/data1/maxim/user01/mytest $ ls -R | grep ":$" | sed -e 's/:$//' -e 's/[^-][^\/]*\//--/g' -e 's/^/   /' -e 's/-/|/'
   .
   |-test1
   |-test2
   |-test3
   |-index
 
 
 

Unable to open DISPLAY-Xming

XXX$ DISPLAY=12.18.84.120:0
XXX$ set $DISPLAY
XXX$ echo $DISPLAY
12.18.84.120:0
XXX$ xclock

Error: Can't open display:

Launch XLaunch and in the third screen, select 'No Access Control' and click Finish.
 
 

How to get a quick response / Immediate Support from Oracle

I have followed these steps if we want to get a quick response from Oracle

1. Pick up the phone and open the SR via phone.
2. Request for duty manager, to contact you immediately on phone or on email.
3. Request the duty manager to assign the subject matter expert directly, without going through the Mandatory QA with some help desk person who is absolutely ignorant about real Oracle features.
4. Keep your .trc file, .log file, and rda report ready and upload(ftp)  directly to Oracle server from your Unix server, not from your PC/laptop/client. (ftp link will be provided by the Oracle  support person)
5. Doing this way you won't even have to login to metalink and use it's Service request system.

 S S Nair

Oracle DBA/ Developer

http://ora-ssn.blogspot.com

 
 

How to start and stop Oracle Enterprise Manager DB Console in Unix

Login to target Server.

echo $ORACLE_SID
export ORACLE_SID=MYDB
emctl start dbconsole
TZ set to Asia/Calcutta
Oracle Enterprise Manager 10g Database Control Release 10.2.0.1.0
Copyright (c) 1996, 2005 Oracle Corporation.  All rights reserved.
http://N850:1158/em/console/aboutApplication
Oracle Enterprise Manager 10g is running.

You can use emctl status dbconsole to know the console status and
emctl stop  dbconsole to stop the dbconsole service.

S S Nair

Oracle DBA/ Developer

http://ora-ssn.blogspot.com

 

How to Determine Oracle Session Information given UNIX PID ?

This will become useful when you notice that a db is consuming more time for response, all SQL and PL/SQL statements are running slow.
Now if you are in a Unix / Linux machine, you will issue the top command on the Unix.


Here you can see that CPU % used by 12231 is 90.9 % which might have caused my other server process to wait. If you want to determine what process or which Instance( if you have multiple instances in same machine) corresponds to this PID-12231, Issue the following query


Track or Tune the SQL issues by that session.

S S Nair

Oracle DBA/ Developer

http://ora-ssn.blogspot.com