Monday, April 20, 2009

Dynamic view V$PARAMETER

You can use the V$PARAMETER dynamic view to see the current setting of the different database parameters. In this example, we use the DESC SQL*Plus command to describe the V$PARAMETER view, and we then query the V$PARAMETER view to see the value of the control_file parameter setting:

SQL> desc v$parameter
Name Null? Type
----------------------------------------- -------- -------------
NUM NUMBER
NAME VARCHAR2(80)
TYPE NUMBER
VALUE VARCHAR2(512)
DISPLAY_VALUE VARCHAR2(512)
ISDEFAULT VARCHAR2(9)
ISSES_MODIFIABLE VARCHAR2(5)
ISSYS_MODIFIABLE VARCHAR2(9)
ISINSTANCE_MODIFIABLE VARCHAR2(5)
ISMODIFIED VARCHAR2(10)
ISADJUSTED VARCHAR2(5)
ISDEPRECATED VARCHAR2(5)
DESCRIPTION VARCHAR2(255)
UPDATE_COMMENT VARCHAR2(255)
HASH NUMBER
SQL> select name, value from v$parameter where name = 'control_files';
NAME VALUE
-------------------- -----------------------------------------------
control_files C:\ORACLE\ORADATA\BOOKTST\BOOKTST\CONTROL01.CTL, C:\ORACLE
\ORADATA\BOOKTST\BOOKTST\CONTROL02.CTL, C:\ORACLE\ORADATA\
BOOKTST\BOOKTST\CONTROL03.CTL

You may also use the shortcut “show parameter” command. For instance:

SQL> show parameter control_files;

The Parameter File at Startup Time

Oracle prefers the use of an SPFILE to a PFILE. When you startup your Oracle database, Oracle will scan the contents of your parameter directory ($ORACLE_HOME/database on Windows or the Linux directory name $ORACLE_HOME/dbs), searching in the following order:

* spfileSID.ora

* spfile.ora

* initSID.ora

* init.ora

If the directory contains none of the above, then the startup will fail.

Configuring Shared Server

We have recently configured shared server in some of our databases. Steps to follow to enable shared server are below.

The following configuration is on Enterprise Edition 9.2.0.5. The numbers used in the settings shown are only examples. We are running an OLTP system with about 2000 concurrent users with these parameters without any problem.

The parameters you need to set for the shared server and their default values are:

NAME VALUE
------------------------------ ----------
circuits 0
dispatchers
max_dispatchers 5
max_shared_servers 20
mts_circuits 0
mts_dispatchers
mts_max_dispatchers 5
shared_server_sessions 0
shared_servers 0

The ones starting with mts are for backward compatibility, setting circuits, dispatchers and max_dispatchers or mts_circuits, mts_dispatchers and mts_max_dispatchers is the same and is up to you.

If we go over these parameters one by one:

Circuits: This is a static parameter and it defaults to the sessions parameter when the shared server is used. You can leave it to the defalt value if your users are not using database links extensively. Because every session connected through the shared server adds to the circuits count for each distinct database link it opens. In our case we had sessions=3500 and circuits=3500 derived from that sessions parameter. But each user queried from two or three database links, so when the session count was about 1500 we exhausted circuits and began to get “error 18 creating virtual circuit” errors. So, you may need to set this parameter explicitly to a higher value if you are using database links.

Dispatchers: This parameter is said to be dynamic in Database Reference but if you have it as null in your database like the above settings you can not set it dynamically.

SQL> alter system set dispatchers='(protocol=tcp)(dispatchers=2)';
alter system set dispatchers='(protocol=tcp)(dispatchers=2)'
*
ERROR at line 1:
ORA-00105: dispatching mechanism not configured for network protocol
(ADDRESS=(PARTIAL=YES)(PROTOCOL=tcp))

You can not even set it with scope=spfile.

SQL> alter system set dispatchers='(protocol=tcp)(dispatchers=2)' scope=spfile;
alter system set dispatchers='(protocol=tcp)(dispatchers=2)' scope=spfile
*
ERROR at line 1:
ORA-02065: illegal option for ALTER SYSTEM

If you have it null on your system, the only way to set this is to create a pfile from the spfile and put it in there and then shutdown the database, create a new spfile and startup.

Shared_servers: This is a dynamic parameter and defaults to 1 when shared server is used.

Max_shared_servers: This is a static parameter and defaults to 20 or 2*shared_servers.

The steps we followed were:

SQL> alter system set circuits = 10000 scope=spfile;

System altered.

SQL> alter system set max_shared_servers=200 scope=spfile;

System altered.

SQL> alter system set shared_servers=50 scope=spfile;

System altered.

SQL> create pfile from spfile;

File created.

We have inserted the following line to the pfile created.

dispatchers='(protocol=tcp)(dispatchers=5)'

Then shutdown, create spfile from pfile and startup the instance.

Saturday, April 18, 2009

Kiến trúc ORACLE Admin 9i

1. Oracle Architectual

Oracle Server

A. Oracle Database (Storage)

A.1 PHYSICAL

a. Datafile: luu du lieu nguoi dung & luu du lieu Oracle

b. Redologfile

c. Controlfile

d. init parameter file: text & binary

e. password file

f. archived log file

g. alert$ORACLE_SID.log

i. trace file

j. dump

k. backup file

A.2 LOGICAL

Database > Tablespace > Segments ( Table, Index) > Extents > Oracle Block > OS block

B. Oracle Instance

B.1 Memory

a. SGA

- Shared-pool

_ Library Cache: shared sql hoac PLSQL

_ Dictionary Cache: mo ta objects, privs, ...

- Buffer-Cache: cache block duoc doc / ghi

- Redolog-Buffer: ghi moi thay doi len data-block

- Java-Pool: java

- Large-Pool: big objects

b. PGA: khi server process sinh ra, chua thong tin noi bo server process

B.2 Process

a. background process

SMON

PMON

CKPT

DBWRn

LGWR

ARCHn : ARCHIVED LOG mode

b. server process

- dedicated server: 1 server process = 1user process

- shared server: 1 server process = 20 - 30 user process

- General (Hybrid)

L2: Getting Started With Oracle Server

1. SQL* PLUS: interactive & manipulate Oracle Server

2. Oracle Universall Install (OUI): install & remove oracle software, create database (dbca)

3. dbca: create, delete, configure & manage template oracle databae

4. Oracle Enterprise Manager (OEM)

- single database (standalone): quan tri tung database rieng le, khong can cau hinh

- Oracle Management Server (OMS): - quan tri nhieu database tren 1 giao dien chung thong nhat, chi can login 1 lan co the quan tri nhieu database,

- phai cau hinh, phai cai Agent tren cac may Oracle can quan ly va phai cai Oracle Repository)

L3. Managing Oracle Instance

1. pfile / spfile: tham so cau hinh Oracle Instance

- pfile: text, duoc doc khi STARTUP

- spfile: binary, ALTER SYSTEM set shared_pool_size=200M scope=spfile|memory|both

- spfile$ORACLE_SID.ora > spfile.ora > init$ORACLE_SID.ora > init.ora

- Vi tri file tham so:

$ORACLE_HOME/dbs/

2. STARTUP

- STARTUP NOMOUNT: ( STARTED) (select status from v$instance)

doc file: init parameter file

ghi file: alert.log

failed: khong tim thay init parameter file hoac loi cau tham so

- STARTUP MOUNT: (MOUNTED)

doc file: controlfile file

ghi file: alert.log

failed: khong tim thay Controlfile hoac CF bi loi

- ALTER DATABASE OPEN: (OPEN)

doc file: datafile & redologfile

ghi file: alert.log

failed: khong tim thay datafile & redologfile hoac datafile, redologfile bi loi

Vi du:

cannot access file : test01.dbf

==> file bi loi ==> OPEN ==> OFFLINE test01.dbf ==> OPEN

alter database datafile '/u01/oradata/ora9i/test01.dbf' offline;

3. SHUTDOWN

A: no wait, but not safe (mat dien may chu) (RECOVER AUTOMATIC DATABASE)

I: cho ghi du lieu da comit & dong bo file

T: co Immediate va khong cho Transaction moi

N: co T va khong cho phep Session moi

4. Xem log

background_dump_dest: vi tri alert.log

background_dump_dest: vi tri luu trace file do background process sinh ra

user_dump_dest: luu vi tri trace file do user process & server process sinh ra

Lession 4: Creating Database

1. Plan

- Purpose: dev, prod, test

- Type: Data warehouse (DSS), OLTP (shared-pool, block_size) , Hybrid

- hardware, growth ... chi tiet ( 80 GB)

- Security: features (XML db, JVM, ...) ==> default accounts ==> default password

2. Co 2 cach tao database

- dbca: GUI, them - xoa - sua - template

- memory: shared-pool, buffer

- location & size

- tablespaces

- charactset: VN8VN3, UTF8

- archive-log mode

- dedicated | shared

- generates scripts ==> $ORACLE_BASE/admin/$ORACLE_SID/scripts/

ora9i.sh: moi cai file deu co ten database ==> doi ten db_name trong tat file

- dbcreate.sql

- dbpostcreation.sql

- create template

- CREATE DATABASE

a. log group, logfile, controlfile, datafile SYSTEM

b. catalog.sql: data dictionary views

catproc.sql: PLSQL packages

Lession 5: Data Dictionary

- Based-tables: ten cot rat kho hieu, thuoc ve SYS, chi duoc truy cap, thay doi boi chinh Oracle

sys.user$ ==> dba_users

- Data Dictionary View: thong tin mo ta ve Oracle, ten cot de hieu, thuong duoc truy cap DBAs

- catalog.sql ==> $ORACLE_HOME/rdbms/admin/

dbms*.sql: thu vien PL/SQL

utl*.sql: tien ich

cat*.sql: danh muc du lieu

prvt*.sql: thu vien lenh dang duoc ma hoa

- select tu based-tables

[dba | all | user]_tables

dba_: co tat ca object

all_: chi nhung object ma tai khoan truy cap

user_: chi co nhung objects ma do chinh tai khoan tao ra

SYS-SQL> SELECT COUNT(*) FROM ALL_tables;

HR-SQL> SELECT COUNT(*) FROM ALL_tables;

HR-SQL> SELECT COUNT(*) FROM user_tables;

- Dynamic Performance View:

- doc tin truc tu bo nho & controlfile

- V$

- phan anh thay doi cua Oracle mang tinh real-time

- v$sga

- v$versions

- v$sessions

- v$datafile

- v$tablespace

- Ky thuat sinh ma

vi du:

- SELECT 'DROP TABLE ' || table_name || ';'

FROM dba_tables

WHERE owner='HR';

- CONNECT hr/hr

SELECT 'DROP TABLE ' || table_name || ';'

FROM user_tables;

- exp hr/hr tables=(employees) file=employees.dmp

- SELECT 'exp hr/hr tables=(' || table_name || ') file=' || table_name || '.dmp'

FROM user_tables;

- SELECT 'exp system/oracle owner=(' || username || ') file=' || username || '.dmp data=y'

FROM dba_users;

Lession 6: Controlfile

- CF: luu thong tin cau truc vat ly database

- CREATE CONTROL FILE: so luong datafile, logfile , vi tri file ...

- ALTER DATABASE BACKUP CONTROLFILE TO TRACE; -- user_dump_dest

- Multiplexing controlfile: dat cac controlfile nam tren cac dia cung khac nhau, dat tren 2 Controller khac nhau

- v$controlfile

- show parameter control_

- v$parameter: SELECT value FROM v$parameter WHERE name='control_files';

- v$spparameter

- Neu co 3 control file khai bao trong control_files, khi STARTUP neu thieu 1 CF thi se khong STARTUP database

- CF duoc doc o mode Mounted

Lession 7: Redo log file

- Redolog file: chua moi thay doi cua Oracle ===> archived-log file ( 10 vi tri)

- Log Group: it nhat 2, moi log group phai co it nhat 1 log member ( file)

- Oracle tu van, 1 log group nen 3 logfile member

- Multiplexing redo log file

- ALTER SYSTEM add logfile MEMBER | GROUP

- v$log

- v$logfile

- ARCHIVED-LOG: lay dinh file archived-log default, $ORACLE_HOME/dbs/

1. SHUTDOWN IMMEDIATE

2. STARTUP MOUNT;

3. ALTER DATABASE ARCHIVELOG;

4. ALTER DATABASE OPEN;

5. ARCHIVE LOG LIST;

Lession 8: Tablespace & Datafile

1. Tong quan tablespace & datafile

- tablespace gom nhieu datafile

- datafile gom nhieu block

- SYSTEM tablespace: luu thong tin ve ban than Oracle

- NON-SYSTEM: DATA, TEMP, LOG

- Noi dung tablespace thi co 3 loai:

PERMANENT: du lieu

UNDO: luu anh truoc khi thay doi (500$)

salary:500$

update emp set salary=1000$ where name='DONG';

rollback;

TEMP:

- SELECT first_name, salary FROM employees ORDER BY first_name;

- SELECT DISTINCT isdn FROM mc_subscribers; -- 20.000.000

==> cat du lieu thanh manh

==> luu nhung manh chua sort xuong vung tam: tablespace TEMP

==> sau khi sort ==> tap hop ket qua sort & gui cho client

2. Phan loai phuong thuc quan ly Extent

- Database > Tablespace > Segment > Extents > Oracle Block > OS Block

- khi INSERT du lieu vao table ==> FULL ==> ALLOCATE EXTENT

1. Dictionary Managed Tablespace:

- hoi Data Ditionary View

- ALLOCATE extent: INSERT based-tables

- DE-ALLOCATE extent: DELETE | UPDATE vao based-tables

2. Locally Managed Tablespace

- Bitmap: 0 | 1

- CREATE TABLESPACE example datafile '/u01/oradata/ora9i/example01.dbf' size 100M;

3. UNDO TABLESPACE

- CREATE UNDO TABLESPACE

4. TEMPORARY TABLESPACE

- Create TEMPORARY TABLESPACE temp1 tempfile /u01

- Create TEMPORARY TABLESPACE temp2 tempfile /u02

create user u1 identified by u1 TEMPORARY TABLESPACE temp1;

create user u2 identified by u2;

- Co 2 cach chi dinh DEFAULT temporary tablespace;

- CREATE DATABASE

- ALTER DATABASE

5. CREATE | DROP | OFFLINE | READ-ONLY | RESIZE TABLESPACE

create tablespace data01 datafile '/u01/oradata/ora9i/data0101.dbf' size 5M

extent managent local;

Oracle9.2.0, SYSTEM locally ==> locally

6. ADD | RESIZE DATAFILE

- AUTOEXTEND ON

- cho insert khi file bi day

- maxextents=UNLIMITED

- quyen INSERT ==> INSERT den khi dia cung FULL thi thoi ==> D.O.S

- AUTOEXTEND ==> TEMPORARY TABLESPACE

- Sort tren Mem khong du thi se luu tam thoi du lieu sort tren TEMPFILE

- TEMPFILE dang la AUTOEXTEND

vi du:

- 2007, VMS-TT2, MOBICARD, LINUX

- MC_ACTION_AUDIT: rat lon, luu moi tac dong thay doi, xem tren du lieu thong ung dung FPT

- partition theo thang:

- 2005: SELECT ... MC_ACTION_AUDIT GROUP BY, ORDER BY ...

- TEMP: AUTOEXTEND

===> day toan bo mountpoint /u02 ==> tempfile02.dbf 25 GB

===> ket noi bi bao loi ==> khong ket noi thuc hien giao dich ==> 8h PM

===> DBA chay vao ==> telnet ==>

sqlplus "/ as sysdba" ==> trace file ==> Khong dang nhap duoc

SHUTDOWN ABORT

DROP TABLESPACE TEMP;

CREATE TEMP newtemp;

OPEN

rm /u02/tempfile02.dbf

su - root

updatedb

df -h

reboot

OS loi ==> 10h ==> lo mo cau hinh RAID ==> nang hon

==> bo tay ==> LAC VIET ==> recover block ==> 2 ngay

==> restore backup from tape ==> may chu ==> chuyen mobicard ==>

==> hop phong tinhoc voi lanh dao IT ==> tim nguyen ==> dua giai phap

==> thanh lap to SYSTEM & DATABASE

7. Oracle managed File (OMF)

- create tablespace data2009 datafile '/u01/oradata/ora9i/data01.dbf' size 100M;

- alter tablespace data2009 add datafile '/u01/oradata/ora9i/data02.dbf' size 100M;

- db_create_file_dest = '/u01/oradata/ora9i/';

create tablespace data2009 datafile size 100;

alter tablespace data20009 add datafile;

8. dba_tablespaces | v$tablespace | dba_free_space

dba_data_files | v$datafile

dba_temp_files | v$tempfile

Lesssion 9: Storage Structur & relationship

1. Types of Segment:

- Table

_ khong co Index thi thuong dung Full-Table-Scan

_ xem du lieu voi cot co Index: Scan b*tree Index ==> rowid ==> row data

- Index: b*tree index

- Partitioned Table : cac rows duoc phan nhom theo mot dac tinh nao do: thoi gian

- Partitioned Index: no se "dua" vao Table: drop table ==> drop index, truncate table ==> truncate index

- Cluster Table: dung de nhom hai hay nhieu bang co cung cot du lieu voi nhau ==> tang toc do cau truy JOIN giua cac bang do

- Index Organized Table (IOT): du lieu cua bang duoc luu tren b*tree index

- LOBs Segment: Large Object ==> CLOB (character: text file) & BLOB (Binary: movie, music)

- TEMPORARY: chi dung de luu tam thoi du lieu cho tac vu Sort khong du tren bo nho

- Bootstrap (Cache) segment: dung de luu Data Dictionary Cache (shared-pool)

2. Block size: kich thuoc chuan & phi chuan

- db_block_size: 8 K

- CREATE TABLESPACE data datafile '/u01/data01.dbf' blocksize 16K size 100M;

- db_multiple_block_read_count = 16 | 32

3. Co che quan ly segment

- AUTOMATIC SEGMENT MANAGEMENT

- create tablespace data2009 datafile '/u01/oradata/ora9i/data01.dbf' size 100M

extent management local

automatic segment management;

- MANUAL SEGMENT MANAGEMENT

- inittrans : so trans duoc phep truy cap vao block dong thoi

- maxtrans: so max trans duoc phep truy cap vao block dong thoi

- pctfree: qui dinh nguong cho thao tac INSERT & UPDATE

= 10% ==> de gianh 10% cho tac vu UPDATE

= 0% ==> UPDATE xay ra ==> migration row ==> row tren 2 block

- pctused: chi ra nguong cho tac vu DELETE ==> khi du lieu bi delete xuong duoi muc qui dinh boi pctused thi block se thoa dieu kien INSERT

==> nghia block duoc chuyen freelist

4. dba_segments: segment dang nam trong file

dba_extents:

Lession 10: UNDO tablespace

1. noi dung: anh truoc khi thay doi cua mot du lieu nao do

2. chuc nang:

a. rollback

b. shutdown abort ==> inconsistent ==> (instance) recovery ==> redolog file & undo tablespace

b. read consitency: chi du lieu commit moi duoc thay boi cac session #

3. co che quan ly UNDO

a. AUTO

b. MANUAL

4. Cau hinh

- UNDO_MANAGEMENT = AUTO | MANUAL

- UNDO_TABLESPACE =

5. tham so

- undo_retention: qui dinh so giay du lieu trong UNDO Tablespace duoc giu lai

6. dba_rollback_segs: xem thong rollback segment

v$rollname

v$undostat

Lession 11: Tables

1. segment types: table, partition table, cluster, iot

2. cau truc rows: header, data , column legth ==> migration rows

3. Table

a. CREATE TABLE a (x number)

b. Built-in: NUMBER, VARCHAR2, DATE

User-defined: TYPE nhan vien IS manv, tennv, phongban;

ROWID: vi tri vat ly 1 rows, 9i ==> truy cap theo ROWID la nhanh nhat

UROWID: phien ban 8i (restricted rowid)

c. ALTER TABLE : thay doi thong so luu tru cua Table (Storage Clause: initial extent, next

d. ALTER TABLE ... ADD COLUMN

e. ALTER TABLE DROP COLUMN ==> delete column-length ==> chiem thoi gian & IO

f. ALTER TABLE SET UNUSED ==> DROP COLUMN vao thoi diem it tai

g. TRUNCATE != DELETE

h. CREATE TABLE new_emp AS SELECT * FROM employees; (CTAS)

giong:

- cau truc cot & kieu du lieu cua cot

- no co the lay duoc du lieu cua bang goc

khong giong

- khong co Index, khong constraint, trigger ...

==> CREATE TABLE new_emp AS SELECT * FROM employees WHERE 1=2;

- DISCTINCT, MAX, MIN, COUNT, SUM, GROUP BY, ORDER BY

==> FPT

- hang dem xu ly chiem tai ==> TEMP ===>

==> MVIEW

CREATE MATERIALIZE VIEW v_dept_emp AS

select dept_name, count(first_name)

from big_department a, big_employees b

where a.dept_id = b.dept_it

ORDER BY dept_name;

Lession 12: Managing Index

1. Y nghia Index: muc luc mot cuon sach hang tram trang

2. Loai Index

1. B*Tree Index

- So luong rows khong qua nhieu: 20.000.000 rows

- gia tri cua cot tao Index khong xac dinh duoc, nhieu gia phan biet

vi du: isdn

imsi

cust_id

2. Bitmap Index

- so luong rows nhieu: 200.000.000 rows

- gia tri cua cot tao Index khong xac dinh duoc

vi du:

status = 0, 1, 2, 3, 4, 5 ... 10

color = 1, 2, ,3 , ..., 16

sex = male, female, gay

- cau lenh SQL ma dung nhieu dieu kien AND hoac OR

3. Function-based Index

- SELECT * FROM employees WHERE id = 100;

==> SELECT * FROM employees WHERE to_number(id) = 100;

3. Cau lenh lien quan Index

a. CREATE INDEX emp_id_idx ON emloyees(emp_id) PARALLEL 7 NOLOGGING;

b. DROP INDEX ...

c. ALTER INDEX ...: thay doi thuoc tinh luu tru cua Index

d. REBUILD INDEX: reoranized index ==> tang toc do duyet cay B*tree Index

e. ANALYZE INDEX ten_index VALIDATE STRUCTURE; ==> xac dinh Index co bi loi

Lession 13:

create table test (x varchar2(5), y number check > 0);

insert into test values('a',0);

insert into test ('a',1);

Lesssion 14: Managing User Profile

A. Quan tri mat khau

- dang nhap sai 3 lan: LOCK acccount ?

- qua 10 ngay khong doi mat khau: EXPIRED ?

CREATE PROFILE admin_users LIMIT

PASSWORD_FAILED_LOGINS 3

PASSWORD_LIFE_TIME 10;

- Neu user chua tao san:

create user dong identified by dong

default tablespace users

temporary tablespace temp

profile admin_users;

- Neu user da co san

alter user dong profile admin_users;

B. gioi han tai nguyen nguoi dung

- moi nguoi dung chi duoc ket noi 3 connection 1 luc

- nguoi dung nao ket noi ma khong lam gi trong vong 15' se LOGOUT

- Nguoi dung chi duoc phep ket noi trong vong 60'

CREATE PROFILE admin_users LIMIT

SESSIONS_PER_USER 3

IDLE_TIME 15

CPU_PER_USER 10

CONNECT_TIME 60

PASSWORD_FAILED_LOGINS 3

PASSWORD_LIFE_TIME 10;

- LIMIT_RESOURCE = TRUE

Lession 15: Managing User

1. CREATE USER

- quan tri theo user thuc: nghia la user do duoc tao boi Oracle

CREATE USER DONG IDENTIFIED BY oracle:

CREATE USER TRUNG IDENDIFIED BY oracle;

CREATE USER KHANG IDENDIFIED BY oracle;

CREATE USER HUNG IDENDIFIED BY oracle;

- quan tri theo user ung dung: nghia la user do duoc khai bao trong 1 bang ==> BIGAPPUSER

==> PAYMENT ==> PAYMENT_OWNER

==> Connection Pool: PAYMENT_OWNER, 50

admin_user

username

password

c. CREATE USER aaa IDENTIDED BY bbb

- khong chi dinh default TEMPORARY TABLESPACE

==> CREATE USER aaa IDENTIFIED BY bbb

TEMPORARY TABLESPACE temp;

- khong chi dinh default [PERMANENT] TABLESPACE

CREATE USER aaa IDENTIFIED BY bbb

TEMPORARY TABLESPACE temp

DEFAULT TABLESPACE data;

- cap QUOTA tren tablespace cho USER: alter user a quota 5m on example;

- UNLIMITED TABLESPACE: unlimited quota

2. ALTER USER a IDENTIFIED BY b; ==> khong an toan

SQL> password a ==> an toan hon

password ghi vao redolog o dang ma hoa ===> pha ma

3. ALTER USER a ACCOUNT LOCK | UNLOCK;

Les 4: Creating Database

Type of Database

- Data warehouse (DSS = Decission Support System): DOI SOAT,

- du lieu rat lon ==> 1 ti rows # 1 trieu rows

alter table small_tables add (status varchar2(1) default '1');

alter table big_tables add (status varchar2(1) default '1');

subscribers ( 5 M rows)

mc_subscribers (12M rows)

mc_subscribers.status = 0, 1 , 2 , 3 , 4, 5

update mc_subcribers set status='3';

==> redo-entry

- nhieu tac vu tong hop so lieu lon

- thuong co bao cao lon

============================

- 30/04 - 01/05/2009 khach hang se co khuynh huong mua san pham nao ?

- 30/04 khach nam 20 - 30 tuoi se mua qua - banh ?

===> thay boi ==> co thong tin co so ve ==> cang nhieu thong tin ==> cang tin

- Can co lich su du lieu ==> 10 nam mua hang cua khach hang ==> suy ra thoi quen mua hang

==> 3 nam nam gioi thich chocolate mau den

- Online Transaction Processing (OLTP) - IN (nap tien, kich hoat ...)

- nhieu tac vu - giao dich nho, nhanh

- thay doi du lieu nhieu

- it co nhung bao cao lon, tong hop

- General (Hybrid) - CSKH, MOBIFONE, MOBICARD

- DSS + OLTP

Less 5: Data Dictionary

- duoc luu trong tai khoan SYS

1. Based - table

sys.user$

2. Data Dictionary View

dba_users

dba_data_files

2.1 Dynamic Performance View: load len bo nho, luu tren do

v$session: select count(*) from v$session; ==> 15

2 session connected ==> 17

v$instance

v$database

v$datafile

3. DBA_TABLES | INDEXES | VIEWS | USERS | OBJECTS

ALL_TABLES | INDEXES | VIEWS | USERS | OBJECTS

USER_TABLES | INDEXES | VIEWS | USERS | OBJECTS

spfile$ORACLE_SID.ora

spfile.ora

init$ORACLE_SID.ora

init.ora

CN - 12 PM: backup database offline (no archived log)

Chieu thu 2: 3 PM, bi hu mat cai data01.dbf

===> phuc hoi lai du lieu cua ban backup luc 12 PM, CN

===> mat het toan bo du lieu tu luc 12 PM ==> thoi chieu thu 2

hu 1 block, datafile ==> phuc hoi toan bo bo database

TEST ==> DEVELOPMENT, PRODUCTION

=================

- tang I/O

- chiem space disk

- doi soat VMS cong ty (TT2): 1 ngay sinh 100 GB archived-log

BILLING:

toan bo thay doi doi duoc luu ra archived-log file

muon truy vet xem ai da thay doi du lieu trong bang customers vao thu 6 tuan vua

roi ???

LogMiner ==> xem noi dung archived-log file

select * from employees where id = 100;

select d.dept_name, e.first_name, e.salary

from employees e, department d

where e.dept_id = d.dept_id;

- cau lenh phai JOIN giua 2 hay nhieu bang

- cau lenh thuc thi nhieu lan

==> ton chi phi

================

cluster table

create temporary table t1 (x number);

delete from emloyees;

commit;

==> LogMiner

==> Flashback