7

OracleOracleOracle

DBA:DBA

7.1 Oracle

(ONLINE)(OFFLINE)SYSTEM

Oracle(DICTIONARY)(LOCAL) 0racle9i R2:

7.2 SYSTEMSYSTEMOracle DBA

1. CREATE TABLESPACECREATE TABLESPACE: CREATE TABLESPACE tablespace_name DATAFILE [path]SIZE n[k| M] [[path]SIZE n[k|M]...] [ONLINE l OFFLINE] [EXTENT MANAGEMENT DICTIONARY | LOCAL [AUTOALLOCATE |UNIFORM[SIZE n[K l M]]]]

(1)tablespace name: (2)DATAFILE (3)ONLINE:OFFLINEONLINE (4)EXTENT MANAGEMENT:oracle9i:AUTOALLOCATEUNIFORMSIZESIZE1MB

2. 7-1 testDtest01.dbf200MB SQL>CREATE TABLESPACE test DATAFILED:test01.dbfSIZE 200M EXTENT MANAGEMENT LOCAL EXTENT MANAGEMENT LOCAL

7.2edu1.5GB2edu01.dbfedu02.dbfDEXXWOracle SQL>CREATE TABLESPACE edu DATAFILE D:\xxw\edu01.dbf SIZE 1000M E:\xxw\edu02.dbf SIZE 500M EXTENT MANAGEMENT LOCAL AUTOALLOCATE

7-3 index_tbs1index01.dbf128KB SQL>CREATE TABLESPACE index_tbs DATAFILEindex01.Dbf SIZE 100M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K

7.4 tmp_tbsSQL>CREATE TEMPORARY TABLESPACE tmp_tbs TEMPFILE tmp.dbf SIZE 300M:SQL>ALTER DATABASE DEFAULT TEMPORARY TABLESPACE tmp_tbs

7.5 UNDOund001SQL>CREATE UNDO TABLESPACE und001 DATAFILE E:\Oracle\oradata\Student\und001.dbfSIZE 50M EXTENT MANAGEMENT LOCAL AUTOALLOCATE UNDO()DML(INSERTUPDATEDELETE)UNDOUNDOUNDO

OracleUNDOUNDOUNDO_TABLESPACEUNDOUNDO: SQL>ALTER SYSTEM SET UNDO_TABLESPACE=und00 1

7.6 userdata100MBOracle9iEXTENT MANAGEMENT DICTIONARYSQL>CREATE TABLESPACE userdata DATAFILEE:\Oracle\oradata\USerdata01.dbfSIZE 100M EXTENT MANAGEMENT DICTIONARYOracle 9.0.1.1.1

3. .(1) SQL>SELECT tablespace_name FROM dba_tablespaces SQL>SELECT name FROM v$tablespace

(2)edudba_data__files SQL>SELECT file name FROM dba_data_files WHERE tablespace_name=EDU; dba_data_filesdba_tempfiles: SQL>SELECT file_name FROM dba_temp_files

(3) SQL>SELECT tablespace_nameextent_management FROM dba_tablespaces (4)SYSTEMUNIFORMSQL>SELECT tablespace_nameallocation_type FROM dba_tablespaces WHERE extent_management=LOCAL

7.3

7.3.1 1. ONLINEOFFLINESQL>ALTER TABLESPACE edu OFFLINE :SQL>ALTER TABLESPACE edu ONLINE

: SQL>ALTER DATABASE DATAFILE D:\xxw\edu01.dbf OFFLINE : SQL>ALTER DATABASE DATAFILE D:\xxw\edu01.dbf ONLINE;

2. SELECT SQL>ALTER TABLESPACE edu READ ONLY SQL>ALTER TABLESPACE edu READ WRITE ;

3. statusREAD ONLY()ONLINE()OFFLINE() SQL>SELECT tablespace_namestatus FROM dba_tablespaces:

7.3.2 DBA dba_data-files: SQL>SELECT tablespace_namesum(bytes)10241024 total MB FROM dba_data_files GROUP BY tablespace_name sum(bytes)10241024MB

dba_free_space: SQL>SELECT tablespace_namesum(bytes)10241024 free MB FROM dba_free_space GROUP BY tablespace_name

DBAdba_data_illesdba_free_space: SQL>SELECT b.tablespace_nametablespace sum(b.bytes)10241024 total_MB sum(a.bytes)10241024 free_MB round(sum(a-bytes)sum(b.bytes)*i001) freetotal FROM dba__free__space adba__data__files b WHERE a.file_id=b.file_id GROUP BY b.tablespace name

7.3.3 7.1USERS0.2 (1) (2)

1. (1)DBA dba_data_files: SQL>SELECT file_nameautoextensible FROM dba_data_files WHERE tablespace_name=USERS

autoextensibleYESNOALTER DATABASE DATAFILE:SQL>ALTER DATABASE DATAFILE E:\Oracle\oradata\student\users01.dbf AUTOEXTEND ON NEXT 10M MAXSIZE 500M

AUTOEXTEND ONusers01.dbfNEXTMAXSIZEUNLIMITED455MB10MB500MBALTER DATABASE DATAFILEAUTOEXTEND OFF : SQL>CREATE TABLESPACE userdata02 DATAFILE userdata02.dbf SIZE 5M AUTOEXTEND ON NEXT 2M

(2)users01.dbf455MB200MB700MB SQL>ALTER DATABASE DATAFILE-E:\Oracle\oradata\student\users01.dbf RESIZE 700M RESIZE

2. USERS200MBE200MBI: SQL>ALTER TABLESPACE users ADD DATAFILE F:\Student\users02.dbf SIZE 200M

200MB: SQL>ALTER TABLESPACE users ADD DATAFILE f:\ Student\USers02.dbfSIZE 200M AUTOEXTEND ON NEXT 1 0M ()IO SYSTEM

SYSTEM 1.SYSTEM users01.dbf (1)SQL>ALTER TABLESPACE users OFFLINE

(2)SQL>HOST COPY E:\Oracle\oradata\student\users01dbf F:\student\users01.dbf

(3)ALTER TABLESPACE RENAME DATAFILESQL>ALTER TABLESPACE users RENAME DATAFILE E:\Oracle\0radata\student\users01.dbf TO tF.\student\users01.dbf(4) SQL>ALTER TABLESPACE users ONLINE(5) users01.dbfEF

2.SYSTEM SYSTEMSYSTEM system01.dbf (1)SQL>SHUTDOWN IMMEDIATE

(2)SQL>HOST COPY E:\Oracle\oradata\student\system01dbf F:\student\system01.dbf

(3)(M0uNT)SQL>STARTUP MOUNT(4)ALTER DATABASE RENAME FILESQL>ALTER DATABASE RENAME FILE E.\0racle\oradata\student\system01.dbf TO F.\student\system01.dbf

(5)SQL>ALTER DATABASE OPEN

7.4DBADROPTABLESPACE : DROP TABLESPACE tablespace_name [INCLUDING CONTENTS [AND DATAFILES] [CASCADE CONSTRAINTS]] (1)INCLUDING CONTENTS:

(2)AND DATAFILES: (3)CASCADE CONSTRAINTS: tt:SQL>DROP TABLESPACE tt DROP TABLESPACE tt

ERRORl: ORA01549:INCLUDING CONTENTStt:SQL>DROP TABLESPACE tt INCLUDING CONTENTS AND DATAFILES ;

DBA

1.( ) A.v$controlfile B.v$datafile C.v$logfile Dv$tablespace

2( ) Adbadatafiles Bdbatablespaces Cv$tablespace Ddba free space3( ) A B c D

4( ) Auser Btooltbs C temptbs Dsystem5( ) Ausers Bsystem Cindex Dexam6( ) ARENAME DATA FILE BALTER TABLESPACE CNEWNAME DALTER DATABASE

7( )Oracle A=>=>=>=> B=>=>=>=> C=>=>=>=> D=>=>=>=>8( ) AALTER DATABASE DATAFILE OFFLINE DROP BALTER TABLESPACE OFFLINE IMMEDIATE CDROP TABLESPACE CASCADE CONSTRAINTS DDROP TABLESPACEINCLUDING CONTENTS

9( ) Adbatablespaces Bdba free space Cv$tablespace Ddba extents

Segment,table/index/temp/rollbackExtent ___Block ___Create tablespace wshDatafile d:\oracle\db1.dbf size 5mExtent management dictionaryDefault storage ( Initial 100kNext 100kPctincrease 10)offlineCreate tablespace wshDatafile d:\oracle\db1.dbf size 5mExtent management localUniform 1m;

Delete emp;Rollback;Select * from v$dablespace;Select * from v$datafile;Select t1.name,t2.name from v$tablespace t1,v$datafile t2 where t1.ts#=t2.ts#