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#