how do i limit the number of rows returned by an oracle query after ordering

Source http://stackoverflow.com/questions/470542/how-do-i-limit-the-number-of-rows-returned-by-an-oracle-query-after-ordering

I did some performance testing between:

A.) Asktom

select * from (
  select a.*, ROWNUM rnum from (
    <select statemenet with order by clause>
  ) a where rownum <= MAX_ROW
) where rnum >= MIN_ROW

B.) Analytic approach

select * from (
  <select statemenet with order by clause>
) where myrow between MIN_ROW and MAX_ROW

C.) Short alternative

select * from (
  select statement, rownum as RN with order by clause
) where a.rn >= MIN_ROW and a.rn <= MAX_ROW

Results:

Table had 10 million records, sort was on an unindexed datetime row:

• Explain plan showed same value for all three selects (323168)
• But the winner is AskTom (with analytic following close behind)

Selecting first 10 rows took:

• AskTom: 28-30 seconds
• Analytical: 33-37 seconds
• Short alternative: 110-140 seconds

Selecting rows betwwen 100.000 and 100.010:

• AskTom: 60 seconds
• Analytical: 100 seconds

Selecting rows between 9.000.000 and 9.000.010:

• AskTom: 130 seconds
• Analytical: 150 seconds

CREATE USER WITH TABLESPACE in the ORACLE

The syntax for the CREATE USER statement in Oracle/PLSQL is:

CREATE USER user_name 
  IDENTIFIED { BY password
             | EXTERNALLY [ AS 'certificate_DN' ]
             | GLOBALLY [ AS '[ directory_DN ]' ]
             }
  [ DEFAULT TABLESPACE tablespace
  | TEMPORARY TABLESPACE
       { tablespace | tablespace_group }
  | QUOTA integer [ K | M | G | T | P | E ]
        | UNLIMITED }
        ON tablespace
    [ QUOTA integer [ K | M | G | T | P | E ]
        | UNLIMITED }
            ON tablespace
    ]
  | PROFILE profile_name
  | PASSWORD EXPIRE
  | ACCOUNT { LOCK | UNLOCK }
     [ DEFAULT TABLESPACE tablespace
     | TEMPORARY TABLESPACE
         { tablespace | tablespace_group }
     | QUOTA integer [ K | M | G | T | P | E ]
           | UNLIMITED }
           ON tablespace
       [ QUOTA integer [ K | M | G | T | P | E ]
           | UNLIMITED }
           ON tablespace
        ]
     | PROFILE profile
     | PASSWORD EXPIRE
     | ACCOUNT { LOCK | UNLOCK } ]
     ] ;

PARAMETERS OR ARGUMENTS

user_name is the name of the database account that you wish to create.

PROFILE profile_name is optional. It is the name of the profile that you wish to assign to the user account to limit the amount of database resources assigned to the user account. If you omit this option, the DEFAULT profile is assigned to the user.

PASSWORD EXPIRE is optional. If this option is set, then the password must be reset before the user can log into the Oracle database.

ACCOUNT LOCK or UNLOCK is optional. ACCOUNT LOCK disables access to the user account. ACCOUNT UNLOCK enables access to the user account.

 

EXAMPLE

CREATE USER smithj
  IDENTIFIED BY pwd4smithj
  DEFAULT TABLESPACE tbs_perm_01
  TEMPORARY TABLESPACE tbs_temp_01
  QUOTA 20M on tbs_perm_01;

This CREATE USER statement would create a new user called smithj in the Oracle database whose password is pwd4smithj, the default tablespace would be tbs_perm_01 with a quota of 20MB, and the temporary tablespace would be tbs_temp_01.

How to kill user session in oracle

select sid,serial#,username from v$session where username = ‘yourusername’;

alter system kill session ‘233,2707’ immediate;

get the sequence have created

select * from all_sequences;
select * from user_sequences;
select * from dba_sequences ;

view trigger on table

select trigger_body
from user_triggers
where triggering_event = 'INSERT'
and table_name = 'YOUR_TABLE';

How to Reset a Sequence in Oracle

create or replace

procedure reset_sequence(p_seq in varchar2)

is

    l_value number;

begin

-- Select the next value of the sequence

    execute immediate

    'select ' || p_seq ||

    '.nextval from dual' INTO l_value;

-- Set a negative increment for the sequence,

-- with value = the current value of the sequence

    execute immediate

    'alter sequence ' || p_seq ||

    ' increment by -' || l_value || ' minvalue 0';

-- Select once from the sequence, to

-- take its current value back to 0

    execute immediate

    'select ' || p_seq ||

    '.nextval from dual' INTO l_value;

-- Set the increment back to 1

    execute immediate

    'alter sequence ' || p_seq ||

    ' increment by 1 minvalue 0';

end;

/

source http://www.oratable.com/reset-sequence-in-oracle/