Expert Oracle

Oracle SQL Developer Data Modeling - Early Adopter Release

2008-10-15T08:11:00.000-07:00

SQL Developer Data Modeling offers a full spectrum of data and database modeling tools and utilities, including Entity Relationship modeling, Relational (Database Design), Data Type and Multidimensional modeling, full forward and reverse engineering and code generation. It includes importing from and exporting to a variety of sources and targets, provides a variety of formatting options and validates the models through a predefined set of Design Rules.

SQL Developer Data Modeling can connect to any Oracle Database version 9.2.0.1 and later, and is platform independent. Initially available as a standalone product, with future releases available as an extension to Oracle SQL Developer. The first Early Adopter release is stand alone and file-based only. For download and additional information, please click here

An easier way to write Mediawiki pages

2008-10-09T04:01:00.000-07:00

One of my colleague has circulated email for MediaWiki. Following is extract from his email :

For any of you that struggle with the wiki syntax, as I do, you might find it easier to use OpenOffice, a free alternative to Microsoft Office. Writer (their version of Word) will export in MediaWiki format. So you can write your document in OpenOffice, then export to MediaWiki. As you can also open Word documents in Writer then export them. This is quite handy if you want to create a document that you can send out, but also share on the wiki.

You can download it here: http://www.openoffice.org/

A couple of limitations I've found are:

1. Image support is, well, non existant really. If you have an image in your document, it'll put a placeholder in the wiki text, but no more. But it's a start I guess.

2. Tables are all set with a default class="prettytable". None of the border or other such formatting settings are created. However, the data is in the right places (which is always the bit that gives me a headache) so adding border="1" at the top etc isn't too traumatic once the table is there.

3. Certain text sections get exported wrapped in tags. The best example is that if you have a snippet of XML in a document, it'll wrap that in so that the XML tags aren't interpreted. This makes using the wiki code syntax highlighter* a little harder, but it's not too hard to strip out the and then wrap with a tag instead.

SQL Deployment

2008-01-15T06:10:00.000-08:00

Example of Directory Structure & SQL Scripts

Set up a directory structure, e.g.

Note : The "sql" folder *MUST* contain at least following scripts :


 1.  declare.sql
 2.  rollout.sql
 3.  rollout_all.sql
 4.  recompile_invalid_objects.sql

Note : The "sql" folder *MUST* contain atleast one sub-folder with schema name:


 eg.  SCOTT

SQL Scripts

sql/declare.sql

This script is used to declare database connect string and substitute passwords


 REM Defining Connection Details
 DEFINE connect_string = orcl10g.world
 DEFINE SCOTT_password = tiger
 DEFINE HR_password = hr

sql/rollout.sql

* 'rollout.sql' runs declare.sql which DEFINE's substitution variables for all passwords (and for the db connect string). It then connects to each database user in turn and runs the rollout scripts for each schema.

* This script is used to install SQL for particular release (Following example is for Release 2)


 @../../2007.12.2.0/sql/declare.sql
 spool ../../2007.12.2.0/sql/rollout.spl
 Prompt Deploying scripts for Release 2
 connect SCOTT/&SCOTT_password@&connect_string
 sho user
 PROMPT Connected as SCOTT on &connect_string
 @../../2007.12.2.0/sql/SCOTT/rollout.sql
 @../../2007.12.2.0/sql/recompile_invalid_objects.sql
 connect HR/&HR_password@&connect_string
 sho user
 PROMPT Connected as HR on &connect_string
 @../../2007.12.2.0/sql/HR/rollout.sql
 @../../2007.12.2.0/sql/recompile_invalid_objects.sql
 PROMPT Scripts deployed for Release 2

sql/rollout_all.sql

* The @../../ in the paths works fine on both windows and unix. We have used @../../script.sql so that we can successfully rollout all the releases by running single 'rollout_all.sql' script.

* This script is used to install everything *upto* particular release.

Example of rollout_all.sql for release 2

In the following example, it will deploy SQL scripts for release 1 and 2.


 @../../2007.12.1.0/sql/rollout.sql
 PROMPT Press any key to deploy release 2 scripts or Ctrl+C to abort
 PAUSE
 @../../2007.12.2.0/sql/rollout.sql

Example of rollout_all.sql for release 3

In the following example, it will deploy SQL scripts for release 1,2 and 3.


 @../../2007.12.1.0/sql/rollout.sql
 PROMPT Press any key to deploy release 2 scripts or Ctrl+C to abort
 PAUSE
 @../../2007.12.2.0/sql/rollout.sql
 PROMPT Press any key to deploy release 3 scripts or Ctrl+C to abort
 PAUSE
 @../../2007.12.3.0/sql/rollout.sql

Note : Please remember to change 'declare.sql' before running above script.

sql/recompile_invalid_objects.sql

This script is used to do the following
* To list invalid objects
* Try to recompile all invalid objects
* To list invalid objects which were not compiled successfully
* To list Objects deployed / re-compiled today
* To list compilation errors (if any) after deployment


prompt +--------------------------------------------+
prompt | List of invalid objects before compilation |   
prompt +--------------------------------------------+
column object_type format a30
column object_name format a30
column invalid_obj_cnt format 999
column error_text format a30 word_wrap
set serveroutput on
set pagesize 80
select object_type
     , count(*) invalid_obj_cnt 
from   user_objects
where  status <> 'VALID'
group by object_type
order by 1
/
prompt +--------------------------------------------+
prompt | Recompiling invalid objects                |
prompt +--------------------------------------------+
BEGIN
  FOR cur_rec IN (SELECT object_name,
                         object_type,
                         DECODE(object_type, 'PACKAGE', 1,
                                             'PACKAGE BODY', 2, 2) AS recompile_order
                  FROM   user_objects
                  WHERE  status != 'VALID'
                  ORDER BY 3)
LOOP
  BEGIN
    IF cur_rec.object_type = 'PACKAGE BODY' THEN
      EXECUTE IMMEDIATE 'ALTER PACKAGE "' ||  cur_rec.object_name || '" COMPILE BODY';
    ELSE
      EXECUTE IMMEDIATE 'ALTER ' || cur_rec.object_type ||' "' || cur_rec.object_name || '" COMPILE';
    END IF;
  EXCEPTION
    WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE(cur_rec.object_type || ' : ' || cur_rec.object_name ||' is INVALID');
  END;
END LOOP;
END;
/
prompt +--------------------------------------------+
prompt | List of invalid objects after compilation  |
prompt +--------------------------------------------+
select object_type
     , count(*) invalid_obj_cnt 
from   user_objects
where  status <> 'VALID'
group by object_type
order by 1
/
prompt +---------------------------------------------+
prompt | List of objects deployed / recompiled today |
prompt +---------------------------------------------+
select object_type 
     , object_name 
from   user_objects 
where  subobject_name is null
and    (created > trunc(sysdate) or last_ddl_time > trunc(sysdate))
order by object_type, object_name
/ 
prompt +---------------------------------------------+
prompt | List of Errors                              | 
prompt +---------------------------------------------+
select name 
     , type 
     , 'Line '||line||' Position '||position||' - '||text error_text
from   user_errors 
-- where  attribute = 'ERROR' /* Uncomment if deploying to Oracle Database 10g */
order by name
/

sql/HR/rollout.sql

* Use @@ so that the scripts are picked up relative to the directory of the script being run


 @@view.sql
 @@procedure.sql

sql/SCOTT/rollout.sql

* Use @@ so that the scripts are picked up relative to the directory of the script being run


@@grants.sql

Example of Deployment Instructions

Deployment Instructions for Test Environment

1. Edit declare.sql and substitute the correct database connect string and correct passwords

2. At the command prompt:


 $ cd ../2007.12.1.0/sql                    
 $ sqlplus SCOTT/ValidPassword@ValidConnectString
 sql> @rollout.sql
 sql> exit

NB. The connect string on the command line has to work but is otherwise completely ignored

3. Examine the output (spool file): no errors or warnings should occur


 $ cat rollout.spl

Deployment Instructions for Live Environment

1. Edit declare.sql and substitute the correct database connect string and correct passwords. We must modify declare.sql of every releases. In the examples given above, we should modify following declare.sql


 ../2007.12.1.0/sql/declare.sql      
 ../2007.12.2.0/sql/declare.sql      
 ../2007.12.3.0/sql/declare.sql      
 ..etc

2. At the command prompt:


 $ cd ../2007.12.3.0/sql   -- Change directory to Last release
 $ sqlplus SCOTT/ValidPassword@ValidConnectString
 sql> @rollout_all.sql            -- Script to rollout SQL scripts of all releases
 sql> exit

NB. The connect string on the command line has to work but is otherwise completely ignored

3. Examine the output (spool file): no errors or warnings should occur


 $ cat ../2007.12.1.0/sql/rollout.spl
 $ cat ../2007.12.2.0/sql/rollout.spl      
 $ cat ../2007.12.3.0/sql/rollout.spl      
 .. etc

SQL Script Guidelines
* All SQL script *MUST* start with 'prompt .....'
* SQL script for procedures, functions and packages *MUST* end with 'show error'
* Script should be saved under unix format. So, if you are using UltraEdit to create / modify your script, please use 'File' -> 'Conversions' -> 'Dos to UNIX'
* Name of the script should be lower case. The script name should match oracle object name when possible.
* Package body and package definition should be saved separately.
* Script file extension :


      Package Specification   .pks
      Package Body            .pkb
      All other scripts       .sql

To Do / Can be done
* We are creating declare.sql per release. Due to this, live deployment requires changes to all declare.sql. To avoid this, consider amending 'declare.sql' of first release for every subsequent deployment.
* Consider creating 'PATCH' table per schema. Insert a row with patch_id, timestamp and userid if the patch gets deployed successfully (i.e. without errors). This will allow us to track database changes by querying single table.
* Consider using Ant scripts for SQL deployment.

Useful Unix / Linux scripting links

2008-01-15T06:02:00.000-08:00

Guide to Linux System Command Mastery

Improve Your Scripting with the test Command

Guide to Linux Filesystem Mastery

An Introduction to Linux Shell Scripting for DBAs

Guide to Advanced Linux Command Mastery Part-1

Guide to Advanced Linux Command Mastery Part-2

AWK: The Linux Administrators' Wisdom Kit

Using the sed Editor

Download Oracle VM

2007-11-15T05:53:00.000-08:00

You can download OracleVM from http://edelivery.oracle.com/oraclevm

Load XML into Database

2007-10-16T06:34:00.000-07:00

Following is the extract from Oracle SQL & PLSQL Forum (Courtesy Billy Verreynne)

You can store XML as CLOB or XMLType objects.

You can easily load XML files into the database as CLOBs using the DBMS_LOB PL/SQL package - it has a load-from-file procedural call that can be made. Very easy to use.


drop table xml_clob; 
drop table my_objects;
-- create a table to store a large XML doc in raw format 
create table xml_clob ( doc CLOB ) 
/
-- create such a raw XML document, using the ALL_OBJECTS -- table 
DECLARE
    CURSOR curxml IS
        SELECT xmlelement("ORA_OBJECT", xmlforest(object_id AS "ID", owner AS "OWNER", object_type AS "TYPE", object_name AS "NAME")) AS "XML_DATA"
        FROM   all_objects
        WHERE  rownum < 100001;

    xml    XMLTYPE;
    c      CLOB;
BEGIN
    dbms_lob.createtemporary(c, TRUE);

    OPEN curxml;

    LOOP
        FETCH curxml
        INTO  xml;

        EXIT WHEN curxml%NOTFOUND;
  DBMS_LOB.Append( c, xml.GetCLOBVal() ); 
        -- dbms_lob.writeappend(c, LENGTH(xml.getclobval()), xml.getclobval());
    END LOOP;

    CLOSE curxml;

    INSERT INTO xml_clob
    VALUES      ('' || c || ''
                );

    COMMIT;
END;
 
/
CREATE TABLE my_objects (obj_id NUMBER,   obj_owner VARCHAR2(30),   obj_type VARCHAR2(19),   obj_name VARCHAR2(30) )
 
/
-- call proc with a raw XML clob and have it insert the contents 
-- into a relational table 
CREATE OR REPLACE PROCEDURE insertobjects(
    xmldoc    IN OUT NOCOPY    CLOB
) IS
BEGIN
    INSERT INTO my_objects
        SELECT extractvalue(VALUE(xml), 'ORA_OBJECT/ID')
             , extractvalue(VALUE(xml), 'ORA_OBJECT/OWNER')
             , extractvalue(VALUE(xml), 'ORA_OBJECT/TYPE')
             , extractvalue(VALUE(xml), 'ORA_OBJECT/NAME')
        FROM   TABLE(SELECT xmlsequence(EXTRACT(XMLTYPE(xmldoc), 'DATASET/*'))
                     FROM   dual) xml;

    COMMIT;
END;
/
-- the test (processing 10,000 XML rows): 
set timing on 
declare   c CLOB; 
BEGIN
    SELECT doc
    INTO   c
    FROM   xml_clob;

    insertobjects(c);
END;
/ 

set timing offselect count(*) from my_objects; 
select * from my_objects where rownum < 4;
--eof

Extracting from XML Datatype

2007-10-15T07:39:00.000-07:00



WITH XMLDATA AS
(SELECT xmltype(
 '<?xml version="1.0" encoding="utf-8"?>
<transaction type="IN">
  <customer id="Cust001"/>
  <product>
    <product_details>
      <orderno>ORD000001</orderno>
      <orderlineno>000010</orderlineno>
      <product_code>PRD</product_code>
      <product_content>
        <URL/>
        <product_one>
          <textType>1</textType>
          <text/>
          <text1>Surrounding Areas</text1>
          <iconCreditCards boolean="false"/>
          <messagingEmailAddress>myemailAddress@email.com</messagingEmailAddress>
        </product_one>
      </product_content>
      <live_date>2007-02-21</live_date>
      <term_date>2008-03-05</term_date>
      <proposed_start_date>2007-03-07</proposed_start_date>
    </product_details>
    <classification code="00001">
      <branch bf_id="0001">
        <suppress_address boolean="false"/>
        <branch_override_details>
          <telephone_number prefix="Tel">01234 567890</telephone_number>
        </branch_override_details>
        <organisation_name>SIGNAL</organisation_name>
        <address_line1>Address Line 1, Some Road</address_line1>
        <address_line2/>
        <address_line3/>
        <locality>London</locality>
        <post_town>PostTown</post_town>
        <postcode>PO1 BO1</postcode>
        <telephone_number>02314 555000</telephone_number>
        <telephone_number_2 prefix="Mobile">07834 123123</telephone_number_2>
      </branch>
      <branch bf_id="0002">
        <suppress_address boolean="true"/>
        <branch_override_details>
          <telephone_number prefix="Tel">01234 567891</telephone_number>
        </branch_override_details>
        <organisation_name>SIGNAL</organisation_name>
        <address_line1>Adress Line 1, Street Road</address_line1>
        <address_line2/>
        <address_line3/>
        <locality>Best Locality</locality>
        <post_town>Wonderful Town</post_town>
        <postcode>PO1 CD2</postcode>
        <telephone_number>01189 118118</telephone_number>
        <telephone_number_2 prefix="Mobile">07834 123456</telephone_number_2>
      </branch>
    </classification>
  </product>
</transaction>'
') XML FROM DUAL)
SELECT 
extractValue(xml,'/transaction/customer@id') as CustomerID,
extractValue(xml,'/transaction/product/product_details/live_date') as startDate,
extractValue(xml,'/transaction/product/product_details/orderno') as orderno,
extractValue(xml,'/transaction/product/product_details/product_content/product_one/messagingEmailAddress') as Email,
extractValue(value(d),'/branch@bf_id') branch,
extractValue(value(d),'/branch/suppress_address@boolean') AddressSuppress,
extractValue(value(d),'/branch/branch_override_details/telephone_number@prefix') TelPrefix,
extractValue(value(d),'/branch/branch_override_details/telephone_number') TelNo
FROM   XMLDATA x, 
table(xmlsequence(extract(x.xml,'/transaction/product//classification/branch'))) d
/

----------  ----------  ----------  --------------------------  ------  --------------- ----------- -------------
CUSTOMERID STARTDATE ORDERNO     EMAIL                     BRANCH ADDRESSSUPPRESS TELPREFIX TELNO
----------  ----------  ----------  --------------------------  ------  --------------- ----------- -------------
Cust001     2007-02-21 ORD000001 myemailAddress@email.com 0001 false         Tel         01234 567890
Cust001     2007-02-21 ORD000001 myemailAddress@email.com 0002 true         Tel         01234 567891
----------  ----------  ----------  --------------------------  ------  --------------- ----------- -------------

PIVOT Table using XML

2007-10-15T04:39:00.000-07:00

Rows to Column

I want to get output of multiple rows into single variable seperated by comma. How can I achieve that. There is also requirement of using dynamic sql as table and column will be known at runtime only.

Here is the example :


select rtrim(dbms_xmlgen.getxmltype('select empno '','' e from EMP WHERE DEPTNO = '&p_deptno).extract('//text()'),',') x
from dual;

Enter value for p_deptno: 10

X
--------------
7782,7839,7934

Columns to Rows
I've got comma seperated values in single row, now I need the output in multiple rows. How can I get this?




EMPNO                                                                           
------
7782                                                                            
7839                                                                            
7934                                                                            

3 rows selected.

OCP Oracle Database 11g : New Features for Administrators Exam Guide

2007-08-15T06:51:00.001-07:00

Topics related to above exam follows.....

For now... here are some of the cool new features of 11g

Case Sensitive Passwords

Beginning with Oracle Database 11g Release 1, database passwords are case sensitive.
You can disable this features by setting the SEC_CASE_SENSITIVE_LOGON initialization parameter to FALSE.

Automatic Stats Collection on Tables

As you know that you can enable DBMS_STATS to automatically gather statistics for a table by specifying the MONITORING keyword in the CREATE (or ALTER) TABLE statement. But, starting with Oracle Database 11g, the MONITORING and NOMONITORING keywords have been deprecated and statistics are collected automatically. If you do specify these keywords, they are ignored.

Monitoring tracks the approximate number of INSERT, UPDATE, and DELETE operations for the table since the last time statistics were gathered. Information abouthow many rows are affected is maintained in the SGA, until periodically (about everythree hours) SMON incorporates the data into the data dictionary. This data dictionary information is made visible through the DBA_TAB_MODIFICATIONS,ALL_TAB_MODIFICATIONS, or USER_TAB_MODIFICATIONS views. The database uses these views to identify tables with stale statistics.

Readonly Tables

To place a table in read-only mode, you must have the ALTER TABLE privilege on thetable or the ALTER ANY TABLE privilege. In addition, the COMPATIBILE initializationparameter must be set to 11.1.0 or greater.

The following example places the EMP table in read-only mode:

ALTER TABLE EMP READ ONLY;

The following example returns the table to read/write mode:

ALTER TABLE EMP READ WRITE;

Invisible Indexes

An invisible index is an index that is ignored by the optimizer unless you explicitly set the OPTIMIZER_USE_INVISIBLE_INDEXES initialization parameter to TRUE at the session or system level. Making an index invisible is an alternative to making it unusable or dropping it.

Using invisible indexes, you can do the following:

Test the removal of an index before dropping it.

Altering Indexes

Use temporary index structures for certain operations or modules of an application without affecting the overall application.

To create an invisible index, use the SQL statement CREATE INDEX with theINVISIBLE clause. The following statement creates an invisible index named IND_EMP_DEPT for the DEPT column of the EMP table:

CREATE INDEX ind_emp_dept ON emp(dept)INVISIBLE;

To make an invisible index visible, issue this statement:

ALTER INDEX ind_emp_dept VISIBLE;

To make a visible index invisible, issue this statement:

ALTER INDEX ind_emp_dept INVISIBLE;

To check whether an index is visible or invisible, query the dictionary views

USER[ALL][DBA]_INDEXES

For example, to determine if above index is invisible, issue the following query:

SELECT INDEX_NAME, VISIBILITY FROM
USER_INDEXES WHERE INDEX_NAME = 'IND_EMP_DEPT';

INDEX_NAME VISIBILITY
------------- ----------
IND_EMP_DEPT VISIBLE

Partitioning Limitations

2007-08-14T14:14:00.000-07:00

I was trying to reverse engineer the schema using Visual Paradigm for UML 6.0 Professional Edition and identified that it doesn't work with partitioned tables. However, this is a restriction / bug in Visual Paradigm.
To list out few limitations of Partitioning :

In 9i, you can not create index organized table with List Partitioning (however in 10g, you can)
In 9i, you can not Fast Refresh materialized views after truncating partitions on base table. You have to have range or composite (implies range) especially in this case. But In 10g, you can i.e. range OR list (or composite)
You have to disable referential integrity between partitioned tables if you want to drop old partitions. Even if you drop child partition first, followed by its parent table partition, currently results in Oracle error ORA-02266: unique/primary keys in table referenced by enabled foreign keys.
We can not move partitions to tablespace with different block size using ALTER TABLE MOVE command

Drop partitions

2007-08-14T13:32:00.000-07:00

Back to basics...

"I want to drop the partitions however I dont want to rebuild the local indexes. I have enforced referential integrity on tables.. How can I achive this?"

1. Use deferrable FK constraints
2. disable the constraints before dropping partitions
3. Enable constraint with 'NOVALIDATE' clause after dropping partitions.

But what if I have indexes on FK constraints?
You can use 'KEEP INDEX' caluse at the time of disabling the constraints.