THE Database FOR Internet Computing Oracle
... 3... 4... 4... 5... 5... 7... 8... 9... 12... 13... 13... 16... 17... 18... 20... 22... 23... 23... 24... 25... 25... 26... 28... 31... 31... 31... 33... 34... 34... 34... 35... 36... 37... 39... 40... 43 1
... 44... 44... 45... 46... 48... 49... 50 2
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
4
PCTFREE PCTFREE PCTFREE PCTFREE + PCTUSED 100 5
6 INITIAL
( ) 1. UTLCHAIN.SQL CHAINED_ROWS SCOTT SQL> CONNECT SCOTT/TIGER SQL> @C: ORANT RDBMS80 ADMIN UTLCHAIN 7
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 99-02-01 SCOTT CHAIN_TEST AAAAn+AACAAABmmAAA 99-02-01 8
9
10
11
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
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
SQL> CREATE TABLESPACE DMS_DATA SQL> ALTER TABLESPACE DMS_DATA 14
15
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
50MB 40MB DEALLOCATE 10MB 10MB 40MB 10MB SQL> ALTER TABLE EMP DEALLOCATE UNUSED KEEP 100K; SQL> ALTER TABLE EMP DEALLOCATE UNUSED; 17
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
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 TBS01 1048576 TBS01 1048576 1MB 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
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 TBS01 0 1048576 512 EXT500K EXTENT_ID = 0TBS01 1048576 512 1MB 500KB MINIMUM EXTENT Oracle 8i R8.1.5 1MB 2MB 3MB 20
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
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 R8.1.5 22
23
24
25
26
27
28
29
30
31
32
OLTP INITIAL 100K SET TRANSACTION USE ROLLBACK SEGMENT 4 1 UNLIMITED EXTENTS V$ROLLSTAT 33
34
35
36
37
SQL>CREATE TEMPORARY TABLESPACE TEMP_FILE TEMPFILE 2 C: ORACLE ORADATA ORCL TEMPF1ORCL.DBF SIZE 50M 3 EXTENT MANAGEMENT LOCAL UNIFORM SIZE 2M; 38
39
40
41
42
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
44
45 1 2 3 A B LGWR
46
47
48
5MB 3 REDO RAID1 RAID0+1 V$SYSSTAT BSTAT/ESTAT 49
50