oracle±í¿Õ¼ä²Ù×÷
-½¨Á¢±í¿Õ¼ä(oracleÖеÄtablespace(±í¿Õ¼ä)¾ÍÏ൱ÓÚsqlserverµÄdatabase)
CREATE TABLESPACE data01
DATAFILE 'D:\oracle\ora92\oradata\db\DATA01.dbf' SIZE 200M
UNIFORM SIZE 128k;
#Ö¸¶¨Çø³ß´çΪ128k,Èç²»Ö¸¶¨£¬Çø³ß´çĬÈÏΪ64k
--½¨Á¢ÁÙʱ±í¿Õ¼ä
CREATE TEMPORARY TABLESPACE temp_data
TEMPFILE 'D:\TEMP_DATA.dbf' SIZE 100M
--½¨Á¢Óû§
CREATE USER peter IDENTIFIED BY peter
DEFAULT TABLESPACE data01
TEMPORARY TABLESPACE temp_data;
--¸øÓû§ÊÚȨ
grant connect,resource,dba to peter;
-- ´Ó '½¨Á¢±í¿Õ¼ä' µ½ '½¨Á¢ÁÙʱ±í¿Õ¼ä' µ½ ’½¨Á¢Óû§‘ µ½ ’¸øÓû§ÊÚȨ’ £¬
-- µ½´Ë¾Í¿ÉÒÔÓý¨Á¢µÄÓû§½øÐеǽ£¬È»ºó½¨Á¢tableÁË
-- ²¢ÇÒÒÔij¸öÓû§µÄÉí·Ý½øÐеǽ£¬½øÐб¸·ÝÓ뻹ÔÁË
Ò»¡¢½¨Á¢±í¿Õ¼ä
CREATE TABLESPACE data01
DATAFILE '/oracle/oradata/db/DATA01.dbf'
SIZE 500M
UNIFORM SIZE 128k; #Ö¸¶¨Çø³ß´çΪ128k,Èç²»Ö¸¶¨£¬Çø³ß´çĬÈÏΪ64k
(×¢Ò⣬±ØÐëÏÈдdatafile²ÅÄÜдsizeºÍuniform size£¬ÒòΪֻÓÐÏÈÖ¸¶¨ÁËÎļþ²ÅÄܹ»Ö¸¶¨ÎļþµÄ´óС£¬ÕâÊÇÒ»¸öÒò¹û¹Øϵ)
¶þ¡¢½¨Á¢UNDO±í¿Õ¼ä
CREATE UNDO TABLESPACE UNDOTBS02
DATAFILE '/oracle/oradata/db/UNDOTBS02.dbf' SIZE 50M
#×¢Ò⣺ÔÚOPEN״̬ÏÂijЩʱ¿ÌÖ»ÄÜÓÃÒ»¸öUNDO±í¿Õ¼ä£¬Èç¹ûÒªÓÃн¨µÄ±í¿Õ¼ä£¬±ØÐëÇл»µ½¸Ã±í¿Õ¼ä:
ALTER SYSTEM SET undo_tablespace=UNDOTBS02;
Èý¡¢½¨Á¢ÁÙʱ±í¿Õ¼ä
CREATE TEMPORARY TABLESPACE temp_data
TEMPFILE '/oracle/oradata/db/TEMP_DATA.dbf' SIZE 50M
ËÄ¡¢¸Ä±ä±í¿Õ¼ä״̬
1.ʹ±í¿Õ¼äÍÑ»ú
ALTER TABLESPACE game OFFLINE;
Èç¹ûÊÇÒâÍâɾ³ýÁËÊý¾ÝÎļþ£¬Ôò±ØÐë´øÓÐRECOVERÑ¡Ïî
ALTER TABLESPACE game OFFLINE FOR RECOVER;
2.ʹ±í¿Õ¼äÁª»ú
ALTER TABLESPACE game ONLINE;
3.ʹÊý¾ÝÎļþÍÑ»ú
ALTER DATABASE DATAFILE 3 OFFLINE;
4.ʹÊý¾ÝÎļþÁª»ú
ALTER DATABASE DATAFILE 3 ONLINE;
5.ʹ±í¿Õ¼äÖ»¶Á
ALTER TABLESPACE game READ ONLY;
6.ʹ±í¿Õ¼ä¿É¶Áд
ALTER TABLESPACE game READ WRITE;
Î塢ɾ³ý±í¿Õ¼ä£¨É¾³ýÁÙʱ±í¿Õ¼äÒ²ÊÇͬÑùµÄд·¨£©
DROP TABLESPACE data01 INCLUDING CONTENTS AND DATAFILES;
drop tablespace temp_data including contents and datafiles;(ɾ³ýÁÙʱ±í¿Õ¼ä)
Áù¡¢À©Õ¹±í¿Õ¼ä
Ê×ÏȲ鿴±í¿Õ¼äµÄÃû×ÖºÍËùÊôÎļþ
select tablespace_name, file_id, file_n
Ïà¹ØÎĵµ£º
Oracle ÈýÖÖ¼¯ºÏÊý¾ÝÀàÐ͵ıȽÏ:
PL/SQLÖÐûÓÐÊý×éµÄ¸ÅÄËûµÄ¼¯ºÏÊý¾ÝÀàÐͺÍÊý×éÊÇÏàËƵġ£ÔÚ7.3ÒÔÇ°µÄ°æ±¾ÖÐÖ»ÓÐÒ»ÖÖ¼¯ºÏ£¬³ÆΪPL/SQL±í£¬ÔÚÕâÖ®ºóÓÖÓÐÁ½ÖÖ¼¯ºÏÊý¾ÝÀàÐÍ:ǶÌ×±íºÍvarray¡£ÆäÖÐvarray¼¯ºÏÖеÄÔªËØÊÇÓÐÊýÁ¿ÏÞÖƵģ¬index_by±íºÍǶÌ×±íÊÇûÓÐÕâ¸öÏÞÖƵġ£index-by±íÊÇÏ¡ÊèµÄ£¬Ò²¾ÍÊÇ˵ϱê¿ÉÒÔ²»Á¬Ðø ......
FORALLÓï¾äµÄÒ»¸ö¹Ø¼üÐԸĽø£¬Ëü¿É´ó´ó¼ò»¯´úÂ룬²¢ÇÒ¶ÔÓÚÄÇЩҪÔÚPL/SQL³ÌÐòÖиüкܶàÐÐÊý¾ÝµÄ³ÌÐòÀ´Ëµ£¬Ëü¿ÉÏÔÖøÌá¸ßÆäÐÔÄÜ¡£
1:
ÓÃFORALLÀ´ÔöÇ¿DMLµÄ´¦ÀíÄÜÁ¦
OracleΪOracle8iÖеÄPL/SQLÒýÈëÁËÁ½¸öеÄÊý¾Ý²Ù×ÝÓïÑÔ£¨DML£©Óï¾ä£ºBULK COLLECTºÍFORALL¡£ÕâÁ½¸öÓï¾äÔÚPL/SQLÄÚ²¿½øÐÐÒ»ÖÖÊý×é´¦Àí
£»BULK COLLE ......
Oracle Database 10g ÌṩÁËÒ»¸öÏÔÖø¸Ä½øµÄ¹¤¾ß£º×Ô¶¯¹¤×÷¸ºÔØÐÅÏ¢¿â (AWR:Automatic Workload Repository)¡£Oracle ½¨ÒéÓû§ÓÃÕâ¸öÈ¡´ú Statspack¡£AWR ʵÖÊÉÏÊÇÒ»¸ö Oracle µÄÄÚÖù¤¾ß£¬Ëü²É¼¯ÓëÐÔÄÜÏà¹ØµÄͳ¼ÆÊý¾Ý£¬²¢´ÓÄÇЩͳ¼ÆÊý¾ÝÖе¼³öÐÔÄÜÁ¿¶È£¬ÒÔ¸ú×ÙDZÔÚµÄÎÊÌâ¡£Óë Statspack ²»Í¬£¬¿ìÕÕÓÉÒ»¸ö³ÆΪ MMON µÄеĺó ......
Ç°¼¸ÌìÃæÊÔµÄʱºòÃæÊÔ¹Ù²ÅÎʹýÎÒORACLEµÄÌåϵ½á¹¹£¬ÈÃÎÒÔÚÒ»ÕÅ°×Ö½ÉÏ»³öÀ´¡£»ØÍ·ÏëÏ뵱ʱ´ðµÃ»¹²»´í£¬´ó²¿·ÖÄÚÈݶ¼ÃèÊö³öÀ´ÁË£¬ºÇºÇ£¬¸Õ²ÅÔÚÍøÉÏ¿´µ½Ò»Æª½²½âORACLEÌåϵ½á¹¹µÄÎÄÕ£¬¾õµÃ²»´í£¬×ª¹ýÀ´´æ×Å£¬½«À´¿ÉÄÜ»áÓõ½¡£
=================================================================================
ÔÚ±¾ÎÄÀ ......
sql½Å±¾£¬Ò²¾ÍÊÇÔÚÎļþÖÐдÓÐsqlÓï¾äµÄÎļþ£¬¿ÉÒÔÔÚsqlplusÖÐÔËÐС£
ÒýÈësql½Å±¾
sqlplus Óû§Ãû/ÃÜÂë @sql½Å±¾.sql
£¨×¢Ò⣺ÔÚÓû§ÃûÃÜÂëÊäÈë½áÊøºóÒ»¶¨Òª¼Ó¿Õ¸ñÈ»ºóÔÙд@sql½Å±¾£©
OracleÖеĿÕÖµ»áµ±×÷ÎÞÇî´ó´¦Àí£¬Æäʵ¿ÕÖµ¸ù±¾¾Í²»»á´æ´¢£¬Ö»ÊÇ¿´×÷ÊÇÎÞÇî´ó¡£
OracleÖпØÖÆ´¦Àíº¯Êý NVL(×Ö¶ÎÃû£¬ ......