OracleÖ®¹ÜÀí½ÇÉ«
Ô¤¶¨Òå½ÇÉ«ÊÇoracleÌṩµÄ½ÇÉ«,ÕâЩ½ÇÉ«ÊÇÔÚ½¨Á¢Êý¾Ý¿â,°²×°Êý¾Ý×ÖµäÊÓͼºÎPL/SQL°üʱ½¨Á¢µÄ,²¢ÇÒÿÖÖ½ÇÉ«¶¼ÓÃÓÚÖ´ÐÐÒ»Ð©ÌØ¶¨¹ÜÀíÈÎÎñ.
1,CONNECT½ÇÉ«
CONNECT½ÇɫʱÔÚ½¨Á¢Êý¾Ý¿âʱ,oracleÖ´Ðнű¾SQL.BSQ×Ô¶¯½¨Á¢µÄ½ÇÉ«,¸Ã½ÇÉ«¾ßÓÐÓ¦Óÿª·¢ÈËÔ±ËùÐèµÄ¶àÊýȨÏÞ.CONNECT½ÇÉ«¾ßÓеÄËùÓÐϵͳȨÏÞÈçÏÂ:
ALTER SESSION Ð޸ĻỰ²ÎÊýÉèÖÃ
CREATE CLUSTER ½¨Á¢´Ø
CREATE DATABASE LINK ½¨Á¢Êý¾Ý¿âÁ´
CREATE SEQUENCE ½¨Á¢ÐòÁÐ
CREATE SESSION ½¨Á¢»á»°(Á¬½Óµ½Êý¾Ý¿â)
CREATE PUBLIC SYNONYM ½¨Á¢Í¬Òå´Ê
CREATE TABLE ½¨±í
CREATE VIEW ½¨Á¢ÊÓͼ
2,RECOURCE½ÇÉ«
RECOURCE ½ÇɫʱÔÚ½¨Á¢Êý¾Ý¿âʱ,oracleÖ´Ðнű¾SQL.BSQ×Ô¶¯½¨Á¢µÄ½ÇÉ«,¸Ã½ÇÉ«¾ßÓÐÓ¦Óÿª·¢ÈËÔ±ËùÐèµÄÆäËûȨÏÞ.È罨Á¢´æ´¢¹ý´Ó,´¥·¢Æ÷µÈ.½¨Á¢Êý¾Ý¿âÓû§ºó,Ò»°ãÇé¿öÏÂÖ»Òª¸øÓû§ÊÚÓèCONNECTºÍRECOURCE½ÇÉ«¾Í×ã¹»ÁË.ÐèҪעÒâµÄʱ,RECOURCE½ÇÉ«Òþº¬¾ßÓÐUNLIMITED TABLESPACEϵͳȨÏÞ.
CREATE CLUSTER ½¨Á¢´Ø
CREATE INDEXTYPE ½¨Á¢Ë÷ÒýÀàÐÍ
CREATE PROCEDURE ½¨Á¢PL/SQL³ÌÐòµ¥Ôª.
CREATE SEQUENCE ½¨Á¢ÐòÁÐ
CREATE TABLE ½¨±í
CREATE TRIGGER ½¨Á¢´¥·¢Æ÷
CREATE TYPE ½¨Á¢ÀàÐÍ
3.DBA
¸Ã½ÇÉ«¾ßÓÐËùÓÐϵͳȨÏÞºÍWITH ADMIN OPTIONÑ¡Ïî.ĬÈϵÄDBAÓû§ÎªSYSTEM,¸ÃÓû§¿ÉÒÔ½«ÏµÍ³È¨ÏÞÊÚÓèÆäËûÓû§,ÐèҪעÒâµÄÊÇ,DBA½ÇÉ«²»¾ß±¸SYSDBAºÍSYSOPERÌØÈ¨,¶øSYSDBAºÍSYSOPER×Ô¶¯¾ßÓÐDBA½ÇÉ«µÄËùÓÐȨÏÞ.
4,EXECUTE_CATALOG_ROLE
EXECUTE_CATELOG_ROLE½ÇÉ«ÌṩÁ˶ÔËùÓÐϵͳPL/SQL°ü(DBA_XXX)µÄEXECUTE¶ÔÏóȨÏÞ
5,SELECT_CATALOG_ROLE
¸Ã½ÇÉ«ÌṩÁËÔÚËùÓÐÊý¾Ý×Öµä(DBA_XXX)ÉϵÄSELECT¶ÔÏóȨÏÞ.
6,DELETE_CATALOG_ROLE
¸Ã½ÇÉ«ÌṩÁËϵͳÉ󼯱íSYS.AUD$ÉϵÄDELETE¶ÔÏóȨÏÞ.
7,EXP_FULL_DATABASE
¸Ã½ÇÉ«ÓÃÓÚÖ´ÐÐÊý¾Ý¿âµÄµ¼³ö²Ù×÷.¸Ã½ÇÉ«¾ßÓеÄȨÏ޺ͽÇɫΪ:
SELECT ANY TABLE ²éѯÈÎÒâ±í
BACKUP ANY TABLE ±¸·ÝÈÎÒâ±í
EXECUTE ANY PROCEDURE Ö´ÐÐÈκιý³Ì,º¯ÊýºÍ°ü
EXECUTE ANY TYPE Ö´ÐÐÈκζÔÏóÀàÐÍ
ADMINISTER RESOURCE MANAGER ¹ÜÀí×ÊÔ´¹ÜÀíÆ÷
EXECUTE_CATALOG_ROLE Ö´ÐÐÈκÎPL/SQLϵͳ°ü
SELECT_CATALOG_ROLE ²éѯÈκÎÊý¾Ý×Öµä
8.IMP_FULL_DATABASE
¸Ã½ÇÉ«ÓÃÓÚÖ´ÐÐÊý¾Ý¿âµ¼Èë²Ù×÷,Ëü°üº¬ÁËEXECUTE_CATALOG_ROLE,SELECT_CATALOG_ROLE½ÇÉ«ºÍ´óÁ¿ÏµÍ³È¨ÏÞ.
9.RECOVERY_CATALOG_OWNER
¸Ã½ÇɫΪ»Ö¸´Ä¿Â¼ËùÓÐÕßÌṩÁËϵͳȨÏÞ.
CREATE SESSION ½¨Á¢»á»°
ALTER SESSION
Ïà¹ØÎĵµ£º
SQLÖеĵ¥¼Ç¼º¯Êý
1.ASCII
·µ»ØÓëÖ¸¶¨µÄ×Ö·û¶ÔÓ¦µÄÊ®½øÖÆÊý;
SQL> select ascii('A') A,ascii('a') a,ascii('0') zero,ascii(' ') space from dual;
A A ZERO SPACE
--------- --------- --------- ---------
65 97 48 32
2.CHR
¸ø³öÕûÊý,·µ»Ø¶ÔÓ¦µÄ×Ö·û;
SQL> select chr(54740) zhao,chr(65) chr6 ......
ÎÊ£ºÊ²Ã´ÊÇNULL£¿
´ð£ºÔÚÎÒÃDz»ÖªµÀ¾ßÌåÓÐʲôÊý¾ÝµÄʱºò£¬Ò²¼´Î´Öª£¬¿ÉÒÔÓÃNULL£¬ÎÒÃdzÆËüΪ¿Õ£¬ORACLEÖУ¬º¬ÓпÕÖµµÄ±íÁ㤶ÈΪÁã¡£
ORACLEÔÊÐíÈκÎÒ»ÖÖÊý¾ÝÀàÐ͵Ä×Ö¶ÎΪ¿Õ£¬³ýÁËÒÔÏÂÁ½ÖÖÇé¿ö£º
1¡¢Ö÷¼ü×ֶΣ¨primary key£©£¬
2¡¢¶¨ÒåʱÒѾ¼ÓÁËNOT NULLÏÞÖÆÌõ¼þµÄ×Ö¶Î
˵Ã÷£º
1¡¢µÈ¼ÛÓÚûÓÐÈκÎÖµ¡¢ÊÇδ֪ ......
Èç¹ûtemp_t1²»´æÔÚ£¬
oracle:
create table temp_t1
as
select * from t1
sql server:
select * into temp_t1 from t1
Èç¹ûtemp_t1´æÔÚ£¬
oracle:
insert into table temp_t1
select * from t1
sql server:
insert into table temp_t1
select * from t1 ......
Ñ¡Ôñ10gÊÇÒòΪ¶ÔÍø¸ñ¼¼Êõ±È½ÏºÃÆæ£¬ºÃÏñ»¹Ã»µÃµ½¹ã·ºÓ¦Óã¬Ò²¾ÍÎÞËùνÓëÇ°Ãæ°æ±¾ÓкܴóÇø±ðÁË¡£
ÔÚѸÀ×ÉÏÏÂÁ˸öÈí¼þ£¬ÔËÐÐsqlplusw£¬È»ºóÕÕ×ÅÊ飬¿ñÇÃÁËÒ»·£¬ÓÐÔÚÍøÉÏÏÂÁ˸öÊÓÆµ½Ì³Ì£¨MLDNħÀֿƼ¼_Oracle¿ÎÌã©£¬½²µÃͦºÃµÄ£¬¾ÍÊÇʱ¼ä³¤Á˵㣬²»Èç¿´ÊéÀ´µÃ¿ì¡£·´ÕýÏÖÔÚÖ»ÊÇÏëÊìϤһÏ»ù±¾Óï¾ä¡£
¿´ÁËÁ½ÌìÊéÁË¡£Á˽âÁËÒ» ......
Oracle ¼ì²é¶ÔÏó
8.3. Oracle¶ÔÏóµÄ״̬
¹²·ÖÁù¸ö²¿·Ö£¬·Ö±ðΪ£º¼ì²éOracle¿ØÖÆÎļþ״̬£»¼ì²éOracleÔÚÏßÈÕ־״̬£»¼ì²éOracle±í¿Õ¼äµÄ״̬£»¼ì²éOracleËùÓÐÊý¾ÝÎļþ״̬£»¼ì²éOracleËùÓÐ±í¡¢Ë÷Òý¡¢´æ´¢¹ý³Ì¡¢´¥·¢Æ÷¡¢°üµÈ¶ÔÏóµÄ״̬£»¼ì²éOracleËùÓлعö¶ÎµÄ״̬¡£
8.3.1. Oracle¿ØÖÆÎļþ״̬
¼ì²é¿ØÖÆÎļþ×´Ì ......