ebook10-5

Size: px

Start display at page:

Download "ebook10-5"

稻封
9 years ago
Views:

1 Oracle 7.x RDBMS 5 Oracle S Y S S Y S T E M O r a c l e 5.1 O r a c l e R D B M S O r a c l e O r a c l e 5.2 SYS SYSTEM S Y S S Y S T E M O r a c l e S Y S V $ D B A C O N N E C T R E S O U R C E S Y S O r a c l e S Y S S Y S S Y S SYS S Y S S Y C H A N G E _ O N _ I N S TA L L S Y S T E M DBA S Y S T E S Y S T E M SYSTEM M A N A G E R S Y S S Y S T E M

2 34 Oracle 7. x RDBMS O r a c l e ALTER USER IDENTIFIED BY VALUES ' ' ' ' O r a c l e A LTER USER IDENTIFIED BY ' ' O r a c l e 5.3 R D B M S system database object user database object R D B M S R O W I D Oracle 1. initialization parameter file i n i t. o r a R D B M S A S C I I U N I X $ O R A C L E _ H O M E / d b s N T $ O R A C L E _ H O M E / d a t a b a s e i n i t S I D. o r a S I D U N I X i n i t. o r a O r a c l e S I D $ O R A C L E _ S I D O r a c l e i n i t. o r a i n i t. o r a I F I L E U N I X $ O R A C L E _ H O M E / d b s / i n i t. o r a Oracle Worldwide Customer Support

o r a R D B M S A S C I I U N I X $ O R A C L E _ H O M E / d b s N T $ O R A C L E _ H O M E / d a t a b a s e i n i t S I D.

3 5 Oracle 35 i n i t. o r a i n i t. o r a i n i t. o r a Oracle RDBMS i n i t. o r a $ O R A C L E _ H O M E / d b s i n i t. o r a V $ PA R A M E T E R i n i t. o r a V $ PA R A M E T E R init.ora 5-1 i n i t. o r a Oracle Server Reference manual O r a c l e 5-1 i n i t. o r a a u d i t _ t r a i l b a c k g r o u n d _ d u m p _ d e s t c o m p a t i b l e c o n t r o l _ f i l e s d b _ b l o c k _ b u ff e r s d b _ b l o c k _ s i z e d b _ f i l e s d b _ n a m e d b _ f i l e _ m u l t i b l o c k _ r e a d _ c o u n t d m l _ l o c k s l o g _ a r c h i v e _ d e s t l o g _ a r c h i v e _ s t a r t l o g _ b u ff e r l o g _ c h e c k p o i n t _ i n t e r v a l m a x _ d u m p _ f i l e _ s i z e p r o c e s s e s r e m o t e _ l o g i n _ p a s s w o r d f i l e r o l l b a c k _ s e g m e n t s s e q u e n c e _ c a c h e _ e n t r i e s s h a r e d _ p o o l _ s i z e s n a p s h o t _ r e f r e s h _ p r o c e s s e s t i m e d _ s t a t i s t i c s u s e r _ d u m p _ d e s t O r a c l e a l e r t. l o g d b _ b l o c k _ b u ff e r s d b _ b l o c k _ s i z e = Oracle C R E AT E D ATABASE I / O D M L A R C H O r a c l e U N I X N O N E S H A R E D E X C L U S I V E S N P S N P D B M S _ J O B s q l _ t r a c e

o r a a u d i t _ t r a i l b a c k g r o u n d _ d u m p _ d e s t c o m p a t i b l e c o n t r o l _ f i l e s d b _ b l o c k _ b u ff e r s d b _ b l o c k _ s i z e d b _ f i l e s d b _ n a m

4 36 Oracle 7. x RDBMS 2. i n i t. o r a C O N T R O L _ F I L E S C R E ATE DATA B A S E O r a c l e C R E ATE DATA B A S E C O N T R O L _ F I L E S C O N T R O L _ F I L E S 1 2 A LTER DATABASE BACKUP CONTROLFILE TO TRACE S Q L * P l u s U S E R _ D U M P _ D E S T 3 C R E ATE CONTROLFILE 4 SHUTDOWN NORMAL O r a c l e 5 S TA RTUP NOMOUNT C R E ATE CONTROLFILE 6 A LTER DATABASE OPEN CHARACTERSET

O L _ F I L E S C O N T R O L _ F I L E S 1 2 A LTER DATABASE BACKUP CONTROLFILE TO TRACE S Q L * P

5 5 Oracle M A X L O G F I L E S M A X L O G M E M B E R S M A X D ATA F I L E S M A X I N S TA N C E S M A X L O G H I S TO RY REUSE DATABASE < > SET DATABASE < > V$CONTROLFILE O r a c l e A LTER DATABASE BACKUP CONTROLFILE TO T R A C E 3. L G W R O r a c l e 5-1 LGWR L G W R L G W R ( A R C H ) L G W R A R C H A R C H A R C a l e r t. l o g L G W R

V$CONTROLFILE O r a c l e A LTER DATABASE BACKUP CONTROLFILE TO T R A C E 3.

6 38 Oracle 7. x RDBMS L G W R a l e r t. l o g R A I D 1 L G W R V $ L O G V$LOGFILE 4. O r a c l e s i d A L RT. l o g sid i n i t. o r a B A C K G R O U N D _ D U M P _ D E S T B A C K G R O U N D _ D U M P _ D E S T U S E R _ D U M P _ D E S T U S E R _ D U M P _ D E S T B A C K G R O U N D _ D U M P _ D E S T s i d P R O C. t r c s i d P R O C D B W R L G W R S M O N P M O N o r a. t r c S Q L A LTER DATA B A S E BACKUP CONTROLFILE TO TRACE S Q L S Q L * P l u s A LTER SESSION SET SQL_TRACE=TRUE i n i t. o r a S Q L _ T R A C E T R U E i n i t. o r a S Q L _ T R A C E T R U E S Q L V $ PARAMETER B A C K G R O U N D _ D U M P _ D E S T U S E R _ D I M P _ D E S T 5. ROWID O r a c l e Oracle RDBMS R O W I D R O W I D

s i d P R O C. t r c s i d P R O C D B W R L G W R S M O N P M O N o r a.

7 5 Oracle 39 B B B B B B B B 16 F F F F R R R R R O W I D R O W I D C D B A _ D ATA_FILES R D W I D O r a c l e 7 O r a c l e 8 R O W I D / R O W I D R O W I D R O W I D p s e u d o c o l u m n R O W I D R O W I D R O W I S Q L R O W I D SQL 6. Oracle O r a c l e O r a c l e O r a c l e U N I X 8 K B db_block_size KB O r a c l e O r a c l e I / O RDBMS O r a c l e Oracle O r a c l e

l u m n R O W I D R O W I D R O W I S Q L R O W I D SQL 6.

8 40 Oracle 7. x RDBMS P C T F R E E P C T U S E D P C T F R E E 30 % P C T F R E E 70 % 70 O r a c l e 30 % P C T U S E D P C T F R E E P C T U S E D P C T F R E E P C T R S E D 100 % 21 O r a c l e I / O Online Transaction Processing O LT P 4 K B 8 K B I / O Decision Support System D S S I / O User database objects Oracle RDBMS O r a c l e 1. Oracle O r a c l e Oracle RDBMS S Q L C R E ATE TA B L E S PA C E A LTER TA B L E S PA C E C R E AT E 10 M B 1 0 M B Oracle 7.3 O r a c l e

P C T U S E D P C T F R E E P C T R S E D 100 % 21 O r a c l e I / O Online Transaction Processing O LT P 4 K B 8 K B I /

9 5 Oracle 41 D B A _ D ATA _ F I L E S V $ D ATAFILE : 2. e x t e n t O r a c l e O r a c l e Oracle 7.3 O r a c l e C R E AT E 21 D B A _ E X T E N T S 3. t a b l e s p a c e I / O D B A _ TA B L E S PA C E S database segment t a b l e O r a c l e 5-3

10 42 Oracle 7. x RDBMS 5-3 Oracle 7.x C H A R 255 VA R C H A R 2 K B VA R C H A R 2 2 K B L O N G 2 G B N U M B E R ~ D AT E R AW LONG RAW 2 G B R O W I D 6 A N S I O r a c l e VA R C H A R 2 VA R C H A R O r a c l e VA R C H A R 2 VA R C H A R A N S I Oracle D B A _ TA B L E S D B A _ TA B _ C O L U M N S i n d e x e R O W I D O r a c l e R O W I D Oracle RDBMS B *- Tr e e B - C R E ATE INDEX B *- Tr e e B *- Tr e e B * - Tr e e E l m a s r i N a v a t h e Fundamentals of Database S y s t e m s c l u s t e r D M L b i t m a p 1 0 B *- Tr e e

R C H A R A N S I Oracle D B A _ TA B L E S D B A _ TA B _ C O L U M N S 5.4.

11 5 Oracle 43 B * - Tr e e DBA_INDEXES D B A _ I N D _ C O L U M N S rollback segment r o l l b a c k segment extension 21 DBA_ROLLBACK_SEGS table cluster I / O hash cluster O r a c l e I / O

00001000000000000100 01000100000100000000 5.4.

12 44 Oracle 7. x RDBMS 5.5 Oracle 2 1 data dictionary Oracle RDBMS R D B M S X $ (V$) RDBMS X $ O r a c l e R D B M S X $ Oracle RDBMS X $ X $ X $ X $ S Q L * P l u s V $ S G A S TAT 1 S Y S X $ V $ S Q L * P l u s P L A N _ TA B L E $ O R A C L E _ H O M E / r d b m s / a d m i n / U T L X P L A N. s q l 2 S Q L * P l u s SET AUTOTRACE ON 3 W H E R E SELECT * FROM V$sgastat WHERE 0=1; S Q L X $ data dictionary table S Y S S Q L. B S Q $ t a b $ s e g $ c o n s $ S Q L. B S Q $ O R A C L E _ H O M E / d b s S Q L. B S Q Oracle RDBMS

L * P l u s V $ S G A S TAT 1 S Y S X $ V $ S Q L * P l u s P L A N _ TA B L E $ O R A C L E _ H O M E / r d b m s / a d m i n / U T L X

13 5 Oracle V $ dynamic performance V $ v i e w O r a c l e X $ V$ Oracle Reference Manual V $ S Y S V$ V $ V$ V $ X $ D B A _ A L L _ U S E R _ D B A _ DBA _TA B L E S A L L _ U S E R _ S Q L HR S Q L THE DBA_VIEWS N E X T VA L C U R RVA L

5.4 X $ D B A _ A L L _ U S E R _ D B A _ DBA _TA B L E S A L L _ U

14 46 Oracle 7. x RDBMS E M P _ S E Q SELECT EMP_SEQ.NEXTVAL FROM DUAL 1 SELECT EMP_SEQ. CURRVAL FROM D U A L C U R RVA L N E X T VAL DBA_SEQUENCES t r i g g e r O r a c l e DBA_TRIGGERS s y n o n y m O r a c l e p r i v a t e p u b l i c P U B L I C S Q L S Q SELECT * FROM EMP_SALARY O r a c l e E M P _ S A L A RY 1 E M P _ S A L A RY 2 O r a c l e E M P _ S A L A RY 3 E M P _ S A L A RY 4 E M P _ S A L A RY 5 O r a c l e O R A table or view does not e x i s t D B A _ S Y N O N Y M S P U B L I C database link O r a c l e 40 D B A _ D B _ L I N K S U s e r I D D B A _ D B _ L I N K S

RY 2 O r a c l e E M P _ S A L A RY 3 E M P _ S A L A RY 4 E M P _ S A L A RY 5 O r a c l e O R A - 00942 table or view does not e x i s t D B A _ S Y N

15 5 Oracle SQL * Plus D B A _ O B J E C T S D B A _ S O U R C E

R D B M S O R D B M S R D B M S / O R D B M S R D B M S O R D B M S 4 O R D B M S R D B M 3. ORACLE Server O R A C L E U N I X Windows NT w w

R D B M S O R D B M S R D B M S / O R D B M S R D B M S O R D B M S 4 O R D B M S R D B M 3. ORACLE Server O R A C L E U N I X Windows NT w w 1 1.1 D B M S To w e r C D 1. 1 968 I B M I M S 2 0 70 Cullinet Software I D M S I M S C O D A S Y L 1971 I D M S containing hierarchy I M S I D M S I M S I B M I M S I D M S 2 2. 18 R D B M S O R D B