Oracle

最新推荐文章于 2024-04-30 22:34:40 发布

asran

最新推荐文章于 2024-04-30 22:34:40 发布

阅读量272

点赞数

分类专栏： Database/Oracle 文章标签： Oracle C C++ C# SQL Server

Database/Oracle 专栏收录该内容

1 篇文章

订阅专栏

SHOW USER(or SELECT USER FROM DUAL)

SELECT USERNAME, USER_ID, PASSWORD FROM DBA_USERS ORDER BY USERNAME

SELECT DEFAULT_TABLESPACE FROM USER_USERS

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

ALTER TABLE FOO MOVE TABLESPACE TS_MY_TABLESPACE

SELECT * FROM DBA_TABLESPACES

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.

VAR[IABLE] VARIABLE_NAME DATA_TYPE

PRINT VARIABLE_NAME

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

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

end;

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

create or replace package body asran.pkg_all

function add(p_a in int, p_b in int)

return int

v_sum int := 0;

begin

v_sum := p_a + p_b;

return v_sum;

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>