Oracle 表空间与数据文件.doc

Oracle -=-Oracle -=/*-(systemsysaux)SYSTEM -UNDO -dml,dqlSYSAUX -10g systemSYSAUX,systemSYSAUXTEMP -USERS -10g systemOracle1. Schema: -2. database-tablespace- segment-extent- blockOracle3. OS block -datafile 4. scott */-scottSQL select USERNAME,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE5. from dba_users6. where username = SCOTT;USERNAME DEFAULT_TABLESPACE TEMPORARY_TABLESPACE- - -SCOTT USERS TEMPSQL col name format a50SQL select , -7. from v$tablespace t1,v$datafile t28. where t1.ts# = t2.ts#;NAME NAME- -SYSTEM /u01/app/oracle/oradata/orcl/system01.dbfUNDOTBS1 /u01/app/oracle/oradata/orcl/undotbs01.dbfSYSAUX /u01/app/oracle/oradata/orcl/sysaux01.dbfUSERS /u01/app/oracle/oradata/orcl/users01.dbfEXAMPLE /u01/app/oracle/oradata/orcl/example01.dbf/*scott-emp(table)-()-system() -user()-user()-user01.dbf-databufferdbwr */*-CREATE BIGFILE | SMALLFILE TEMPORARY TABLESPACE tablespace nameDATAFILE datafile spec | TEMPFILE tempfile specMINIMUM EXTENT minimum extent sizeBLOCKSIZE blocksizeCOMPRESS|NOCOMPRESS DEFAULT STORAGE (default storage clause)LOGGING|NOLOGGINGFORCE LOGGINGONLINE|OFFLINEEXTENT MANAGEMENT DICTIONARY |LOCAL AUTOALLOCATE|UNIFORM SIZE sizeSEGMENT SPACE MANAGEMENT MANUAL|AUTOFLASHBACK ON|OFF-1. create tablespaceDBAsysdba,sysoper2. bigfiel smallifle T bigfile3. I/OI/O4. datafile */-BIGFILE SMALLFILE,SMALLFILE,SMALLFILESQL select PROPERTY_NAME,PROPERTY_VALUE from database_properties where PROPERTY_NAME like %TBS%;PROPERTY_NAME PROPERTY_VALUE- -DEFAULT_TBS_TYPE SMALLFILE-SQL alter database set default bigfile tablespace;Database altered.SQL select PROPERTY_NAME,PROPERTY_VALUE from database_properties where PROPERTY_NAME like %TBS%;PROPERTY_NAME PROPERTY_VALUE- -DEFAULT_TBS_TYPE BIGFILE-SQL alter database set default smallfile tablespace;Database altered.-BIGFILE | SMALLFILE/*bigfile)TGblock,M-blockcreate bigfile tablespace datafile .;controldatafilebigfile */-SQL create tablespace TBS1 datafile /u01/app/oracle/oradata/orcl/tbs1.dbf size 100m;Tablespace created.SQL col name format a50SQL select ,5. from v$tablespace t1,v$datafile t26. where t1.ts# = t2.ts#;NAME NAME- -SYSTEM /u01/app/oracle/oradata/orcl/system01.dbfUNDOTBS1 /u01/app/oracle/oradata/orcl/undotbs01.dbfSYSAUX /u01/app/oracle/oradata/orcl/sysaux01.dbfUSERS /u01/app/oracle/oradata/orcl/users01.dbfEXAMPLE /u01/app/oracle/oradata/orcl/example01.dbfTBS1 /u01/app/oracle/oradata/orcl/tbs1.dbf/*/CREATE TMEPORARY TABLESPACE tablespace_nameTEMPFILE . SIZE nCREATE TEMPORARY TABLESPACE T2 TEMPFILE /opt/oracle/oradata/orcl/t2.dbfsize 100m;/*NOLOGGING*/-(1)alter database tempfile /opt/oracle/oradata/orcl/t2.dbf resize 150m;-(2)alter database tempfile /opt/oracle/oradata/orcl/t2.dbfautoextend on next 10m maxsize 50m;-(3)alter tablespace t2 add tempfile /disk8/oracle/t2b.dbf size 50m;-select TABLESPACE_NAME,FILE_NAME,BYTES/1024/1024 SIZE(MB)from dba_temp_files;-select * from database_properties;-ALTER DATABASE DEFAULT TEMPORARY TABLESPACE T2;/*system*/SELECT dbms_metadata.get_ddl(TABLESPACE,SYSTEM) FROM dual;/*10G1 */CREATE TEMPORARY TABLESPACE tablespace_name TEMPFILE . SIZE nTABLESPACE GROUP group_name;ALTER TABLESPACE tablespace_name TABLESPACE GROUP group_name;ALTER TABLESPACE T3 TABLESPACE GROUP TEMP_GRP;-:dba_tablespace_groupsSELECT * FROM DBA_TABLESPACE_GROUPS;-ALTER TABLESPACE tablespace_name TABLESPACE GROUP ;-ALTER DATABASE DEFAULT TEMPORARY TABLESPACE tablespace_name;-UNDO/*/CREATE UNDO TABLESPACE tablespace_name DATAFILE . SIZE nSQL CREATE UNDO TABLESPACE undo2DATAFILE /opt/oracle/oradata/orcl/undo2_01.dbf SIZE 200M,/opt/oracle/oradata/orcl/undo2_02.dbf size 100m;-UNDO:ALTER SYSTEM SET UNDO_TABLESPACE=tablespace_name/*UNDODATAFILEDATAFILEAUTOEXTENDUNDO */DROP TABLESPACE tablespace_name/*UNDOUNDO */-/docs/cd/B28359_01/server.111/b28286/statements_7003.htm#SQLRF01403/*oracle 8i(iDMTDMToracleUET$used extents)FET$(freeextents)oracleSQLDMTLMT iR2LMTextentextentoracleLMTDMT- */SQL select TABLESPACE_NAME,EXTENT_MANAGEMENT,BLOCK_SIZE,STATUS,CONTENTS,FORCE_LOGGING,BIGFILE7. from dba_tablespaces;TABLESPACE_NAME EXTENT_MAN BLOCK_SIZE STATUS CONTENTS FOR BIG- - - - - - -SYSTEM LOCAL 8192 ONLINE PERMANENT NO NOUNDOTBS1 LOCAL 8192 ONLINE UNDO NO NOSYSAUX LOCAL 8192 ONLINE PERMANENT NO NOTEMP LOCAL 8192 ONLINE TEMPORARY NO NOUSERS LOCAL 8192 ONLINE PERMANENT NO NOEXAMPLE LOCAL 8192 ONLINE PERMANENT NO NOTBS1 LOCAL 8192 ONLINE PERMANENT NO NO-DMT LMT -exec dbms_space_admin.tablespace_migrate_to_local() -exec dbms_space_admin.tablespace_migrate_from_local()-opensystem , sysaux ALTER TABLESPACE XXX RENAME TO YYY-/*/-(1)ALTER DATABASE DATAFILE . | FileNo RESIZE XX-(2)ALTER DATABASE DATAFILE . | FileNo AUTOEXTEND ON | OFF NEXT 20M MAXSIZE 300M;-SELECT FILE_NAME,TABLESPACE_NAME,AUTOEXTENSIBLE FROM dba_data_files;/* (3)I/O */ALTER TABLESPACE tablespace_name ADD DATAFILE . SIZE XX;ALTER TABLESPACE tbs2 ADD DATAFILE /u01/app/oracle/oradata/orcl/tbs2.dbf size 200m autoextend on ;-onlineofflineread onlyread write/*offline(ONLINE)*/ALTER TABLESPACE tablespace_name OFFLINE;-ALTER TABLESPACE tablespace_name ONLINE;-ALTER DATABASE RECOVER TABLESPACE tablespace_nameALTER TABLESPACE tablespace_name ONLINE;/*read only:DMLDDLDQL */ALTER TABLESPACE tablespace_name READ ONLY;read writeALTER TABLESPACE tablespace_name READ WRITE;-tablespacename onlie read onlysystem online read writesysaux offline read onlyundo offline read only-SQL select tablespace_name,file#,v.status,v.enabled from dba_data_files d,8. v$datafile v9. where d.file_id = v.file#;TABLESPACE_NAME FILE# STATUS ENABLED- - - -SYSTEM 1 SYSTEM READ WRITEUNDOTBS1 2 ONLINE READ WRITESYSAUX 3 ONLINE READ WRITEUSERS 4 ONLINE READ WRITEEXAMPLE 5 ONLINE READ WRITETBS1 6 ONLINE READ WRITE-SQL alter tablespace users read only;Tablespace altered.SQL update scott.emp set sal = sal + 50 where ename = SCOTT; -update scott.emp set sal = sal + 50 where ename = SCOTT*ERROR at line 1:ORA-00372: file 4 cannot be modified at this timeORA-01110: data file 4: /u01/app/oracle/oradata/orcl/users01.dbfSQL select tablespace_name,file#,v.status,v.enabled from dba_data_files d,10. v$datafile v11. where d.file_id = v.file# and tablespace_name = USERS;TABLESPACE_NAME FILE# STATUS ENABLED- - - -USERS 4 ONLINE READ ONLY-SQL alter tablespace users offline;Tablespace altered.SQL update scott.emp set sal = sal + 100 where ename = SCOTT;/*/update scott.emp set sal = sal + 100 where ename = SCOTT*ERROR at line 1:ORA-00376: file 4 cannot be read at this timeORA-01110: data file 4: /u01/app/oracle/oradata/orcl/users01.dbf-SQL alter tablespace users online;Tablespace altered.SQL select tablespace_name,file#,v.status,v.enabled from dba_data_files d,12. v$datafile v13. where d.file_id = v.file# and tablespace_name = USERS;TABLESPACE_NAME FILE# STATUS ENABLED- - - -USERS 4 ONLINE READ ONLY-readonly SQL delete from scott.emp where ename = SCOTT;SQL rollback;Rollback complete.-SQL alter tablespace users read write;Tablespace altered-openarchive:-1select TABLESPACE_NAME,FILE_NAME,BYTES/1024/1024 SIZE(MB)from dba_data_files;-2alter tablespace uu offline;-3(cp)host mv /opt/oracle/oradata/orcl/tt.dbf /disk9/uu1.dbf-4(rename)alter tablespace uu rename datafile /opt/oracle/oradata/orcl/tt.dbfto /disk9/uu1.dbf;-5alter tablespace uu online-1mountstartup mount-2host mv /opt/oracle/oradata/orcl/tt.dbf /disk9/uu1.dbf-3alter database rename file /disk8/uu.dbf to /disk8/uu2.dbf;-4alter database open;/*/ */DROP TABLESPACE tablespace_name INCLUDING CONTENTS AND DATAFILESINCLUDING CONTENTS - INCLUDING CONTENTS AND DATAFILES -ALTER USER user_name QUOTA n ON tablespace_name;ALTER USER scott QUOTA 10m on UU;-ALTER USER user_name QUOTA UNLIMITED ON tablespace_name;GRANT UNLIMITED TABLESPACE TO user_name;-1SELECT name FROM v$datafile;-2ALTER DATABASE DATAFILE . OFFLINE;-3ALTER DATABASE DATAFILE . ONLINE- RECOVER DATAFILE . ALTER DATABASE RECOVER TABLESPACE tablespace_name;- -dba_tablespacesv$tablespace-dba_data_filesv$datafile-dba_temp_filesv$tempfile-SQL select PROPERTY_NAME,PROPERTY_VALUE from database_properties14. where PROPERTY_NAME like DEFAULT%;-SQLALTER DATABASE DEFAULT TABLESPACE tablespace_name-SQLALTER DATABASE DEFAULT TEMPORARY TABLESPACE tablespace_name-SQLselect USERNAME,USER_ID,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE from dba_users;-SQLALTER USER user_name DEFAULT TABLESPACE tablespace_name-dba_data_files;dba_free_space;-SQL select tablespace_name,sum(bytes/1024/1024)15. from dba_free_space group by tablespace_name;/*datafiledatafiledatafiledbwrdata buffer cachedirty buffer data dbwrckptdirty buffer data buffer caceh offline/read only,drop, truncate */-SQL select file# ,name,status,enabled,bytes/1024/1024 M from v$datafile;FILE# NAME STATUS ENABLED M- - - - -1. /u01/app/oracle/oradata/orcl/system01.dbf SYSTEM READ WRITE 4902. /u01/app/oracle/oradata/orcl/undot