表空间和数据文件管理.doc

表空间和数据文件管理鍦RACLE涓紝鍒涘缓鐨勬墍鏈夋暟鎹粠閫昏緫涓婄湅閮芥槸瀛樻斁鍦绌洪棿涓紝褰撶劧琛闂翠笅鏈夋銆佸尯銆佸潡銆?鐗悊涓婄湅鏄斁鍦暟鎹枃浠朵腑銆備竴涓绌洪棿鍙敱澶氫釜鏁版嵁鏂囦欢缁勬垚銆?琛闂寸殑绠悊 琛闂寸殑绠悊鏂瑰紡锛?瀛楀吀绠悊锛氬叏搴撴墍鏈夌殑绌洪棿鍒嗛厤閮芥斁鍦暟鎹瓧鍏镐腑銆傚鏄撳紩璧峰瓧鍏镐簤鐢紝鑰屽鑷存兘闂銆?鏈湴绠悊锛氱闂村垎閰嶄笉鏀惧湪鏁版嵁瀛楀吀锛岃屽湪姣忎釜鏁版嵁鏂囦欢澶撮儴鐨勭3鍒扮8涓潡鐨勪綅鍥惧潡锛? 鏉鐞嗙闂村垎閰嶃? 绯荤粺涓粯璁垱寤虹殑鍑犱釜琛闂达細system sysauxusersundotbs 琛闂寸殑鍒嗙被锛?姘镐箙琛闂? 瀛樻斁姘镐箙鎬暟鎹紝濡傝锛岀储寮曘?涓存椂琛闂?涓嶈兘瀛樻斁姘镐箙鎬璞紝鐢簬淇濆瓨鏁版嵁搴撴帓搴忥紝鍒嗙粍鏃朵骇鐢熺殑涓存椂鏁版嵁銆?UNDO琛闂?淇濆瓨鏁版嵁淇敼鍓嶇殑闀滆薄鍒涘缓姘镐箙琛闂?CREATE TABLESPACE tablespace_nameDATAFILE . SIZE .濡傦細SQL create tablespace tt datafile /u02/tt.dbf size 10m;鏌湅绯荤粺鐨勯粯璁殑姘镐箙琛闂? 鏁版嵁搴撶骇锛?SQL select PROPERTY_NAME,PROPERTY_VALUE from database_properties; 鐢埛绾細SQL select username,DEFAULT_TABLESPACE from dba_users;淇敼榛樿琛闂? 鏁版嵁搴撶骇锛?SQL alter database default tablespace tt; 鐢埛绾細SQL alter user scott default tablespace users;鍒犻櫎琛闂? DROP TABLESPACE tablespace_name INCLUDING CONTENTS AND DATAFILES 褰撳墠鐨勬暟鎹簱绾殑榛樿琛闂翠笉鏈夊垹闄紝鐢埛绾殑鍙互鍒犻櫎SQL drop tablespace tt2 including contents and datafiles;鏌湅琛闂村灏忥細SQL select TABLESPACE_NAME,sum(BYTES)/1024/1024 MB FROM DBA_DATA_FILESGROUP BY TABLESPACE_NAME;鏌湅琛闂村墿浣欑闂?SQL select TABLESPACE_NAME,sum(BYTES)/1024/1024 FREE(MB) from dba_free_space group by TABLESPACE_NAME;姘镐箙琛闂寸殑鎵锛? 鏂规硶涓锛氶噸缃暟鎹枃浠跺灏?SQL alter database datafile /u01/app/oracle/oradata/ocp/sysaux01.dbfresize 500M; -鍦墽琛屽畠涔嬪墠瑕佹煡鐪嬩竴涓嬶紝鍘熷澶皬鏄澶?SQL select TABLESPACE_NAME,FILE_NAME,BYTES/1024/1024 MB FROM dba_data_files; 鏂规硶浜岋細涓鸿绌洪棿澧炲姞鏁版嵁鏂囦欢SQL alter tablespace users add datafile /u02/user02.dbf size 50m; 鏂规硶涓夛細璁剧疆鏁版嵁鏂囦欢鐨勮嚜鍔墿灞?SQL alter database datafile /u02/tt02.dbf autoextend on next 5mmaxsize 20m;闄愬埗鐢埛瀵硅绌洪棿鐨勪娇鐢紙琛闂寸殑纾佺洏閰嶉锛?1銆佸彇娑堢敤鎴风殑鏃犻檺浣跨敤琛闂存潈闄?鐢埛鑷凡鍙互SQL select * from session_privs;SQL REVOKE UNLIMITED TABLESPACE FROM SCOTT;2銆佸埗瀹氶厤棰?SQL alter user scott quota 5m on tt; -鏌湅琛灏? SQL select SEGMENT_NAME,SEGMENT_TYPE,TABLESPACE_NAME,BYTES/1024/1024 MB from dba_segments where SEGMENT_NAME=TT_TAB;鍙栨秷閰嶉SQL alter user scott quota unlimited on tt;鏌閰嶉SQL select * from dba_ts_quotas;琛闂寸姸鎬佺殑鏀瑰彉锛?琛闂寸姸鎬侊細online銆乷ffline銆乺ead only銆乺ead writeALTER TABLESPACE tablespace_name online/offline/read only/read writeoffline 涓鑸敤浜庢暟鎹簱鐨勮繛鏈哄浠斤紝鏁版嵁鎭绛夌淮鎶搷浣?琛闂村彧琛紝鍏朵腑鐨勮锛屼笉鑳借繘琛屼换浣旸ML鎿嶄綔锛屼絾鏄兘DROP TABLE 鏈変簺琛闂翠笉鑳絆FFLINE锛屽锛歴ystem,undo绛?system 涓嶈兘offline锛屼篃涓嶈兘read only褰撳墠鐨剈ndo琛绌洪棿锛屼笉鑳給ffline,涔熶笉鑳絩ead only褰撳墠鐨勪复鏃惰绌洪棿涓嶈兘offline,涔熶笉鑳絩ead onlySYSAUX鍙互offline 涓嶈兘read only鏌琛闂寸姸鎬侊細SQL select TABLESPACE_NAME,CONTENTS,STATUS from dba_tablespaces;SQL select FILE#,STATUS,ENABLED from v$datafile;琛闂存敼鍚?SQL alter tablespace tt2 rename to tt;绉诲姩鏁版嵁鏂囦欢 1銆佹煡鐪嬭绌洪棿鐨勬枃浠跺垎甯?SQL select TABLESPACE_NAME,FILE_NAME,BYTES/1024/1024 MB from dba_data_files; 2銆佸皢琛闂寸绾?SQL alter tablespace users offline; 3銆佸湪鎿嶄綔绯荤粺涓嬪皢鏁版嵁鏂囦欢绉诲埌鍙竴浣嶇疆 SQL host mv /u01/app/oracle/oradata/ocp/users01.dbf /u02/SQL host ls /u02/ 4銆佷慨鏀规帶鍒舵枃浠剁殑璁板綍鎸囬拡SQL alter database rename file /u01/app/oracle/oradata/ocp/users01.dbf to /u02/users01.dbf;鎴栬?SQL alter tablespace users rename datafile /u01/app/oracle/oradata/ocp/users01.dbf to /u02/users01.dbf;娉細鎵姝鏃讹紝鐩爣鏂囦欢锛圱O鍚庨潰鐨勯偅涓娈碉級涓瀹氳瀛樺湪銆? 5銆佸皢琛闂村湪绾?SQL alter tablespace users online;瀵逛簬閭簺涓嶈兘offline鐨勮绌洪棿锛屽彧鑳藉叧闂暟鎹紝鍦ount鐘舵佷笅淇敼锛屼慨鏀瑰悗鍐峅PEN澶枃浠惰绌洪棿 澶枃浠惰绌洪棿锛屼竴涓绌洪棿鍙兘鏈変竴涓暟鎹枃浠讹紝姝枃浠舵渶澶彲浠揪鍒?G涓暟鎹潡 鍒涘缓澶枃浠惰绌洪棿锛?SQL create bigfile tablespace bf_ts datafile /u02/tf_ts01.dbf size 200m; 鍒涘缓鐨勮绌洪棿榛樿鏄粈涔堝鏂囦欢琛闂磋繕鏄皬鏂囦欢琛闂村彇鍐充簬 default tablespaceSQL select PROPERTY_NAME,PROPERTY_VALUE from database_properties; 璁剧疆榛樿鐨勮绌洪棿绫诲瀷锛?SQL alter database set default smallfile tablespace;OMF绠悊鐨勮绌洪棿 1銆佽缃甦b_create_file_dest鍙傛暟SQL alter system set db_create_file_dest=/u02/; 2銆佸垱寤鸿绌洪棿锛岀渷鐣枃浠跺悕SQL create tablespace t3;SQL create tablespace t4 datafile size 200m;涓存椂琛闂? 涓存椂琛闂翠富瑕佺敤浜庡瓨鏀炬帓搴忋佸垎缁勬椂浜敓鐨勪复鏃舵暟鎹紝涓嶈兘瀛樻斁姘镐箙鎬璞備竴鑸儏鍐典笅锛? 绯荤粺鍙兘浣跨敤涓涓复鏃惰绌洪棿锛堜复鏃惰绌洪棿缁勯櫎澶栵級銆?鍒涘缓涓存椂琛闂达細CREATE TEMPORARY TABLESPACE tablespace_nameTEMPFILE . SIZE . REUSESQL create temporary tablespace temp2 tempfile /u02/temp201.dbf size 200M;鍒犻櫎涓存椂琛闂达細DROP TABLESPACE tablespace_name;涓存椂琛闂存墿瀹癸細 鏂规硶涓锛氬鍔犱复鏃舵枃浠跺灏?SQL alter database tempfile /u02/temp201.dbf resize 300m; 鏂规硶浜岋細娣诲姞涓存椂鏂囦欢SQL alter tablespace temp2 add tempfile /u02/temp202.dbf size 100m; 鏂规硶涓夛細璁复鏃舵枃浠惰嚜鍔墿鍏?SQL alter database tempfile /u02/temp201.dbf autoextend on next 1m maxsize 500m;鏌湅涓存椂鏂囦欢SQL select TABLESPACE_NAME,FILE_NAME,BYTES/1024/1024 MB FROM DBA_temp_files;鍒犻櫎涓存椂琛闂翠腑鐨勪复鏃舵枃浠讹細SQL alter tablespace temp2 drop tempfile /u02/temp202.dbf;涓存椂琛闂达紝鍙兘浣跨敤涓涓紙鍗抽粯璁殑涓磋繘琛闂达級锛屾煡鐪嬮粯璁殑涓存椂琛闂达細 鏁版嵁搴撶骇锛?SQL select PROPERTY_NAME,PROPERTY_VALUE from database_properties; 鐢埛绾細SQL select username,default_tablespace,temporary_tablespace from dba_users;淇敼榛樿涓存椂琛闂达細 鏁版嵁搴撶骇锛?SQL alter database default temporary tablespace temp2; 鐢埛绾細SQL alter user scott temporary tablespace temp2;涓磋繘琛闂寸粍:涓磋繘琛闂寸粍鏄竴涓昏緫姒傚康锛屼笉闇瑕佹樉绀虹殑鍒涘缓鍜屽垹闄傚彧瑕佹妸涓涓复鏃惰绌洪棿鍒嗛厤涓涓粍涓紝涓存椂琛闂寸粍灏辫嚜鍔垱寤猴紝鎵鏈夌殑涓存椂琛闂翠粠涓闄氨鑷姩鍒犻櫎銆? 鎸囨淳缁勶細SQL create temporary tablespace temp3 tempfile /u02/t301.dbf size 50mtablespace group temp_grp; -寤鸿绌洪棿鏃?SQL alter tablespace temp2 tablespace group temp_grp; -寤哄鍚庣殑淇敼 浠庣粍涓闄細SQL alter tablespace temp3 tablespace group ; 鏌湅涓存椂琛闂寸粍锛?SQL select * from dba_tablespace_groups;杞偍涓存椂鏂囦欢alte system dump tempfile 鏂囦欢鍙锋垨鏂囦欢鍚?block min block_no block max block_no;UNDO琛闂? 鍒涘缓UNDO琛闂?CREATE UNDO TABLESPACE tablespace_name DATAFILE . SIZE . 鍒犻櫎DROP TABLESPACE tablespace_name 澶儴鍒嗘搷浣滀笌姘镐箙琛闂寸浉浼? 鍦换涓鏃跺埢锛孫RACLE鍙兘浣跨敤涓涓猆NDO琛闂达紝鍏蜂綋浣跨敤鍝竴涓紝鍙氳繃SQL show parameter undo_tablespace 淇敼褰撳墠绯荤粺鐨刄NDO琛闂达細SQL alter system set undo_tablespace=undo2; undo_retention 鐢簬璁剧疆鏁版嵁鍦NDO琛闂翠腑鐨勪繚鐣欐椂闀匡紙鍗曚綅锛氱锛?SQL alter system set undo_retention=1800; 姝繚鐣欐椂闀匡紝鏄笉鑳戒繚璇佺殑锛屽彇鍐充簬UNDO琛闂寸殑澶皬鍙兘杩囪缃紝璁叾寮哄埗淇濈暀SQL select TABLESPACE_NAME,CONTENTS,STATUS,RETENTION from dba_tablespaces;TABLESPACE_NAME CONTENTS STATUS RETENTION- - - -SYSTEM PERMANENT ONLINE NOT APPLYUNDOTBS1 UNDO ONLINE NOGUARA