Accessing XML data from SQL

Oracle has powerful utilities for developeers. The one that i like is utility function for playing XML data. Utility functions allows you to access XML file as if the XML data is inside a database table. Suppose that you have a company XML file and you have to access the XML data without uploading/importing/loading XML file into database. XMLTABLE is just stands for this reason. It has a special syntax-XQuery- for accessing XML data and its attributes. To understand better, please follow the demonstration below. XML file(company.xml) is resides server side file system(/home/oracle/Documents/). XMLTYPE, BFILE and XMLTABLE will be used:


Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 
Connected as mennan

SQL> 
SQL> 
SQL> 
SQL> 
SQL> CREATE OR REPLACE DIRECTORY COMPANY_REPORT_DIR AS '/home/oracle/Documents/';

Directory created
SQL> CREATE TABLE COMPANY_LIST
  2  (
  3    ID                VARCHAR2(4),
  4    COMPANY_TITLE     VARCHAR2(32)
  5  );

Table created
SQL> SELECT XMLTYPE(BFILENAME('COMPANY_REPORT_DIR', 'company.xml'), nls_charset_id('UTF8')).GetStringVal() AS XML_DATA FROM DUAL;

XML_DATA
--------------------------------------------------------------------------------
<?xml version="1.0" encoding="ISO-8859-9"?>
<CompanyList>
  <Company>
    <Identifier>0001</Identifier>
    <Title>Oracle</Title>
  </Company>
  <Company>
    <Identifier>0002</Identifier>
    <Title>Microsoft</Title>
  </Company>
  <Company>
    <Identifier>0003</Identifier>
    <Title>Apple</Title>
  </Company>
  <Company>
    <Identifier>0004</Identifier>
    <Title>Google</Title>
  </Company>
</CompanyList>

SQL> INSERT INTO COMPANY_LIST ( ID, COMPANY_TITLE )
  2    SELECT x.ID, x.COMPANY_TITLE
  3      FROM (SELECT XMLTYPE(BFILENAME('COMPANY_REPORT_DIR', 'company.xml'), nls_charset_id('UTF8')) AS XML_DATA FROM DUAL) e,
  4           XMLTABLE('for $i in /CompanyList
  5                     return $i/Company'
  6                     PASSING XML_DATA
  7                     COLUMNS ID            VARCHAR2(4)   PATH 'Identifier',
  8                             COMPANY_TITLE VARCHAR2(32)  PATH 'Title'
  9                     ) x;

4 rows inserted
SQL> COMMIT;

Commit complete
SQL> SELECT * FROM COMPANY_LIST;

ID   COMPANY_TITLE
---- --------------------------------
0001 Oracle
0002 Microsoft
0003 Apple
0004 Google
SQL> DROP DIRECTORY COMPANY_REPORT_DIR;

Directory dropped
SQL> DROP TABLE COMPANY_LIST;

Table dropped

SQL> 




For more information please read the documentation : http://download.oracle.com/docs/cd/E11882_01/appdev.112/e23094/xdb_xquery.htm#ADXDB5121

unx:Exception during creation of the process

I had created some automated test scripts for one of my projects. For utility functions, i had created a simple shell executor which is a java source and compiled in the oracle database in order to help me to execute unix commands from pl/sql.  After i did some tests, i realized that the shell executor results an error as shown below:

Connected to Oracle Database 11g Enterprise Edition Release 11.1.0.6.0

Connected as mennan

SQL> set serveroutput on;

SQL>

SQL> DECLARE

  2    CommandText  varchar2(4000);

  3  BEGIN

  4    CommandText := ShellExecutor(pis_command =>'/bin/ls -1tr');

  5    dbms_output.put_line( 'Return : ' ||  CommandText);

  6  END;

  7  /

Return : RET:unx:Exception during creation of the process

PL/SQL procedure successfully completed

 When i grant “java_deploy” role to the my user, the problem was solved:

SQL> connect sys/sysadm@testdb.vm as sysdba

Connected to Oracle Database 11g Enterprise Edition Release 11.1.0.6.0

Connected as SYS

SQL> grant java_deploy to mennan;

Grant succeeded

SQL> connect mennan/sysadm@testdb.vm

Connected to Oracle Database 11g Enterprise Edition Release 11.1.0.6.0

Connected as mennan

SQL> set serveroutput on;

SQL>

SQL> DECLARE

  2    CommandText  varchar2(4000);

  3  BEGIN

  4    CommandText := ShellExecutor(pis_command =>'/bin/ls -1tr');

  5    dbms_output.put_line( 'Return : ' ||  CommandText);

  6  END;

  7  /

Return : RET:unx:init.ora

initdw.ora

lktestdb

hc_testdb.dat

orapwtestdb

spfiletestdb.ora

The source files of ShellExecutor is shown below:

Java source to be compiled in the database:

create or replace and compile java source named Shell as

import java.io.*;

public class Shell {

 

  public static String executeCommand(String _command) {

    String commandOutput, temp, inputStreamOutput = "", errorStreamOutput = "";

    try {

   

      String[] shellCommandArray;

      if (System.getProperty("os.name").toLowerCase().indexOf("windows") != -1) {

        shellCommandArray = new String[4];

        shellCommandArray[0] = "C:\\windows\\system32\\cmd.exe";

        shellCommandArray[1] = "/y";

        shellCommandArray[2] = "/c";

        shellCommandArray[3] = _command;

      }

      else {

        shellCommandArray = new String[3];

        shellCommandArray[0] = "/bin/sh";

        shellCommandArray[1] = "-c";

        shellCommandArray[2] = _command;

      }

      Process runtimeProcess = Runtime.getRuntime().exec(shellCommandArray);

      BufferedReader readerInputStream = new BufferedReader(new InputStreamReader(runtimeProcess.getInputStream()));

      temp = null;

      while ((temp = readerInputStream.readLine()) != null) {

      inputStreamOutput+=temp+ System.getProperty("line.separator") ;

      }

      readerInputStream.close();

     

      BufferedReader readerErrorStream = new BufferedReader(new InputStreamReader(runtimeProcess.getErrorStream()));

      temp = null;

      while ((temp = readerErrorStream.readLine()) != null) {

      errorStreamOutput+=temp + System.getProperty("line.separator") ;

      }

      readerErrorStream.close();

    }

    catch (Exception ex) {

    errorStreamOutput=ex.getMessage();

    }

   

    if ( errorStreamOutput.length() > 0  ){

    commandOutput = "E:"+errorStreamOutput;

    }else{

    commandOutput = "S:"+inputStreamOutput;

    }   

   

    return commandOutput;

   

     }

}

PL/,SQL Wrapper function:

CREATE OR REPLACE FUNCTION ShellExecutor(pis_CommandText IN VARCHAR2 )

  RETURN VARCHAR2 AS

LANGUAGE JAVA NAME 'Shell.executeCommand( java.lang.String ) return java.lang.String';

/

Sample test script:

set serveroutput on;

DECLARE

  vs_CommandText VARCHAR2(4000);

  vs_ReturnText  VARCHAR2(4000);

  vs_SuccessFail VARCHAR2(2);

BEGIN

  --To list directories

  --vs_CommandText := '/bin/ls -1tr';

 

  --To remove files

  vs_CommandText := '/bin/rm  /home/oracle/temp/dummy.txt';

 

  vs_ReturnText  := ShellExecutor(vs_CommandText);

  vs_SuccessFail := substr(vs_ReturnText, 1, 2);

  vs_ReturnText  := substr(vs_ReturnText, 3);

  IF vs_SuccessFail <> 'S:' THEN

    dbms_output.put_line('Failed!!!');

  END IF;

  dbms_output.put_line(vs_ReturnText);

END;

/

Parallelization of Scheduler Jobs with Advanced Queueing

One of the useful utilities of Oracle database is Scheduler.With Scheduler, it is possible to use an Oracle database as a complex,configurable job scheduling mechanism, like cron of unix. I will not go deepinside of the capabilities of Oracle’s Scheduler.(you can search previousposts) The main reason that made me to write this post is, making the sameScheduler Job to be run at the same time, as multiple instances. That is, it isnot possible to run the same job simultaneously. You can run the Job, after the running-onefinishes. This is not something bad, asit sounds. On the contrary, it is a must in order to keep atomicity of programunits, jobs. Remember usage of semaphores. For the shared resources, a controlmechanism should exist in order prevent common resources to be usedsimultaneously at the same time. This is main concept of parallel computing.

Suppose that, running the same job more than once at thesame time is not *something* bad for your application design. Again, supposethat your jobs has parameters and you are controlling atomicity of the job byyourself. So, you want to get rid of serial execution restrictions ofScheduler. But how?

I like finding workarounds for the problems. There arealways some problems in our lives. In order to overcome the problems that arenot created by you, you should find an exit-gate. Finding an exit-gate is notas easy as in the movies. …

Let’s go…

To make a Scheduler job to be run simulataneously, youshould take advantages of Advanced Queueing(AQ). Again, this is anotherpowerful utilities of Oracle for developers. You are not forced to createtables, programs, algorithms for a simple queue. With Oracle’s AQ, you have atoolkit for queue generation. Those interfaces that exists in the toolkit, willhelp you to create complex queues.(for more info, please check Oracle’s doc.)

I simulated serial execution(only Scheduler) and parallelexecution(Sheduler + AQ) below. I think analyzing the code pieces below, willmake you to understand better.

Connected to Oracle Database 11g Enterprise EditionRelease 11.2.0.1.0

Connected as mennan

SQL> --serial execution: scheduler program-job

SQL> CREATE TABLE LOG_TABLE

2 (

3 ID NUMBER,

4 CALLER VARCHAR2(32),

5 CALL_TIMESTAMP TIMESTAMP DEFAULT SYSTIMESTAMP

6 );

Table created

SQL> CREATE OR REPLACE PROCEDUREInsertLogTable(pin_Id IN LOG_TABLE.ID%TYPE) AS

2 BEGIN

3 INSERT INTO LOG_TABLE (ID,CALLER) VALUES(pin_Id, 'SERIAL_EXECUTION_PRG');

4 COMMIT;

5 DBMS_LOCK.sleep(3);

6 END;

7 /

Procedure created

SQL> BEGIN

2 DBMS_SCHEDULER.CREATE_PROGRAM(program_name => 'SERIAL_EXECUTION_PRG',

3 program_type =>'STORED_PROCEDURE',

4 program_action =>'InsertLogTable',

5 number_of_arguments => 1,

6 enabled => FALSE,

7 comments => 'The program that simulatesserial execution of dbms_scheduler.');

8 END;

9 /

PL/SQL procedure successfully completed

SQL> BEGIN

2 DBMS_SCHEDULER.DEFINE_PROGRAM_ARGUMENT(program_name => 'SERIAL_EXECUTION_PRG',

3 argument_position => 1,

4 argument_name => 'pin_Id',

5 argument_type => 'NUMBER',

6 default_value => NULL);

7 END;

8 /

PL/SQL procedure successfully completed

SQL> BEGIN

2 DBMS_SCHEDULER.CREATE_JOB(job_name => 'SERIAL_EXECUTION_JOB',

3 program_name=> 'SERIAL_EXECUTION_PRG',

4 enabled => FALSE,

5 auto_drop => FALSE,

6 comments => 'The job that simulates serialexecution of dbms_scheduler by inserting log table.');

7 END;

8 /

PL/SQL procedure successfully completed

SQL> BEGIN

2 DBMS_SCHEDULER.enable(NAME =>'SERIAL_EXECUTION_PRG');

3 END;

4 /

PL/SQL procedure successfully completed

SQL> BEGIN

2 DBMS_SCHEDULER.enable(NAME =>'SERIAL_EXECUTION_JOB');

3 END;

4 /

PL/SQL procedure successfully completed

SQL> pause;

SQL> ----TEST:run 1

SQL> BEGIN

2 DBMS_SCHEDULER.set_job_argument_value(job_name => 'SERIAL_EXECUTION_JOB',

3 argument_position => 1,

4 argument_value => 1);

5 DBMS_SCHEDULER.run_job(job_name => 'SERIAL_EXECUTION_JOB',

6 use_current_session=> FALSE);

7 END;

8 /

PL/SQL procedure successfully completed

SQL> --run 2

SQL> BEGIN

2 DBMS_SCHEDULER.set_job_argument_value(job_name => 'SERIAL_EXECUTION_JOB',

3 argument_position => 1,

4 argument_value => 2);

5 DBMS_SCHEDULER.run_job(job_name => 'SERIAL_EXECUTION_JOB',

6 use_current_session =>FALSE);

7 END;

8 /

BEGIN

DBMS_SCHEDULER.set_job_argument_value(job_name => 'SERIAL_EXECUTION_JOB',

argument_position => 1,

argument_value => 2);

DBMS_SCHEDULER.run_job(job_name => 'SERIAL_EXECUTION_JOB',

use_current_session=> FALSE);

END;

ORA-27478:"MENNAN.SERIAL_EXECUTION_JOB" işi çalışıyor

ORA-06512: konum"SYS.DBMS_ISCHED", satır 185

ORA-06512: konum"SYS.DBMS_SCHEDULER", satır486

ORA-06512: konum satır 6

SQL> select * from log_table where id is notnull;

ID CALLER CALL_TIMESTAMP

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

1SERIAL_EXECUTION_PRG 26/09/2011 20:26:30,689920

SQL>

---rollback

DROP TABLE LOG_TABLE;

DROP PROCEDURE InsertLogTable;

BEGIN

DBMS_SCHEDULER.drop_job(job_name => 'SERIAL_EXECUTION_JOB');

END;

/

BEGIN

DBMS_SCHEDULER.drop_program(program_name => 'SERIAL_EXECUTION_PRG');

END;

/

SQL> --parallel execution: scheduler program-job + advanced queue

SQL> CREATE TABLE LOG_TABLE

2 (

3 ID NUMBER,

4 CALLER VARCHAR2(32),

5 CALL_TIMESTAMP TIMESTAMP DEFAULT SYSTIMESTAMP

6 );

Table created

SQL> CREATE OR REPLACE TYPE LOG_TYPE IS OBJECT

2 (

3 ID NUMBER

4 );

5 /

Type created

SQL> BEGIN

2 DBMS_AQADM.create_queue_table(queue_table => 'AQ_LOG_QUEUE_TABLE',

3 queue_payload_type => 'LOG_TYPE',

4 multiple_consumers => TRUE);

5 END;

6 /

PL/SQL procedure successfully completed

SQL> BEGIN

2 DBMS_AQADM.create_queue(queue_name => 'AQ_LOG_QUEUE',

3 queue_table =>'AQ_LOG_QUEUE_TABLE');

4 END;

5 /

PL/SQL procedure successfully completed

SQL> BEGIN

2 DBMS_AQADM.start_queue(queue_name =>'AQ_LOG_QUEUE',

3 enqueue => TRUE,

4 dequeue => TRUE);

5 END;

6 /

PL/SQL procedure successfully completed

SQL> --select * from all_queue_tables wherequeue_table = 'AQ_TEST_QUEUE_TABLE';

SQL> --select * from all_queues where NAME ='AQ_TEST_QUEUE';

SQL> CREATE OR REPLACE PROCEDUREInsertLogTable2(pit_LogType IN LOG_TYPE) AS

2 BEGIN

3 INSERT INTO LOG_TABLE (ID,CALLER) VALUES(pit_LogType.ID, 'PARALLEL_EXECUTION_PRG');

4 COMMIT;

5 DBMS_LOCK.sleep(3);

6 END;

7 /

Procedure created

SQL> BEGIN

2 DBMS_SCHEDULER.create_program(program_name => 'PARALLEL_EXECUTION_PRG',

3 program_type =>'STORED_PROCEDURE',

4 program_action =>'InsertLogTable2',

5 number_of_arguments => 1);

6 DBMS_SCHEDULER.define_metadata_argument(program_name => 'PARALLEL_EXECUTION_PRG',

7 argument_position => 1,

8 metadata_attribute=> 'EVENT_MESSAGE');

9 DBMS_SCHEDULER.enable('PARALLEL_EXECUTION_PRG');

10 END;

11 /

PL/SQL procedure successfully completed

SQL> BEGIN

2 DBMS_SCHEDULER.create_job(job_name => 'PARALLEL_EXECUTION_JOB',

3 program_name => 'PARALLEL_EXECUTION_PRG',

4 event_condition=> 'TAB.USER_DATA.ID IS NOT NULL',

5 queue_spec => 'AQ_LOG_QUEUE',

6 auto_drop => FALSE);

7 DBMS_SCHEDULER.enable('PARALLEL_EXECUTION_JOB');

8 END;

9 /

PL/SQL procedure successfully completed

SQL> BEGIN

2 DBMS_SCHEDULER.set_attribute(NAME => 'PARALLEL_EXECUTION_JOB',

3 ATTRIBUTE=> 'parallel_instances',

4 VALUE => TRUE);

5 END;

6 /

PL/SQL procedure successfully completed

SQL> --select * from all_scheduler_job_log where job_name = 'PARALLEL_EXECUTION_JOB'order by 1 desc;

SQL> --select * fromall_scheduler_job_run_details wherejob_name = 'PARALLEL_EXECUTION_JOB' order by 1 desc;

SQL> -- TEST : run 1

SQL> DECLARE

2 vt_EnqueueOptions DBMS_AQ.ENQUEUE_OPTIONS_T;

3 vt_MessagePropertiesDBMS_AQ.MESSAGE_PROPERTIES_T;

4 vt_RequestObject LOG_TYPE;

5 vr_MessageId RAW(16);

6 BEGIN

7 vt_RequestObject := LOG_TYPE(123);

8

9 DBMS_AQ.enqueue(queue_name => 'AQ_LOG_QUEUE',

10 enqueue_options => vt_EnqueueOptions,

11 message_properties =>vt_MessageProperties,

12 payload => vt_RequestObject,

13 msgid => vr_MessageId);

14 dbms_output.put_line('vr_MessageId : ' ||vr_MessageId);

15 COMMIT;

16 END;

17 /

PL/SQL procedure successfully completed

SQL> -- run 2

SQL> DECLARE

2 vt_EnqueueOptions DBMS_AQ.ENQUEUE_OPTIONS_T;

3 vt_MessagePropertiesDBMS_AQ.MESSAGE_PROPERTIES_T;

4 vt_RequestObject LOG_TYPE;

5 vr_MessageId RAW(16);

6 BEGIN

7 vt_RequestObject := LOG_TYPE(124);

8

9 DBMS_AQ.enqueue(queue_name => 'AQ_LOG_QUEUE',

10 enqueue_options => vt_EnqueueOptions,

11 message_properties =>vt_MessageProperties,

12 payload => vt_RequestObject,

13 msgid => vr_MessageId);

14 dbms_output.put_line('vr_MessageId : ' ||vr_MessageId);

15 COMMIT;

16 END;

17 /

PL/SQL procedure successfully completed

SQL> select * from LOG_TABLE;

IDCALLER CALL_TIMESTAMP

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

123PARALLEL_EXECUTION_PRG 26/09/2011 20:42:34,078989

124PARALLEL_EXECUTION_PRG 26/09/2011 20:42:34,080255

SQL>

SQL>

--rollback

BEGIN

dbms_scheduler.drop_job('PARALLEL_EXECUTION_JOB');

dbms_scheduler.drop_program('PARALLEL_EXECUTION_PRG');

dbms_aqadm.stop_queue('AQ_LOG_QUEUE');

dbms_aqadm.drop_queue('AQ_LOG_QUEUE');

dbms_aqadm.drop_queue_table('AQ_LOG_QUEUE_TABLE');

END;

/

DROP TABLE LOG_TABLE;

DROP TYPE LOG_TYPE;

DROP PROCEDURE InsertLogTable2;

ORA-27300: OS system dependent operation:semget failed with status: 28

ORA-27154: post/wait create failed

ORA-27300: OS system dependent operation:semget failed withstatus: 28

ORA-27301: OS failure message: No space left on device

ORA-27302: failure occurred at: sskgpsemsper

Although theerror says “No space left on device”, but it is not true. The problem is duekernel semaphore configuration. After setting a proper value(echo"250 1500 100 128" > /proc/sys/kernel/sem), the problemwill be solved:

[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on MonJun 21 08:20:51 2010

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

Connected to an idle instance.

SQL> startup

ORA-27154: post/wait create failed

ORA-27300: OS system dependentoperation:semget failed with status: 28

ORA-27301: OS failure message: No spaceleft on device

ORA-27302: failure occurred at:sskgpsemsper

SQL> exit

Disconnected

[oracle@localhost ~]$ df -kh

Filesystem Size Used Avail Use% Mounted on

/dev/mapper/VolGroup00-LogVol00

304G 173G 115G 61% /

/dev/hda1 99M 12M 82M 13% /boot

tmpfs 1.3G 0 1.3G 0% /dev/shm

[root@localhost kernel]# cat /proc/sys/kernel/sem

250 100 32 128

[root@localhost kernel]# echo "250 1500 100 128" > /proc/sys/kernel/sem

[root@localhost kernel]# cat /proc/sys/kernel/sem

250 500 100 128

[root@localhost kernel]#

ORA-01078: failure in processing system parameters

One of my collegues has asked me to help him to start thedatabase that has been created before. When i check, i saw that the databaseserver parameter files –somehow- has been deleted from its own place($ORACLE_HOME/dbs).Then, i opened the database from the init.ora which is created during databasecreation. Finally, i created spfile form this init.ora(pfile).

[oracle@localhost dbs]$/app/oracle/product/11.2.0/dbhome_1/bin/sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Mon Jun21 08:58:08 2010

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

Enter user-name: sys as sysdba

Enter password:

Connected to an idle instance.

SQL> startup

ORA-01078: failure in processing systemparameters

LRM-00109: could not open parameter file'/app/oracle/product/11.2.0/dbhome_1/dbs/initTESTDB.ora'

SQL> exit

Disconnected

[oracle@localhost dbs]$

[oracle@localhost pfile]$/app/oracle/product/11.2.0/dbhome_1/bin/sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Mon Jun21 09:08:29 2010

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

Enter user-name: sys as sysdba

Enter password:

Connected to an idle instance.

SQL> startup nomountpfile=/app/oracle/admin/TESTDB/pfile/init.ora

ORACLE instance started.

Total System Global Area 313860096 bytes

Fixed Size 1336232 bytes

Variable Size 209718360 bytes

Database Buffers 96468992 bytes

Redo Buffers 6336512 bytes

SQL> create spfile from pfile

2 ;

create spfile from pfile

*

ERROR at line 1:

ORA-01078: failure in processing systemparameters

LRM-00109: could not open parameter file

'/app/oracle/product/11.2.0/dbhome_1/dbs/initTESTDB.ora'

SQL> create spfile frompfile=/app/oracle/admin/TESTDB/pfile/init.ora;

create spfile frompfile=/app/oracle/admin/TESTDB/pfile/init.ora

*

ERROR at line 1:

ORA-02236: invalid file name

SQL> create spfile frompfile='/app/oracle/admin/TESTDB/pfile/init.ora';

File created.

SQL> create pfile from spfile;

File created.

SQL> exit

Disconnected from Oracle Database 11g EnterpriseEdition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and RealApplication Testing options

[oracle@localhost pfile]$ pwd

/app/oracle/admin/TESTDB/pfile

[oracle@localhost pfile]$ ls

init.ora

[oracle@localhost pfile]$ ls -ltr $ORACLE_HOME/dbs

total 72

-rw-r--r-- 1 oracle dba 2851 May 15 2009 init.ora

drwx------ 2 oracle dba 4096 Jun 20 14:19peshm_DBUA0_0

-rw-rw---- 1 oracle dba 1544 Jun 20 14:19hc_DBUA0.dat

drwx------ 2 oracle dba 4096 Jun 20 22:52peshm_TESTDB_0

-rw-r----- 1 oracle dba 1536 Jun 20 22:52orapwTESTDB

-rw-rw---- 1 oracle dba 1544 Jun 20 22:52hc_TESTDB.dat

-rw-r----- 1 oracle dba 24 Jun 20 22:52 lkTESTDB

-rw-r----- 1 oracle dba 1536 Jun 21 09:14spfileTESTDB.ora

-rw-r--r-- 1 oracle dba 476 Jun 21 09:14 initTESTDB.ora

[oracle@localhost pfile]$/app/oracle/product/11.2.0/dbhome_1/bin/sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Mon Jun21 09:15:44 2010

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

Enter user-name: sys as sysdba

Enter password:

Connected to:

Oracle Database 11g Enterprise Edition Release11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and RealApplication Testing options

SQL> select name from v$database;

select name from v$database

*

ERROR at line 1:

ORA-01507: database not mounted

SQL> startup

ORA-01081: cannot start already-running ORACLE -shut it down first

SQL> shutdown immediate;

ORA-01507: database not mounted

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 313860096 bytes

Fixed Size 1336232 bytes

Variable Size 209718360 bytes

Database Buffers 96468992 bytes

Redo Buffers 6336512 bytes

Database mounted.

Database opened.

SQL> select name from v$database;

NAME

---------

TESTDB

SQL>