Oracle

1.

SHOW USER(or SELECT USER FROM DUAL)

/

 

2.

SELECT USERNAME, USER_ID, PASSWORD FROM DBA_USERS ORDER BY USERNAME

/

 

3.

SELECT DEFAULT_TABLESPACE FROM USER_USERS

/

 

4.

SELECT TABLE_NAME, TABLESPACE_NAME FROM USER_TABLES <----- only find the current user tables.

/

 

5.

ALTER TABLE FOO MOVE TABLESPACE TS_MY_TABLESPACE

/

 

6.

SELECT * FROM DBA_TABLESPACES

/

 

7.

CREATE TABLESPACE MY_TABLESPACE

LOGGING

DATAFILE 'C:\ORCL\TS\MY_DB_01.DBF' SIZE 32M AUTOEXTEND ON NEXT 32M MAXSIZE 128M,

'C:\ORCL\TS\MY_DB_02.DBF' SIZE 32M AUTOEXTEND ON NEXT 32M MAXSIZE 128M

EXTENT MANAGEMENT LOCAL

/

note: If contents exceed the tablespace max size, then [ORA-01654: unable to extend index ASRAN.SYS_C0024968 by 128 in tablespace] thrown.

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

SELECT * FROM V$DATAFILE;

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

Moving and Renaming Tablespaces:

   1.Take the tablespace offline using an ALTER TABLESPACE statement with the OFFLINE clause:

      ALTER TABLESPACE tbs_02 OFFLINE NORMAL;

   2.Copy the file from 'C:\ORACLEXE\APP\ORACLE\PRODUCT\10.2.0\SERVER\DATABASE\TS_DENGAS_01.DBF' to 'C:\oraclexe\oradata\TEST\TS_DENGAS_01.DBF' using your operating system commands.

   3.Rename the datafile using an ALTER TABLESPACE statement with the RENAME DATAFILE clause:

      ALTER TABLESPACE tbs_02

        RENAME DATAFILE 'C:\ORACLEXE\APP\ORACLE\PRODUCT\10.2.0\SERVER\DATABASE\TS_DENGAS_01.DBF'

        TO              'C:\oraclexe\oradata\TEST\TS_DENGAS_01.DBF';

   4.Bring the tablespace back online using an ALTER TABLESPACE statement with the ONLINE clause:

      ALTER TABLESPACE tbs_02 ONLINE;

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

Add/Drop datafiles:

alter tablespace ts_dengas

add datafile 'c:\oraclexe\oradata\test\ts_dengas_02.dbf' size 100k autoextend off

/

alter tablespace ts_dengas

drop datafile 'c:\oraclexe\oradata\test\ts_dengas_02.dbf'

/

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

Drop tablespace:

drop tablespace <tablespace_name> [including contents] [and datafiles] [cascade constraints]

/

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

Resize datafile:

alter database datafile '<datafile-path>'

resize 2M

/

note: If the target size is too small, then [ORA-03297: file contains used data beyond requested RESIZE value] thrown.

 

8.

VAR[IABLE] VARIABLE_NAME DATA_TYPE

PRINT VARIABLE_NAME

 

9.

SET SERVEROUTPUT ON ESCAPE OFF

DBMS_OUTPUT.PUT_LINE('MSG...');

 

10.

The values of all parts of the primary key must never be null.

1. First Normal Form means that the database doesn't contain any repeating attributes.

2. Violations of Second Normal Form occur when the table contains attributes that depend on a portion of the primary key.

3. Second Normal Form violations can exist only when you have a multi-column primary key.

4. Third Normal Form violations occur when a transitive dependency exists.

5. All attributes in entities (columns in tables) must be dependent upon the primary key or one of the candidate keys and not on other attributes.

 

11.

SELECT OBJECT_NAME, OWNER FROM DBA_OBJECTS WHERE OBJECT_TYPE='TABLE' AND OWNER = 'SYSTEM'

/

 

12.

SELECT TABLE_NAME.COLUMN_NAME FROM TABLE_NAME  -- NO ALIAS

/

 

13.

SELECT LEVEL, empno, manager_id, ename

FROM employee

START WITH empno = 1

CONNECT BY PRIOR empno = manager_id

ORDER BY LEVEL

/

 

14. is null VS. =

SELECT m.* FROM SA_MENU_ITEM m

WHERE m.DISPLAY_IND= :displayInd

    CONNECT BY PRIOR m.ID = m.PARENT_MENU_ID

    START WITH NVL(m.PARENT_MENU_ID, NULL) || ' ' = NVL(:rootMenuId, NULL) || ' ';

 

15. set nls lange & characterset:

set NLS_LANG=AMERICAN_AMERICA.al32UTF8

set nls_nchar_characterset=american_america.al16utf16

 

16. full setting of creating user:

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

-- Create the user

create user asran

  identified by asran

  default tablespace ASRAN_TS

  temporary tablespace TEMP

  profile DEFAULT

  password expire

  account lock

  quota 1m on asran_ts;

-- Grant/Revoke object privileges

grant select, insert, update, delete on ALL_ALL_TABLES to asran with grant option;

-- Grant/Revoke role privileges

grant connect to asran with admin option;

grant resource to asran with admin option;

-- Grant/Revoke system privileges

grant update any table to asran with admin option;

 

17. synonym:

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

create tablespace asran_ts

logging

datafile 'c:\usr\orcl\ts\asran_ts\asran_ts_01.dbf'

size 2M

autoextend off;

 

create user asran identified by asran

    default tablespace asran_ts

    temporary tablespace temp

    quota 32k on asran_ts;

grant connect to asran;

create user ada identified by ada

    default tablespace asran_ts

    temporary tablespace temp

    quota 16k on asran_ts;

alter user ada quota 1M on asran_ts;

 

create table ada.bl_profile(

    id int not null,

    name varchar2(200)

);

alter table ada.bl_profile add constraint ada_bl_profile_pk primary key(id);

 

create table ada.private_bl_profile(

    id int not null,

    name varchar(200)

) tablespace asran_ts;

alter table ada.private_bl_profile add constraint ada_private_bl_profile_pk primary key(id);

 

select * from ada.bl_profile;

grant select on ada.bl_profile to asran;

revoke select on ada.bl_profile from asran;

 

create synonym ada.bl_profile_alias for ada.bl_profile; -- create synonym in current schema

grant select on ada.bl_profile_alias to asran; -- grant priviledges of synonym to other users

 

sqlplus asran/asran

select * from ada.bl_profile; -- can't find table or view

select * from ada.bl_profile_alias; -- works well

 

note: all synonym and real object(table,view,...) share the same object, as a result, any grant/revoke on one of them should effect each other.

 

18. display oracle version:

SELECT * FROM V$VERSION;

 

19. customer defined object type(HashMap):

create or replace type <my_type_obj> as object(<property_name> <property_type>, ...)

/

usage:

    declare: <variable_name> <my_type_obj>;

    assign value: <variable_name> := <schema>.<my_type_obj>(value1, value2, ...);

    get value: <variable_name>.<property_name>

note:

    an object type is just as a key-value pair map.

 

20. customer defined table type(List<HashMap>):

create or replace type <my_type_tbl> as table of <my_type_object>

/

usage:

    declare and initialize: <my_tbl> <schema>.<my_type_tbl> := <schema>.<my_type_tbl>(); -- size 0

    extend the size of list: <my_tbl>.extend;

    add obj: <my_tbl>(index start at 1) := <my_type_obj>(value1, value2, ...);

    e.g.: <my_tbl>(<my_tbl>.count) := <my_type_obj>(value1, value2, ...)

 

21. cursor:

declare

    v_id asran.t_student.id%type;

    v_name asran.t_student.name%type;

    cursor v_cursor is select id, name from asran.t_student;

begin

    open v_cursor;

    loop

        fetch v_cursor into v_id, v_name;

        exit when v_cursor%notfound;

        dbms_output.put_line('id=' || v_id || ', name=' || v_name);

    end loop;

    close v_cursor;

end;

/

 

or use for...loop:

declare

    cursor v_cursor is select id, name from asran.t_student;

begin

    for c in v_cursor loop

        dbms_output.put_line('id=' || c.id || ', name=' || c.name);

    end loop;

end;

/

 

22. package:

--------------------define package

create or replace package asran.pkg_all

as

   function add(p_a in int, p_b in int) return int;

end;

/

--------------------define package body

create or replace package body asran.pkg_all

as

   function add(p_a in int, p_b in int)

   return int

   is

           v_sum int := 0;

   begin

           v_sum := p_a + p_b;

           return v_sum;

   end;

end;

/

--------------------using package

declare

   v_sum int;

begin

   v_sum := asran.pkg_all.add(2,4);

   dbms_output.put_line('sum:' || v_sum);

end;

/

 

23. trigger:

create or replace trigger tri_as_student_insert

after insert or delete on asran.as_student

    for each row

declclsare

    updating_key_fields exception;

    v_count int := 0;

begin

    if inserting then

        select count(1) into v_count from asran.as_student;

        dbms_output.put_line('The total records of asran.as_student is ' || v_count);

    end if;

    if updating then

        raise updating_key_fields;

    end if;

    if deleting then

        ....

    end if;

end;

/

alter trigger asran.tri_as_student_insert disable

/

alter trigger asran.tri_as_student_insert enable

/

 

24. arguments / return type declaration:

Never specify the size/(precision, scale) for char/varchar/number types in Oracle10g.

 

25. sequence:

create sequence [schema.]sequence_name increment by 1 start with 0 nomaxvalue nocycle nocache

/

note:

    1. can't use [create or replace ...] for create sequence, saying that we can't replace an existing sequence.

    2. can't alter sequence start with [ORA-02283: cannot alter starting sequence number].

 

26. pass parameter to plsql:

begin

dbms_output.put_line('&name is a good staff');

end;

/

note: [&****] is named parameter.

 

27. use function in index creation:

create index asran.t_student_idx on asran.t_student(upper(name))

/

 

28. retrieve the next day:

select next_day(sysdate, 'MON|Mondy,...') from dual

/

select add_months(sysdate, 2) from dual

/

 

29. dump sqlplus sreen output to a specified file:

sqlplus>define fil = 'c:\oral_spool.log'

sqlplus>spool &fil      <--- open file output stream

sqlplus>prompt *** Spool to 'c:\oral_spool.log'

sqlplus>....

sqlplus>spool off       <--- flush output buffer to file system

 

30. set SQLPATH environment variable:

set SQLPATH=c:\usr\orcl\sqlpath

sqlplus will find and save buffer file to this folder.(search in current folder, and then SQLPATH folder)

 

31. start sqlplus with silent mode and run a sql script file:

sqlplus -silent asran/asran @init_script.sql

 

32. start sqlplus without login:

sqlplus /nolog

 

33. clear buffer/screen

clear buffer

clear screen

 

33. read a script file to buffer:

get test.sql list|nolist

 

34. export/imprt db in Oracle10g:

select * from nls_database_parameters where parameter = 'NLS_CHARACTERSET';

set NLS_LANG=AMERICAN_AMERICA.AL32UTF8

exp cs2_afw_owner/cs2_afw_owner@zhcs2qa2 file=cs2_afw_owner.dmp

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

sqlplus sys/sys as sysdba

create tablespace cs2_ts

    logging

    datafile 'c:\usr\orcl\ts\cs2_ts\cs2_data_01.dbf' size 512m autoextend on next 128m maxsize 1024m

    extent

    management local

create user cs2_afw_owner identified by cs2_afw_owner default tablespace cs2_ts;

imp file=cs2_afw_owner.dump fromuser=cs2_afw_owner touser=cs2_afw_owner

>username: sys/sys as sysdba

 

 

35. find the company which have the same personnel amount:

select c.* from company c where c.personnel_amt in (

    select personnel_amt from company group by personnel_amt having count(1) > 1

);

 

36. utilites functions:

PL/SQL – String Manipulation:

    rtrim, ltrim, trim, concat, rpad, lpad, upper, lower, length, replace(), initcap(), instr(), substr()

Generating random numbers and strings in Oracle:

    Generating a random number (positive or negative)

        select dbms_random.random from dual;

    Generating a random number between 0 and 1

        select dbms_random.value from dual;

    Generating a random number from a range, between 1 to 1000

        select dbms_random.value(1,1000) num from dual;

    Generating a 12 digit random number

        select dbms_random.value(100000000000, 999999999999) num from dual

    Generating an upper case string of 20 characters

        select dbms_random.string('U', 20) str from dual;

    Generating a lower case string of 20 characters

        select dbms_random.string('L', 20) str from dual;

    Generating an alphanumeric string of 20 characters

        select dbms_random.string('A', 20) str from dual;

    Generating an upper case alphanumeric string of 20 characters

        select dbms_random.string('X', 20) str from dual;

    Generating a string of printable 20 characters

        select dbms_random.string('P', 20) str from dual;

    Example for calling the dbms_random package and setting the seed for generating the same set of random numbers in different sessions

        declare

            l_num    number;

        begin

            l_num := dbms_random.random;

            dbms_output.put_line(l_num);

            dbms_random.seed('amar testing 67890');

            l_num := dbms_random.random;

            dbms_output.put_line(l_num);

        end;

        /

 

37. export & import:

    exp userid=scott/tigger full=y file=myfile.dmp

    exp userid=scott/tigger owner=(scott,ali) file=exp_own.dmp

    exp userid=scott/tigger tables=(scott.emp,scott.sales) file=exp_tab.dmp

    imp scott/tigger ignore-y tables(emp,dept) full=n

    or imp scott/tigger ignore-y tables(T1:P1,T2:P2) full=n, if T1 is partioned table

 

38. SQLPLUS:

    COLUMN <col_name> FORMAT A<width>