Oracleの領域管理～トラブル防止のテクニック～

Transcription

1 THE Database FOR Internet Computing Oracle

2

3

4 Oracle8i R8.1.5 Oracle8 Enterprise Edition R8.0.5 for Windows NT Oracle8 Enterprise Edition R8.0.5 for Solaris Oracle8 Oracle7 Oracle8i Oracle Oracle7 Oracle8 3

5 4

6 PCTFREE PCTFREE PCTFREE PCTFREE + PCTUSED 100 5

7 6 INITIAL

8 ( ) 1. UTLCHAIN.SQL CHAINED_ROWS SCOTT SQL> CONNECT SCOTT/TIGER ORANT RDBMS80 ADMIN UTLCHAIN 7

9 2. LIST CHAINED ROWS ANALYZE CHAINED_ROWS SCOTT CHAIN_TEST SQL> ANALYZE TABLE CHAIN_TEST LIST CHAINED ROWS; 3. SQL SQL> SELECT * FROM CHAINED_ROWS 2 WHERE TABLE_NAME = 'CHAIN_TEST'; OWNER_NAME TABLE_NAME CLUSTER_NAME PARTITION_NAME HEAD_ROWID ANALYZE_ SCOTT CHAIN_TEST AAAAn+AACAAABmkAAA SCOTT CHAIN_TEST AAAAn+AACAAABmmAAA

10 9

11 10

12 11

13 TABLESPACE I/O PCTFREE PCTFREE PCTFREE PCTFREE PCTFREE + PCTUSED 100 STORAGE DEFAULT STORAGE INITIAL Oracle7 R7.3 MAXEXTENTS UNLIMITED PCTINCREASE 1 DB_BLOACK_SIZE OLTP 2 4KB DSS 4 16KB USING INDEX TABLESPACE STORAGE PCTFREE 12

14 SQL> ALTER TABLESPACE USER_DATA 2 ADD DATAFILE 'C: ORANT DATABASE USER2ORCL.ORA' SIZE 10M; SQL> ALTER DATABASE 2 DATAFILE 'C: ORANT DATABASE USR1ORCL.ORA' 3 RESIZE 10M; 13

15 SQL> CREATE TABLESPACE DMS_DATA SQL> ALTER TABLESPACE DMS_DATA 14

16 15

17 A 1 20MB A 1 20MB A 1 20MB B 1 15MB 15MB B COALESCE B 2 15MB 15MB 30MB A 2 20MB A 2 20MB A 2 20MB SQL> ALTER TABLESPACE USER_DATA COALESCE; 16

18 50MB 40MB DEALLOCATE 10MB 10MB 40MB 10MB SQL> ALTER TABLE EMP DEALLOCATE UNUSED KEEP 100K; SQL> ALTER TABLE EMP DEALLOCATE UNUSED; 17

19 Oracle8 MINIMUM EXTENT CREATE/ALTER TABLESPACE Oracle DBA_TABLESPACES USER_TABLESPACES MIN_EXTLEN 2.2 DROP TRUNCATE MINIMUM EXTENT MINIMUM EXTENT 2.3 MINIMUM EXTENT MINIMUM EXTENT 1M 0.8MB 1.5MB 1MB 2MB 2MB 0.8MB 2MB 1.6MB 1MB 1MB 1MB 1.4MB 1MB 1MB 2MB 1.3MB 1MB 1MB 1MB 2MB MINIMUM EXTENT 1M 500KB 1.5MB 1MB 2MB 18

20 MINIMUM EXTENT TBS01 MINIMUM EXTENT 1MB SQL> CONNECT SYSTEM/MANAGER SQL> CREATE TABLESPACE TBS01 2 DATAFILE 'C: ORANT DATABASE TBS01.ORA' SIZE 50M 3 AUTOEXTEND ON NEXT 10M MAXSIZE 200M 4 MINIMUM EXTENT 1M 5 DEFAULT STORAGE ( INITIAL 1M NEXT 1M PCTINCREASE 0); DBA_TABLESPACES MINIMUM EXTENT TBS01 MINIMUM EXTENT SQL> SELECT TABLESPACE_NAME, MIN_EXTLEN 2 FROM DBA_TABLESPACES 3 WHERE TABLESPACE_NAME = 'TBS01'; TABLESPACE_NAME MIN_EXTLEN TBS TBS MB MINIMUM EXTENT TBS01 SCOTT 500KB EXT500K SQL> CONNECT SCOTT/TIGER SQL> CREATE TABLE EXT500K (COL1 CHAR(10)) 2 TABLESPACE TBS01 3 STORAGE ( INITIAL 500K NEXT 500K PCTINCREASE 0); 19

21 DBA_EXTENTS EXT500K SQL> CONNECT SYSTEM/MANAGER SQL> SELECT OWNER, SEGMENT_NAME, TABLESPACE_NAME, 2 EXTENT_ID, BYTES, BLOCKS 3 FROM DBA_EXTENTS WHERE SEGMENT_NAME = 'EXT500K'; OWNER SEGMENT_NAME TABLESPACE_NAME EXTENT_ID BYTES BLOCKS SCOTT EXT500K TBS EXT500K EXTENT_ID = 0TBS MB 500KB MINIMUM EXTENT Oracle 8i R MB 2MB 3MB 20

22 CREATE TABLESPACE EXTENT MANAGEMENT LOCAL UNIFORM SIZE AUTOALLOCATE UNIFORM SIZE AUTOALLOCATE Oracle UNIFORM SIZE AUTOALLOCATE UNIFORM SIZE 1MB AUTOALLOCATE 64KB AUTOALLOCATE UNIFORM SIZE CREATE TABLESPACE DATAFILE SIZE [K ] EXTENT MANAGEMENT LOCAL AUTOALLOCATE CREATE TABLESPACE DATAFILE SIZE [K M] EXTENT MANAGEMENT LOCAL UNIFORM SIZE K M SQL> CREATE TABLESPACE LOCAL_AUTO 2 DATAFILE 'E: ORACLE ORADATA ORCL LOCAL_A01.DBF' 3 SIZE 5M EXTENT MANAGEMENT LOCAL AUTOALLOCATE SQL> CREATE TABLESPACE LOCAL_UNI 2 DATAFILE 'E: ORACLE ORADATA ORCL LOCAL_U01.DBF' 3 SIZE 5M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 500K; SQL>SELECT TABLESPACE_NAME, CONTENTS, LOGGING, 2 EXTENT_MANAGEMENT FROM DBA_TABLESPACES 3 WHERE TABLESPACE_NAME LIKE 'LOCAL_%'; TABLESPACE_NAME CONTENTS LOGGING EXTENT_MAN LOCAL_UNI PERMANENT LOGGING LOCAL LOCAL_AUTO PERMANENT LOGGING LOCAL 21

23 ALTER TABLESPACE ADD DATAFILE R7.2 ALTER DATABASE DATAFILE RESIZE R7.2 ALTER DATABASE DATAFILE AUTOEXTEND ON 2GB TABLESPACE COALESCE DEALLOCATE UNUSED SPACE MINIMUM EXTENT Oracle8i R

24 23

25 24

26 25

27 26

28 27

29 28

30 29

31 30

32 31

33 32

34 OLTP INITIAL 100K SET TRANSACTION USE ROLLBACK SEGMENT 4 1 UNLIMITED EXTENTS V$ROLLSTAT 33

35 34

36 35

37 36

38 37

39 SQL>CREATE TEMPORARY TABLESPACE TEMP_FILE TEMPFILE 2 C: ORACLE ORADATA ORCL TEMPF1ORCL.DBF SIZE 50M 3 EXTENT MANAGEMENT LOCAL UNIFORM SIZE 2M; 38

40 39

41 40

42 41

43 42

44 SORT_AREA_SIZE SQL HASH JOIN Oracle7 R7.3 SGA Oracle8i R8.1.5 INITIAL NEXT DB_BLOCK_SIZE n SORT_AREA_SIZE n 1 10MB NEXT PCTINCREASE 0 PCTINCREASE 0 2 BSTAT/ESTAT V$SYSSTAT V$SORT_SEGMENT 43

45 44

46 A B LGWR

47 46

48 47

49 48

50 5MB 3 REDO RAID1 RAID0+1 V$SYSSTAT BSTAT/ESTAT 49

51 50