SQL WorkShop About Oracle Architecture

– DataBase Version : Oracle XE 10g
– Startup and shutdown database
– Create database
– Control files
– Redo log files
– Table spaces and data files
– Rollback segments
– Temporary segments

SQL > conn sys/pswd as sysdba;

– I prefer to shutdown immediate =)

SQL >SHUTDOWN ABORT
SQL >SHUTDOWN IMMEDIATE
SQL >SHUTDOWN NORMAL
SQL >SHUTDOWN TRANSACTIONAL

SQL >STARTUP PFILE='filepath/initXE.ora'

SQL >CREATE DATABASE BD1
 MAXLOGFILES 5
 MAXLOGMEMBERS 5
   MAXDATAFILES 100
LOGFILE
    GROUP 1 '[path]/log1a.rdo' SIZE 100 M,
    GROUP 2 '[path]/log2a.rdo' SIZE 100 M
DATAFILE
    '[path]/system01.dbf' size 200 M
SQL >exec catalog.sql -- create dictionary views
SQL >exec catproc.sql -- create pl/sql scripts for server 

– accessible for nomount and above

SQL >select * from v$parameter
SQL >select * from v$sga
SQL >select * from v$process
SQL >select * from v$session
SQL >select * from v$version
SQL >select * from v$instance

– accessible for mount and above

SQL >select * from v$thread
SQL >select * from v$controlfile
SQL >select * from v$database
SQL >select * from v$datafile
SQL >select * from v$logfile

– how to terminate a user session

SQL >select sid, serial# from v$session where username = 'HR';

SID SERIAL#
———- ———-
37 100

SQL> alter system kill session '37,100';
System altered.

– redo log files.

SQL> select group#, sequence#, bytes,members, status from v$log

SQL> select * from v$logfile

– adding new log file rdolog3.rdo

SQL> ALTER DATABASE ADD LOGFILE
('C:\ORACLEXE\APP\ORACLE\FLASH_RECOVERY_AREA\XE\ONLINELOG\RDOLOG3.rdo') size 10M;

– add redo log file to group 1

SQL>ALTER DATABASE ADD LOGFILE MEMBER
'C:\ORACLEXE\APP\ORACLE\FLASH_RECOVERY_AREA\XE\ONLINELOG\RDOLOG3.rdo' TO GROUP 1
SQL> ALTER DATABASE DROP LOGFILE GROUP 3

SQL> ALTER DATABASE DROP LOGFILE MEMBER 'C:\ORACLEXE\APP\ORACLE\FLASH_RECOVERY_AREA\XE\ONLINELOG\RDOLOG3.rdo'

SQL>CREATE TABLESPACE app_data
DATAFILE '[path]/app01.dbf' SIZE 100M,
         '[path]/app02.dbf' SIZE 100M
MINIMUM EXTENT 500K
DEFAULT STORAGE (INITIAL 500K
                   NEXT 500K
                   MINEXTENTS 3
                   MAXEXTENTS 500
                   PCTINCREASE 50 );

– INITIAL size of first extent. size of blocks
– NEXT size of second extent. size of blocks
– MINEXTENTS num of extents for created segments
– PCTINCREASE extent size grows
– MAXEXTENTS max num of extents for segments

– adding new tablespace

SQL > ALTER TABLESPACE app_data ADD DATAFILE ('[file_path]/app03.dbf' SIZE 200M);
SQL > ALTER TABLESPACE app_data ADD DATAFILE '[file_path]/app04.dbf' SIZE 200M
AUTOEXTEND ON NEXT 10M MAXSIZE 500M;

– enable autoextension for a datafile

SQL > ALTER DATABASE DATAFIEL '[file_path]/app03.dbf'
AUTOEXTEND ON NEXT 10M MAXSIZE 500M;

– changing the size of Data Files Manually

SQL > ALTER DATABASE DATAFILE '[file_path]/app02.dbf' RESIZE 200M;

– make tablespace read only
– must not contain rollback segments
– must not involved in a online backup

SQL > ALTER TABLESPACE app_data READ ONLY;
SQL > select tablespace_name, contents, status from dba_tablespaces;
SQL > select tablespace_name, initial_extent,next_extent, max_extents, pct_increase, min_extlen
from dba_tablespaces;
SQL > select file_name, tablespace_name, bytes, autoextensible, maxbytes, increment_by from dba_data_files;

– DataBlock Parameters
– INITRANS
– MAXTRANS
– PCTFREE
– PCTUSED

– about storage

SQL > select segment_name, tablespace_name, extents, blocks from dba_segments where owner = 'SYS'
SQL > select extent_id, file_id, block_id, blocks
from dba_extents
where owner='SYS' and segment_name = 'ERROR$'
SQL > select tablespace_name, COUNT(*),max(blocks), sum(blocks) from dba_free_space group by tablespace_name;

– if changes made to rallback segment are also
– protected by redo log files we can rollback all changes
– after unconditional instance fails

– request a rollback segment for this transaction

SQL > select segment_name,SEGMENT_TYPE from dba_segments where owner = 'SYS' and segment_type = 'ROLLBACK'

– SET TRANSACTION USE ROLLBACK SEGMENT SYSTEM
– create new rollback segment

SQL > CREATE ROLLBACK SEGMENT rlbs01
TABLESPACE SYSTEM
STORAGE (INITIAL 100K
         NEXT 100 K
         OPTIMAL 4M
         MINEXTENTS 20
         MAXEXTENTS 100 );

– make it online

SQL > ALTER ROLLBACK SEGMENT rbls01 online
SQL > ALTER ROLLBACK SEGMENT rbls01 offline
SQL > DROP ROLLBACK SEGMENT rbls01;

– list rollback segments

SQL > select segment_name, tablespace_name, owner, status from dba_rollback_segs;

– show status of rollback segments

select usn,extents,rssize,optsize,hwmsize,xacts,status from v$rollstat

– if SORT_AREA_SIZE is full, swap data to temporary segments.

SQL > select * from dba_segments
SQL > select * from v$sort_segment
SQL > create view large_table_view as
select * from dba_source
   UNION ALL select * from dba_source
   UNION ALL select * from dba_source
   UNION ALL select * from dba_source;

– new session for sort operation

SQL > select DISTINCT * from large_table_view

– show currently active sorts (run using different session )

SQL > select * from v$sort_usage

– deallocate of unused (after extend defragmentation ) extents
– to be able to use by other segments

SQL > ALTER TABLE hr.t1 DEALLOCATE UNUSED;

– ANALYZE TABLE
– validate integrity of data blocks in the table.

SQL > ANALYZE TABLE hr.t1 VALIDATE STRUCTURE;

– compute statistics

SQL > ANALYZE TABLE hr.t1 COMPUTE STATISTICS;

SQL Trace and TKPROF

– start sql trace to the session.

set session for trace sql
alter session set timed_statistics = true;

alter session set sql_trace = true;

alter session set max_dump_file_size =unlimited;
alter session set STATISTICS_LEVEL = ALL ;

alter session set statistics_level = all ;
alter session set tracefile_identifier = ‘pfzerprb’;
alter session set events ‘10046 TRACE NAME CONTEXT FOREVER, LEVEL 8′;

–stop sql trace for the session after execution.
alter session set sql_trace = false;

–where is sql trace files? find [filepath]
select value from v$parameter where name = ‘user_dump_dest’

– make trace files more readable using tkprof
tkprof [filepath]\filename.TRC D:\sql_trace.txt explain= [user/psw] sys=no waits=yes

Row Numbering and Ranking Functions

1. ROWNUM without Analytic Function

select employee_id, name , salary, ROWNUM
from employees ORDER BY salary;exec

2. ROW_NUMBER ( )

select employee_id, name, salary, ROW_NUMBER() OVER (ORDER BY salary  )toprank
FROM employees;exec

3. RANK ( )

select employee_id, name, salary, RANK() OVER (ORDER BY salary )toprank
FROM employees;exec

4. DENSE_RANK ( ) with WHERE clause

select * from
( select employee_id, name, salary, DENSE_RANK() OVER (ORDER BY salary  desc ) toprank
FROM employees )t
where t.toprank < 3;exec

5. RANK ( ) with order by

select employee_id, first_name, salary, RANK() OVER (ORDER BY salary  desc) toprank
FROM employees
order by salary;exec

6. RANK ( ) with a join

select e.employee_id, e.name, j.job_title, e.salary,
RANK() OVER (ORDER BY e.salary desc) rankorder
from employees e, jobs j
where e.salary < 3000
and e.job_id = j.job_id
order by salary;exec

7. RANK ( ) contains a join, group by and order by

select j.job_title, COUNT(*), MAX(e.salary) maxsalary,
MIN(e.salary) minsalary,
RANK() OVER (ORDER BY MAX(salary)) rankorder
FROM employees e, jobs j
where e.salary < 5000
AND e.job_id = j.job_id
GROUP BY j.job_title
ORDER BY j.job_title;exec

8. RANK and DENSE_RANK ( ); GROUP BY, HAVING and ORDER BY clause

More than one analytical function can be used in one sql.

select j.job_title,COUNT(*),MAX(salary) maxsalary,
MIN(salary) minsalary,RANK () OVER (ORDER BY MAX(salary)) rankorder,
DENSE_RANK () OVER (ORDER BY MIN(salary)) rankorder
FROM employees e, jobs j
WHERE e.salary < 6000
AND e.job_id = j.job_id
GROUP BY j.job_title
HAVING MIN(salary) > 2000
ORDER BY j.job_title desc;exec

9. ROW_NUMBER ( ) and NULLS LAST

select e.employee_id, e.name, e.salary curr_salary ,
ROW_NUMBER () OVER (ORDER BY salary desc NULLS LAST ) salary
from employees e
ORDER BY curr_salary desc NULLS LAST;exec

10. RANK ( ),  NVL function and NULLS LAST

SELECT e.employee_id, e.name, NVL (salary, 9999),
RANK () OVER (ORDER BY NVL (salary,9999) desc NULLS LAST) salary
from empnls e
where employee_id < 115
ORDER BY salary desc NULLS LAST;exec

11. RANK ( ), Order by after NLV function

SELECT employee_id,
name,
NVL(salary, 6666),
RANK() OVER (ORDER BY NVL (salary, 6666) desc) curr_salary
FROM empnls
ORDER BY curr_salary;exec

12. RANK ( ), Partitioning with PARTITION_BY

SELECT employee_id,
       name,
       department_id,
       salary,
       RANK() OVER(PARTITION BY department_id ORDER BY salary desc) rank
  FROM employees
  ORDER BY department_id;exec

13.  NTILE ( )

This function must apply same segment at the same intervals except last interval and if the remaining segment is equals or more than rownum, segment is always equals rownum.

Interval = (ROUND(rowcount /segment));

SELECT employee_id,
       name,
       salary,
       NTILE(4) OVER(ORDER BY salary desc) ntlist4,
       NTILE(8) OVER(ORDER BY salary desc) ntlist8,
       NTILE(100) OVER(ORDER BY salary desc) ntlist107,
       NTILE(120) OVER(ORDER BY salary desc) ntlist120
 FROM employees;exec

Case expression in sql

We can use case expression in PL/SQL basically or as a sql expression to extend query

flexibility.

Basic Sytax :

case when <condition> then <value>
when <condition> then <value>
...
else <value

end

Basic Example :

select sal, case when sal < 2000 then 'low'
                 when sal < 3000 then 'middle'
                 when sal < 4000 then 'high'
                 else 'top'
            end as "categories"
from emp;

Increase your experience on advanced Apex

We can find the advanced features of Apex in these presentations.

It is necessary increase the expresience to use apex more efficiently.

This is the link of advanced apex presentations.

Presentations

Open Apex to public

By default, apex just works on localhost,

it is not possible to share your applications with grid control by default.

Use this process to open apex to pubklic:

begin
DBMS_XDB.SETLISTENERLOCALACCESS(FALSE);
COMMIT ;
end;

Upgrade to APEX 3.0 on Oracle 10g r2

Upgrade operations sometimes might be more effortable than we assume.

As Apex 2.0.1 comes with Oracle 10g r2, we need to upgrade apex 3.0 with some

operations.

You can download and install APEX 3.0 from here:

Installing the Oracle Application Express Software

Shortly, you will run apexins.sql with these arguments:

@apexins password SYSAUX SYSAUX TEMP /i/

This steps seems really simple but we have a problem that

images arent displaying in APE. It looks like it hasnt copied the new images over or hasnt update the links properly.

The solution is :

Rerun the command:

@APEX_HOME/apex/apxldimg.sql APEX_HOME

substituting APEX_HOME for wherever you extracted the Apex 3.0.1 zip file. If you extracted it to c:\apextmp or /apextmp in Unix then run

@C:\apextmp\apex\apxldimg.sql c:\apextmp

or
@/apextmp/apex/apxldimg.sql /apextmp

Related link for this problem is here:

upgrade problem to APEX 3

Introduction to Triggers

Triggers are like to the procedures stored in Java, PL/SQL or call out to a C procedure but these are implicitly implemented by database due to some kinds of database actions:

• a table or view is modified with DML DDL Statements
• some user actions issued either by a particular schema/user or by any schema/user
• database system actions such as logon/logoff, errors, or startup/shutdown

What is difference between stored procedures and triggers?

Procedures and triggers differ in the way that they are invoked. A procedure is explicitly run by a user, application, or trigger. Triggers are implicitly fired by Oracle when a triggering event occurs, no matter which user is connected or which application is being used.

Oracle provides a highly customized database management system with triggers )

You can use triggers for all these operations:

• restrict DML operations against a table to those issued during regular business hours
• Automatically generate derived column values
• Prevent invalid transactions
• Enforce complex security authorizations
• Enforce referential integrity across nodes in a distributed database
• Enforce complex business rules
• Provide transparent event logging
• Provide auditing
• Maintain synchronous table replicates
• Gather statistics on table access
• Modify table data when DML statements are issued against views
• Publish information about database events, user events, and SQL statements to subscribing applications

Be careful how to use triggers!….

all of them can be problems in complex interdependencies and it is generally hard to handle.

Use always integrity constraints as usable as possible!….

• Oracle strongly recommends that you use triggers to constrain data input only in the following situations:
• To enforce referential integrity when child and parent tables are on different nodes of a distributed database
• To enforce complex business rules not definable using integrity constraints
• When a required referential integrity rule cannot be enforced using the following integrity constraints:

NOT NULL, UNIQUE,PRIMARY KEY,FOREIGN KEY,CHECK,DELETE CASCADE,DELETE SET NULL

Structure of Triggers

Components of Triggers

• A triggering event or statement

A triggering events might be one or group of database events. As you remember:

An INSERT, UPDATE, or DELETE statement on a specific table (or view, in some cases)

A CREATE, ALTER, or DROP statement on any schema object

A database startup or instance shutdown

A specific error message or any error message A user logon or logoff

• A trigger restriction

This is like check constraints on tables to check the boolean results from the defined restriction to fire a trigger or not. Such as

new.parts_on_hand < new.reorder_point

• A trigger action

After definition of the event and restriction if necessary you can write your own procedure to implement implicitly in database. As we define, these are action types:

Contain SQL, PL/SQL, or Java statements

Define PL/SQL language constructs such as variables, constants, cursors, exceptions

Define Java language constructs. Call stored procedures

Syntax of Triggers

CREATE [OR REPLACE] TRIGGER <trigger_name>

{BEFORE|AFTER} {INSERT|DELETE|UPDATE} ON <table_name>

[REFERENCING [NEW AS <new_row_name>] [OLD AS <old_row_name>]]

[FOR EACH ROW [WHEN (<trigger_condition>)]]

<trigger_body>

Heap Tables vs Object Tables

Heap table is the default oracle table type.

It is a default table definition as you use:

create table test_table

( no NUMBER(5), name VARCHAR2 (10) );

Object table has an logically object oriented architecture to provide reusable components and complex applications.

Object oriented programming is based on object types in PL/SQL.

create table t of some_type;

object type example

Union vs Union All

Union and union all operations are used to combine two or more queries in a table.

These queries must have same number of columns and these columns must have similar properties when we compare column by column.

Syntax:

n

select field1, field2, . field_n
from tables
UNION [ ALL ]
select field1, field2, . field_n
from tables;

The basic difference between UNION and UNION ALL is union operation eliminates the duplicated rows from the result set but union all returns all rows after joining.

For example :

X                  Y                   UNION                UNION ALL

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

A		   B                     A                      A

A		   B                     B                      A

B                  A  						B
  							        B

                                                                B

                                                                A

n

Let’s look at the validity of the similarities between columns for UNION [ALL] :

If component queries select character data, then the datatype of the return values are determined as follows:

• If both queries select values of datatype CHAR of equal length, then the returned values have datatype CHAR of that length. If the queries select values of CHAR with different lengths, then the returned value is VARCHAR2 with the length of the larger CHAR value.
• If either or both of the queries select values of datatype VARCHAR2, then the returned values have datatype VARCHAR2.

If component queries select numeric data, then the datatype of the return values is determined by numeric precedence:

• If any query selects values of type BINARY_DOUBLE, then the returned values have datatype BINARY_DOUBLE.
• If no query selects values of type BINARY_DOUBLE but any query selects values of type BINARY_FLOAT, then the returned values have datatype BINARY_FLOAT.
• If all queries select values of type NUMBER, then the returned values have datatype NUMBER.

In queries using set operators, Oracle does not perform implicit conversion across datatype groups. Therefore, if the corresponding expressions of component queries resolve to both character data and numeric data, Oracle returns an error.

Which one provides better performance ?

When you look at the autotrace result for both of them. On the execution plans, you can notice that that a union opration is already use an union all operation in execution.Shortly,

union = union all + sort operations

TEST RESULTS :autotrace for union vs union all

COMMENTS:

n

 

• nIf you can use UNION ALL, by all means use it over UNION to avoid a costly deduplication step, a step that is probably not even necessary most of the time.

• Always give a priority to union all!

• Use UNION ALL instead of UNION (wherever possible).

References:

nUNION VS UNION ALL

http://tonguc.wordpress.com

n

nOracle 10g Documentation Library SQL Reference

http://tahiti.oracle.com