Put Multiple Rows in a Single Row

December 31, 2010 Leave a comment

In my working experience selecting multiple rows into one row was in “high-demand” query. So I decided to write here these queries as in Oracle also in SQL Server 2005:

First of all let’s create table and fill it with desired rows.

————————————————————Oracle—————————————————————–

–Create table

CREATE TABLE testTable(
deptno number,
ename varchar2(50)
);

–Fill table with data

insert into testTable
values(1,'Mari');

insert into testTable
values(1,'Gio');

insert into testTable
values(1,'Anna');

insert into testTable
values(2,'Sopo');

insert into testTable
values(2,'Vaso');

insert into testTable
values(2,'Temo');

–Let see the data in testTable

select *
from testTable

–Output

Query from the table(Oracle)

–But  our desired output is the following:

Multiple rows in a single row(Oracle)

–To achieve this,we should write the following query:

SELECT deptno
       ,LTRIM(SYS_CONNECT_BY_PATH(ename,','),',') as list
FROM(SELECT deptno
            ,ename
            ,ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY ename) AS seq
     FROM  testTable)
WHERE  connect_by_isleaf = 1
CONNECT BY seq = PRIOR seq +1 AND deptno = PRIOR deptno
START WITH seq = 1;

———————————————————–Sql Server 2005——————————————————-

–Create table

CREATE TABLE testTable(
deptno numeric(18) ,
ename varchar(50)
);

–Fill table with data

insert into testTable
values(1,'Mari');

insert into testTable
values(1,'Gio');

insert into testTable
values(1,'Anna');

insert into testTable
values(2,'Sopo');

insert into testTable
values(2,'Vaso');

insert into testTable
values(2,'Temo');

–Let see the data in testTable

select *
from testTable

–Output

Query from the table(SQL server)

–But  our desired output is the following:

Multiple rows in a single row(SQL server)

–To achieve this,we should write the following query:

SELECT DISTINCT deptno,
       STUFF(value_list, 1, 1, '') AS list
FROM testTable AS A
CROSS APPLY (
     SELECT ',' + ename
     FROM testTable AS B
     WHERE B.deptno =A.deptno
     FOR XML PATH('')
) AS t(value_list);

–Let’s discuss the following functions:

*STUFF-It will replace substring started by position 1 with length 1 by '',
       which means substring started by position 1 with length 1 will be removed.
Note:  We need to remove first character,because it is extra comma(output was ,Mari...)
* CROSS APPLY-this function will return corresponding rows from right table expression
              to left table expression.
* FOR XML PATH-returns a single string in xml format.

Filed under DB Tagged with , ,

Signature Pad with HTML5 Canvas

December 25, 2010 6 Comments

If you hurry and don’t want explanations click here to see the final result 🙂

Long time has passed since everything is migrating from real world to digital world. let’s create digital signature pad with HTML5 Canvas technology. It will be little area on web-page and with mouse a user will be able to sign as it is in some classical painting software.

The only markup (for example: canvas.html file) we will need looks like this:

<html>
    <head>
        <title>Signature Pad</title>
        <script type="text/javascript">
            //javascript code goes here ...
        </script>
    <head>
    <body>
       <canvas width="300" height="200" id="canvas"></canvas>
    </body>
</html>

We marked-up a simple html page, with title: “Signature Pad” and in the body of document there is a canvas element. For simplicity lets write JavaScript code inside html (it is not a good pattern, but the aim of this post is idea and realization of making signature pad in Canvas). So in the head part of our markup we have script tag.

Before we start coding lets explain general idea. We will need to listen to mouse events: when mouse is moving on canvas element and mouse button is pressed then we should draw a line from previous point to current point. Actually, event fires too fast and our drown lines looks like a point. Finally, there is natural signature curve.

Let’s formulate technical tasks and then write solutions:
Tasks:

1.

Function that draws a line on canvas from the first point to the second point.

2.

Listen to moment when mouse button is pressed, save the state and put a dot.

3.

Listen to moment when mouse button is released and save the state.

4.

Listen to mouse move event all the time and when mouse button is in pressed state draw a line from previous (x,y) to current (x,y).

Solutions:

1.

Why to draw a line and not a point? Because canvas doesn’t have this feature 🙂 So let’s talk a little about canvas. To draw on canvas firstly we must retrieve drawing context from it, in this case two dimensional context (there is no three dimensional context implemented yet). The idea of drawing with canvas is that firstly you have to create a path. Then move to some (x,y) point and then from that point do what you need (In this case draw a line) then again move to some point and do what you need until you complete your task but nothing will appear on canvas because path needs to be filled with “ink” and then you have to close path. Therefore our JavaScript function drawLine will look like this:

    drawLine = function(x1, y1, x2, y2){
        context.beginPath(); //create a path
        context.moveTo(x1, y1); //move to
        context.lineTo(x2, y2); //draw a line
        context.stroke(); // filled with "ink"
        context.closePath(); //close path
    };

But here we have one side efect “context” is not defined here. Let’s define it and not only “context” but all other staff in our script tag when document is ready:

<script type="text/javascript">
    //fires when document is loaded and DOM is ready.
    window.onload = function(){
        var context, canvas, drawLine;
        canvas = document.getElementById('canvas'); //retrieve canvas from dom by id that we have assigned
        context = canvas.getContext('2d'); //retrieve context
        //definition of function drawLine goes below.
    }
</script>

Let’s note that all our further definitions of functions and variables goes in window.onload = function(){ … } function!

2.

This is a simple task, we will assign listener on canvas’ onmousedown event.

    canvas.onmousedown = function(evt){
        mouseIsPressed = true; //save that mouse is pressed
        drawLine(evt.offsetX, evt.offsetY, evt.offsetX + 1, evt.offsetY + + 1) //draw short line that looks like a dot
    };

3.

Similarly, we will assign listener on canvas’ onmouseup event.

    canvas.onmouseup = function(evt){
        mouseIsPressed = false; //save that mouse is released
    };

4.

And finally, canvas’ onmousemove. This handler has to do the following: get current X and Y coordinates of mouse, check if mouse is pressed, if is pressed then draw a line from previous coordinates to current coordinates, and last, save current coordinates to another variables as previous coordinates.

    canvas.onmousemove = function(evt){
        x = evt.offsetX; y = evt.offsetY; //get current X and Y
        if(mouseIsPressed){
            drawLine(prevX, prevY, x, y);  //draw a line on canvas from previous to current point.
        }
        prevX = x; prevY = y; //save previous x and y in both case, weather mouse is pressed or not
    };

But there is a little problem, the canvas element is not visible on page. This is because both, canvas and page’s background color is white. So, let’s do borders to canvas element that is was seen where is it on page. For this we will need to add one line in onload function that will apply CSS style to canvas. But let’s do it in clever way. When we get evt.offsetX or evt.offsetY it returns coordinates related to canvas edges include border width. So, let’s save border width in variable and subtract that value while drawing line in function drawLine. The change will look like this:

    context.moveTo(x1 - borderWidth, y1 - borderWidth); //move to
    context.lineTo(x2 - borderWidth, y2 - borderWidth); //draw a line

and in onload function add a line:

    canvas.style.border = borderWidth + " px solid #00F000";

If me merge JavaScript functions and add variable declarations in onload function described above, finally, we will get this result :

<html>
    <head>
        <title>Signature Pad</title>
        <script type="text/javascript">
			//fires when document is loaded and DOM is ready.
			window.onload = function(){
				var context, canvas, drawLine, mouseIsPressed, x, y, prevX, prevY, borderWidth;

				canvas = document.getElementById('canvas'); //retrieve canvas from dom by id that we have assigned
				context = canvas.getContext('2d'); //retrieve context
				borderWidth = 5; //let border width will be 5 pixel
				canvas.style.border = borderWidth + " px solid #00F000"; // 5 pixel width solid green border

				drawLine = function(x1, y1, x2, y2){
					context.beginPath(); //create a path
					context.moveTo(x1 - borderWidth, y1 - borderWidth); //move to
					context.lineTo(x2 - borderWidth, y2 - borderWidth); //draw a line
					context.stroke(); // filled with "ink"
					context.closePath(); //close path
				};

				canvas.onmousedown = function(evt){
					mouseIsPressed = true; //save that mouse is pressed
                                        drawLine(evt.offsetX, evt.offsetY, evt.offsetX + 1, evt.offsetY + + 1) //draw short line that looks like a dot
				};

			        canvas.onmouseup = function(evt){
					mouseIsPressed = false; //save that mouse is released
				};

				canvas.onmousemove = function(evt){
					x = evt.offsetX; y = evt.offsetY; //get current X and Y
					if(mouseIsPressed){
						drawLine(prevX, prevY, x, y);  //draw a line on canvas from previous to current point.
					}
					prevX = x; prevY = y; //save previous x and y in both case, weather mouse is pressed or not
				};

			}
        </script>
    <head>
    <body>
       <canvas width="300" height="200" id="canvas"></canvas>
    </body>
</html>

Canvas has a method toDataURL that returns image in base64 format in “image/png” or “image/jpeg”. From base64 we can convert it into binary image. So this way we can get signature image with help of canvas.

Filed under HTML5, JavaScript, Programming Tagged with , ,

Interested Transaction Lists (ITL) Waits

December 23, 2010 Leave a comment

When I’ve generated AWR report, there I’ve noticed “Segments by ITL Waits” at this moment I‘ve not had any idea what was that. But after searching it on the internet I found very good information…Now, I want to share it with you.

First of all let’s discuss what ITLs is:

When a row is locked by a transaction, this information is placed in the block(header), where this row exists. The portion of the block header, which saves this information(transaction address and rowid) is called ITLs(Interested Transaction Lists).

ITLs contains several slots, initial number of slots are defined by the parameter INITRANS, which is indicated during the creation of the object (table, index) and can be increased till MAXTRANS.

Example:
CREATE TABLE test_tab
( COL1 NUMBER,
  COL2 VARCHAR2(50))
INITRANS 1
MAXTRANS 1

When one transaction tries to modify row, one slot will be filled by the info about this transaction(Assume that transaction is not finished, means commit or rollback is not performed). If another transaction will try to change the row within this block, this process will wait till the first transaction will not end.

Question is why?

Because for the second transaction there(in the block header) must be allocated another free slot, but free slot doesn’t exist and also slots number can’t increase because of  MAXTRANS is 1 which equals to the number of already allocated slot. At this time increasing MAXTRANS will solve the problem, to change MAXTRANS do as follows:

–Maximum 10 slots can be allocated(10 transaction can be placed)

Alter table test_tab MAXTRANS 10;

What is ITL wait?

When slot can’t be increased and free slots still not available… Following transaction will wait until any slot will not be available, as we have had previously… This wait is called ITL waits. We solved this waits by increasing MAXTRANS but there may happen another problem. That ,for example we have initially 1 slot(INITRANS is 1) and can be allocated maximum 10 slots (MAXTRANS is 10), info  about one transaction is placed in one slot, then  comes another transaction and its info can’t be placed in the next slot(causes waiting) but as we know we can have 9 other available slots, still can’t be placed, why?

Let me draw some pictures so that you can easily guess what happens.

–Newly created block with initially one slot(during creation table we indicated INITRANS 1) looks like this:

Empty Block with one ITL slot

–Three rows are inserted in the block.

Full block with one ITL slot

–One transaction modified one row and did not commit. The Info is written to the available slot.

Full block with one alocated ITL slot(locking,filling ITL slot)

–Another transaction comes and wants to modify row, consequently info about this transaction should be placed in another free slot(do not forget we have initially one slot and number of this slots can be increased till 10)

Full block with one alocated ITL slot and not enought space for another slot

Note:In this case there is not empty space for new slot, that is why it can’t be allocated… If we have had indicated initially more slots another transaction would be able to be placed, as you can guess to solve this problem you should indicate bigger INITRANS during creation of object, or alter object and change this parameter…For the blocks which are already full as I can guess it will not affect, but for future it will be usefull. However this also means that there is less space in the block for actual data.

Filed under DB Tagged with ,

Apex Uninstall

December 21, 2010 Leave a comment

It is a very simple to uninstall apex.

1.Just go to the folder where apex exists(where you unzipped Oracle Application Express software).

2.Connect to the database as a SYS user:

On Windows:

SYSTEM_DRIVE:\ sqlplus /nolog
SQL> CONNECT SYS as SYSDBA
Enter password:SYS_password

On UNIX and Linux:

$ sqlplus /nolog
SQL>CONNECT SYS as SYSDBA
Enter password: SYS_password

3. Execute the following command:

SQL> @apxremov.sql

Filed under DB Tagged with ,

Gather table statistics(using dbms_stats)

December 20, 2010 Leave a comment

World Scenario
If we have had a big table and then we decided to delete a big amount of rows from this table.Oracle “knew” that it was a big table on which a query was slow and now it is a small table on which a query should be faster than the old one,BUT oracle still needs more time to execute a query…
The question is why???
BECAUSE oracle still has the old statistics on this table ,which means that oracle still thinks that it is a big table…And the solution is to gather new statistics manually:

begin
    dbms_stats.unlock_table_stats(ownname =>'username',
                                  tabname=>'table_name);
    dbms_stats.gather_table_stats(ownname =>'username',
                                  tabname=>'table_name',
                                  degree => 4,
                                  cascade => true);
end;
Note:
dbms_stats.unlock_table_stats
Table statistics can be locked..This is a good opportunity to be sure that estimated statistics will not be changed.So to estimate new statistics you should unlock it.
dbms_stats.gather_table_stats
Here, not easy understandable option is just cascade.
Cascade->Indicates oracle to gather statistics on the indexes also for this table.

Filed under DB Tagged with

Global Hints

December 20, 2010 Leave a comment

Oracle hints just refer to the table in the query. But there exist GLOBAL HINTS which allows you specify hints for a table within a view.

For example hint index:

SELECT /*+ index(v_standart_struct.ss indx_name) */col1,
col2,
col3 - 15,
col4
FROM v_standard_struct
WHERE col1= 910
AND col2 = 2
ORDER BY col4;
Note: I have written “.ss”-> alias of the table within a view, not the actual name. When table has alias, you must use it. If you forget it and write the actual name of the table, but not alias, it will not work.(If table doesn’t have an alias,you must use the actual name.)

Filed under DB Tagged with ,

Oracle Data Miner(Installation)

December 20, 2010 Leave a comment

     Data Miner is a graphical user interface, which helps you to mine data.It builds models based on the historical data and by these models it can make approximate predicates about how business data will change.
It has built in algorithms by which it does this predicates. But now I will not discuss what Data Miner is and what Data Miner does or its architecture, I will just give you the scripts which will install it, and we also will install demo examples(demo data ,which will be useful for the people who are inexperienced in Oracle Data Miner ).

So let’s Start:

1. Download oracle data miner from:
http://www.oracle.com/technetwork/database/options/odm/downloads/index.html

2.Connect as SYS  and create a new user “dmuser” which will be the owner of data miner objects.

SQL>CREATE USER dmuser 
IDENTIFIED BY dmuser
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP
QUOTA UNLIMITED ON USERS;

3.If you have the following script file ” dmshgrants.sql” then you can simply run it by the following way:

Go to the folder where this file exists , connect database as SYS and do the following:

SQL>@dmshgrants.sql
Value for 1:sh
Value for 2:dmuser
Note:
 Value for 1 is a password for “SH” user 
 Value for 2  is a username for our newly created user.(In this case “dmuser”)

If you don’t have this file you can download it from here: dmshgrants.sql


4. If the user will need to import or export data mining models, grant this additional privilege.

SQL>GRANT CREATE ANY DIRECTORY TO dmuser; 

Connect Database as a dmuser and run the following:

5.If you have the following file just go to the directory where this file exists and run:

SQL>@dmsh.sql

 If you don’t have it, download this file from here:dmsh.sql

 To run Data Miner and use its features go to the directory, where data miner was downloaded, go to the bin directory and run odminerw.exe executable file . Click the button “Edit” and enter the correct information.(Do not confuse, username/password should be dmuser/dmuser)

Filed under DB Tagged with ,

Apex Installation(v4.0)

December 20, 2010 Leave a comment

1.Oracle Application Express Installation Requirements

1.1 Oracle Database Requirement

Oracle Application Express version 4.0 requires an Oracle database (Enterprise Edition, Standard Edition or Standard Edition One) that is release 10.2.0.3 or higher. Application Express 4.0 can also be used with Oracle Database 10g Express.

1.1.1 Checking the shared_pool_size of the Target Database

Note:Ignore this requirement if your configuration uses non-null values for the database initialization parameters SGA_TARGET (in Oracle Database 10g and 11g) or MEMORY_TARGET (in Oracle Database 11g).

Oracle Application Express requires the shared_pool_size of the target database to be at least 100 MB.

To check the shared_pool_size of the target database:

1. Start the database:

SQL> STARTUP

2. If necessary, enter the following command to determine whether the system uses an initialization parameter file (initSID.ora) or a server parameter file (spfileDBNAME.ora):

SQL> SHOW PARAMETER PFILE;

If in the output the value of spfile is not empty,this means that database is started by spfile. If value is empty-database is started by pfile.

3.  Determine the current values of the shared_pool_size parameter:

SQL> SHOW PARAMETER SHARED_POOL_SIZE

4.   If the system is using a server parameter file, set the value of the SHARED_POOL_SIZE initialization parameter to at least 100 MB:

SQL> ALTER SYSTEM SET SHARED_POOL_SIZE='100M' SCOPE=spfile;

5.  Shut down the database:

SQL> SHUTDOWN

6.  Restart the database:

SQL> STARTUP

1.2 Browser Requirements

To view or develop Oracle Application Express applications, Web browsers must support Java Script and the HTML 4.0 and CSS 1.0 standards. The following browsers are required to develop applications in Oracle Application Express:

  • Microsoft Internet Explorer 7.0 or later version
  • Mozilla Firefox 3.5 or later version
  • Google Chrome 4.0 or later version
  • Apple Safari 4.0 or later version

Application Express applications can be developed that support earlier Web browser versions, including Microsoft Explorer 6.0.

1.3 Disk Space Requirement

Oracle Application Express disk space requirements are as follows:

  • Free space for Oracle Application Express software files on the file system: 450 MB if using English only download (apex_4_0_en.zip) and 1 GB if using full download (apex_4_0.zip).
  • Free space in Oracle Application Express tablespace: 185 MB
  • Free space in SYSTEM tablespace: 100 MB
  • Free space in Oracle Application Express tablespace for each additional language (other than English) installed: 75 MB

1.4 Oracle XML DB Requirement

Oracle XML DB must be installed in the Oracle database that you want to use. If you are using a preconfigured database created either during an installation or by Database Configuration Assistant (DBCA), Oracle XML DB is already installed and configured.

See Also: Oracle XML DB Developer’s Guide for more information about manually adding Oracle XML DB to an existing database

Tip: The installer does a prerequisite check for Oracle XML DB and will exit if it is not installed.
Tip: The installation of Oracle XML DB creates the user ANONYMOUS. In order for Oracle Application Express workspace to work properly, the ANONYMOUS user must not be dropped from the database.

1.5 PL/SQL Web Toolkit

Oracle Application Express requires the PL/SQL Web Toolkit version 10.1.2.0.6 or later. For instructions on determining the current version of the PL/SQL Web Toolkit, and for instructions on installing version 10.1.2.0.6, please review the README.txt file contained in the directory apex/owa.

2. install Oracle Application Express

2.1 Recommended Pre-installation Tasks

Before installing Oracle Application Express, Oracle recommends that you complete the following steps:

1.      Shut down with normal or immediate priority the Oracle Database instances where you plan to install Oracle Application Express. On Oracle Real Application Clusters (Oracle RAC) systems, shut down all instances on each node.

  1. Back up the Oracle Database installation. Oracle recommends that you create a backup of the current Oracle Database installation before you install Oracle Application Express. You can use Oracle Database Recovery Manager, which is included the Oracle Database installation, to perform the backup.
  2. Start the Oracle Database instance that contains the target database. After backing up the system, you must start the Oracle instance that contains the target Oracle database. Do not start other processes such as the listener or Oracle HTTP Server. However, if you are performing a remote installation, make sure the database listener for the remote database has started.

2.2 Download and Install Oracle Application Express

To install Oracle Application Express:

1.For installations where the development will be in English only, download the file apex_4.0_en.zip from the Oracle Application Express download page. Where the development will include languages other than English, download apex_4.0.zip from the Oracle Application Express download page. See:

http://www.oracle.com/technology/products/database/application_express/download.html

Note that the actual file name may differ if a more recent release has shipped since this document was published.

2.Unzip downloaded zip file:

  • UNIX and Linux: Unzip apex_4.0.zip
  • Windows: Double click the file apex_4.0.zip in Windows Explorer

3.Change your working directory to apex.

4.Start SQL*Plus and connect to the database where Oracle Application Express is installed as SYS specifying the SYSDBA role. For example:

On Windows:

SYSTEM_DRIVE:\ sqlplus /nolog
SQL> CONNECT SYS as SYSDBA
Enter password: SYS_password

On UNIX and Linux:

$ sqlplus /nolog
SQL> CONNECT SYS as SYSDBA
Enter password: SYS_password

And run the command:

SQL>@apexins SYSAUX SYSAUX TEMP /i/
Note:
Arguments:
Position 1: Name of tablespace for Application Express application user(in this case  SYSAUX)
Position 2: Name of tablespace for Application Express files user (in this case SYSAUX)
Position 3: Name of temporary tablespace (in this case TEMP)
Position 4: Virtual directory for APEX images   (in this case /i/)

When Oracle Application Express installs it creates three new database accounts:

  • APEX_040000 – The account that owns the Oracle Application Express schema and metadata.
  • FLOWS_FILES – The account that owns the Oracle Application Express uploaded files.
  • APEX_PUBLIC_USER – The minimally privileged account used for Oracle Application Express configuration with Oracle HTTP Server and mod_plsql or Oracle Application Express Listener.

2.3 Change the Password for the ADMIN Account

To change the password for the ADMIN account:

  1. Change your working directory to the apex directory where you unzipped the installation software.
  2. Start SQL*Plus and connect to the database where Oracle Application Express is installed as SYS specifying the SYSDBA role. For example:

On Windows:

SYSTEM_DRIVE:\ sqlplus /nolog
SQL> CONNECT SYS as SYSDBA
Enter password: SYS_password

On UNIX and Linux:

$ sqlplus /nolog
SQL> CONNECT SYS as SYSDBA
Enter password: SYS_password

3.Run apxchpwd.sql. For example:

SQL>@apxchpwd

When prompted enter a password for the ADMIN account.

2.4 Restart Processes

After you install Oracle Application Express, you must restart the processes that you stopped before you began the installation, such as listener and other processes.

2.5 Configure the Embedded PL/SQL Gateway

2.5 .1 Running the apex_epg_config.sql Configuration Script
The embedded PL/SQL gateway installs with the Oracle Database 11g. However, you must configure it before you can use it with Oracle Application Express. To accomplish this, you run a configuration file and unlock the ANONYMOUS account.

To run the apex_epg_config.sql configuration script:

1.Change your working directory to the apex directory where you unzipped the Oracle Application Express software.

2.Start SQL*Plus and connect to the database where Oracle Application Express is installed as SYS specifying the SYSDBA role. For example:

On Windows:

SYSTEM_DRIVE:\ sqlplus /nolog
SQL> CONNECT SYS as SYSDBA
Enter password: SYS_password

On UNIX and Linux:

$ sqlplus /nolog
SQL> CONNECT SYS as SYSDBA
Enter password: SYS_password

3.Run apex_epg_config.sql passing the file system path to the base directory where the Oracle Application Express software was unzipped as shown in the following example:

On Windows:

SQL> @apex_epg_config SYSTEM_DRIVE:\directory_name
Note:
I’ve windows 7 and I’ve unzipped it to the D:\install directory, so I should write the following:
SQL> @apex_epg_config  D:\install
P.S Just for clarification. My file hierarchy is the following:  D:\install\apex\images\
from here you can guess what should be indicated as a parameter for “apex_epg_config” script.

On UNIX and Linux:

SQL> @apex_epg_config  /directory_name
  1. Enter the following statement to unlock the ANONYMOUS account:
SQL> ALTER USER ANONYMOUS ACCOUNT UNLOCK;

2.5.2 Updating the Images Directory When Upgrading from a Previous Release

If you are upgrading Oracle Application Express from a previous release, you must run the apxldimg.sql script to update the images directory.

Tip:
If you are not upgrading from a prior release of Oracle Application Express, this step is unnecessary. The images will be loaded by runningapex_epg_config.sql as described in the prior section.

To run the apxldimg.sql script:

1.Start SQL*Plus and connect to the database where Oracle Application Express is installed as SYS specifying the SYSDBA role:
On Windows:

SYSTEM_DRIVE:\ sqlplus /nolog
SQL> CONNECT SYS as SYSDBA
Enter password: SYS_password

On UNIX and Linux:

$ sqlplus /nolog
SQL> CONNECT SYS as SYSDBA
Enter password: SYS_password

2.Run apxldimg.sql passing the file system path to the base directory where the Oracle Application Express software was unzipped as shown in the following example:

On Windows:

@apxldimg.sql SYSTEM_DRIVE:\directory_name

On UNIX and Linux:

@apxldimg.sql /directory_name
Tip:
The above examples assume that you unzipped Oracle Application Express in a SYSTEM_DRIVE:\directory_name on Windows and /directory_name on UNIX or Linux.

2.5 .3 Verifying the Oracle XML DB HTTP Server Port

The embedded PL/SQL gateway runs in the Oracle XML DB HTTP server in the Oracle database. You can determine if the Oracle XML DB HTTP server is enabled by verifying the associated port number.

To verify the port number where the Oracle XML DB HTTP Server is running:

1.Start SQL*Plus and connect to the database where Oracle Application Express is installed as SYS specifying the SYSDBA role:

On Windows:

SYSTEM_DRIVE:\ sqlplus /nolog
SQL> CONNECT SYS as SYSDBA
Enter password: SYS_password

On UNIX and Linux:

$ sqlplus /nolog
SQL> CONNECT SYS as SYSDBA
Enter password: SYS_password

2.Enter the following statement to verify the port number:

SELECT DBMS_XDB.GETHTTPPORT FROM DUAL;

If the port number returns 0, the Oracle XML DB HTTP Server is disabled.

3.To enable it, follow the instructions in 2.5 .3 Enabling Oracle XML DB HTTP Server.

2.5 .4  Enabling Oracle XML DB HTTP Server

The embedded PL/SQL gateway runs in the Oracle XML DB HTTP server in the Oracle database.

To enable Oracle XML DB HTTP server:

1.Start SQL*Plus and connect to the database where Oracle Application Express is installed as SYS specifying the SYSDBA role. For example:

On Windows:

SYSTEM_DRIVE:\ sqlplus /nolog
SQL> CONNECT SYS as SYSDBA
Enter password: SYS_password

On UNIX and Linux:

$ sqlplus /nolog
SQL> CONNECT SYS as SYSDBA
Enter password: SYS_password

2.Enter a statement similar to the following:

EXEC DBMS_XDB.SETHTTPPORT(port);

For example:

EXEC DBMS_XDB.SETHTTPPORT(8080);

2.7 About Managing JOB_QUEUE_PROCESSES

JOB_QUEUE_PROCESSES determine the maximum number of concurrently running jobs. In Oracle Application Express release 4.0, transactional support and SQL scripts require jobs.

If  JOB_QUEUE_PROCESSES is not enabled and working properly, you cannot successfully execute a script.

2.7.1 Viewing JOB_QUEUE_PROCESSES from SQL*Plus

SELECT VALUE FROM v$parameter WHERE NAME = 'job_queue_processes'
Note:(I have 1000)

2.7.2 Changing the Number of JOB_QUEUE_PROCESSES

You can change the number of JOB_QUEUE_PROCESSES by running a SQL statement in SQL*Plus:

To update the number of JOB_QUEUE_PROCESSES:

1.Start SQL*Plus and connect to the database where Oracle Application Express is installed as SYS specifying the SYSDBA role:

On Windows:

SYSTEM_DRIVE:\ sqlplus /nolog
SQL> CONNECT SYS as SYSDBA
Enter password: SYS_password

On UNIX and Linux:

$ sqlplus /nolog
SQL> CONNECT SYS as SYSDBA
Enter password: SYS_password

2.In SQL*Plus run the following SQL statement:

ALTER SYSTEM SET JOB_QUEUE_PROCESSES = <number>

For example, running the statement ALTER SYSTEM SET JOB_QUEUE_PROCESSES = 20 sets JOB_QUEUE_PROCESSES to 20.

2.8 Configuring the SHARED_SERVERS Parameter

The embedded PL/SQL gateway uses the shared server architecture of the Oracle Database. To achieve acceptable performance when using the embedded PL/SQL gateway, ensure the SHARED_SERVERS database initialization parameter is set to a reasonable value (that is, not 0 or 1). For a small group of concurrent users, Oracle recommends a value of 5 for SHARED_SERVERS.

Consider the following example:

  1. Start SQL*Plus and connect to the database where Oracle Application Express is installed as SYS specifying the SYSDBA role. For example:

On Windows:

SYSTEM_DRIVE:\ sqlplus /nolog
SQL> CONNECT SYS as SYSDBA
Enter password: SYS_password

On UNIX and Linux:

$ sqlplus /nolog
SQL> CONNECT SYS as SYSDBA
Enter password: SYS_password
  1. Run the following statement:
ALTER SYSTEM SET SHARED_SERVERS = 5 SCOPE=BOTH;
Note: If database is not started by spfile shutdown and start it by spfile.

2.9 Unlock APEX_PUBLIC_USER user and change its password.

1.Connect as SYS user:

On Windows:

SYSTEM_DRIVE:\ sqlplus /nolog
SQL> CONNECT SYS as SYSDBA
Enter password: SYS_password

On UNIX and Linux:

$ sqlplus /nolog
SQL> CONNECT SYS as SYSDBA
Enter password: SYS_password

2.Run the following command:

alter user APEX_PUBLIC_USER account unlock identified by new_password;

Go to the link to check if it works

http://hostname:port/apex/apex_admin

Filed under DB Tagged with ,

Installing Oracle 11g on RedHat 5.5(64 bit)

December 15, 2010 Leave a comment

Part I: Pre Installation

To list all rpms(for defining what packages are installed ), run the following command:

rpm –qa

You should have installed the following rpms, but if you don’t have them, you should install.
But now we are doing an upgrade, because assume that these packages exist,but old versions(as it should be):

rpm –Uvh libaio-devel-0.3.106-3.2.i386.rpm
rpm –Uvh libaio-devel-0.3.106-3.2.x86_64.rpm
rpm –Uvh pdksh-5.2.14-36.el5.x86_64.rpm
rpm –Uvh unixODBC-2.2.11-7.1.i386.rpm
rpm –Uvh unixODBC-2.2.11-7.1.x86_64.rpm
rpm –Uvh unixODBC-devel-2.2.11-7.1.i386.rpm
rpm –Uvh unixODBC-devel-2.2.11-7.1.x86_64.rpm

Note: RPM(RedHat Packet Manager) is a program for installing, uninstalling, verifying,
querying, and updating software packages. The Option –Uvh tells the manager to upgrade
the following packages.(These packages is needed for oracle to install software).
If you don’t have these packages you should write –ivh,  which means install, instead of -Uvh.

Part II: Configuring Linux for Oracle:

–First of all, you should create directory where oracle software will be kept.

mkdir -p /u0/app/oracle
groupadd oinstall
groupadd dba
useradd –s /bin/bash –d /u0/app/oracle –g oinstall –G dba oracle
passwd oracle
chown -R oracle:oinstall /u0/app
chmod -R 777 /u0/app

Explanation:
1)Option –p for mkdir tells linux to create  the parent directories if they don’t exist.
For example if  “app” or “u0” do not exist they will automatically be  created.
2) groupadd command creates some special group, in this case oinstall and dba.
3) useradd command will create a new user.
* –s means shell-> /bin/bash is a path of user’s login shell.
* –d directory->/u0/app/oracle  is a path where oracle software (will be)/is located.
* –g the group name for a new user’s initial group. The named group must exist.
* –G A list of supplementary groups which the user is also a member of
(separated by comma for example -G  group1,group2).

4) passwd sets/changes the password for user.
5) chown -R Changes the owner for the following files/directories recursively.
* oracle is an username.
* oinstall initial group name for this user.
* /u0/app path of the folder for which the owner must  be changed.
6) chmod –R changes the permission of a file recursively(for subdirectories also )
* 777 all permission(read,write,execute)
*/u0/app path of a directory, on which permissions must be assigned.

–Modify  the following file

/etc/sysctl.conf

–And add/update the following lines these are parameters which is needed for oracle
to install software:

fs.aio-max-nr = 1048576
fs.file-max = 6815744
kernel.shmall = 2097152
kernel.shmmax = 536870912
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048586

–Modify the following file:

/etc/security/limits.conf

–And add/update the following lines these are parameters which is needed for
oracle to install software:

oracle soft nproc 2047
oracle hard nproc 16384
oracle soft nofile 1024
oracle hard nofile 65536

Part III

Reboot system

Part IV :Install Oracle Software

Run Oracle Universal Installer (OUI) by finding runInstaller.sh file and executing it by the following way:

Go to the directory where this executable file exists and type the following command:

./ runInstaller.sh

Note: The runInstaller.sh executable performs a preinstall check of the operating system and hardware resources before starting the OUI graphical tool(If this fails, please  repeat  steps in part I, part II and part III,or your computer doesn’t have anough space for oracle db)

After that, there will appear Graphical  User Interface which helps you to install just oracle software or software with database it is up to you. Follow the instructions.

Note: There will appear alert ,during the installation,which tells you to run root.sh and also shows the path where this file exists. Change your directory location to this path and run root.sh file. Executing the root.sh script copies some files to a location outside $ORACLE_HOME and sets the permissions on several files inside and outside $ORACLE_HOME. Once the root.sh script executes successfully, click OK to continue the installation.

Part V: Post Installation

If you have installed oracle software with database ,or then created db by DBCA(Database Configuration Assistant) you should do the following. DBCA executable file is located in  $ORACLE_HOME/bin.
First of all, let’s discuss what oratab file is:
“oratab” is a file created by Oracle in the /etc or /var/opt/oracle directory when installing database software. Originally ORATAB was used for SQL*Net V1, but lately is’s being used to list the databases and software versions installed on a server. This file contains  lines consisting of entries in the following format:

database_sid:oracle_home_dir:Y|N

* database_sid is the system id (SID) of an Oracle instances on the server.
* Oracle_home_dir is the ORACLE_HOME directory associated with this instance.
* The Y|N flags indicate if the instance should automatically start at boot time (Y=yes, N=no).

So now, when we already know what oratab file contains,modify it by the following entry set the restart flag for each instance to ‘Y’:

DB11G:/u01/app/oracle/product/11.2.0/db_1:Y

Which means that DB11G instance,which database is located in /u01/app/oracle/product/11.2.0/db_1 must be automatically started.

That is all… I hope it was useful for you.

Filed under DB Tagged with ,