易截截图软件、单文件、免安装、纯绿色、仅160KB

SQL语句PART11


1. My test: (create and grant the sysdba to a new user by SQL*Plus)
CREATE USER FJTEST1 IDENTIFIED BY JEANJEANFANG;
GRANT SYSDBA TO FJTEST;
REVOKE SYSDBA from FJTEST;
CONNECT FJTEST1/JEANJEANFANG AS SYSDBA;
2. Using ORAPWD in windows:
C:\» ORAPWD;
(show help information)
3. to see the password file:
Grammer: ORACLE_BASE\ORACLE_HOME\database» attrib
F:\oracle\ora92\database\attrib
** if want to create a user that can be connect as normal and can create table, you should grant “create session”, “resource” privileges to the user or it cannot connect as normal.
e.g.:
SQL»create user fj identified by fj;
SQL»grant create session to fj;
SQL»grant resource to fj;
Then the user “fj” can be connected as “normal” and the schema belonging to this user will be created, where he/she can create tables.
SQL» select * from user_sys_privs;
Use above statements to check user’s own system privileges.
2.How to create password file using ORAPWD
F:\oracle\ora92\database\ORAPWD FILE=F:\oracle=ora92\database\PWDmydb.ora PASSWORD=oracle ENTRIES=30
F:\oracle\ora92\database\sqlplus /nolog
SQL» connect SYS/oracle AS SYSDBA
ERROR: ORA-01031: insufficient privileges
(如果发生以上错误, 说明在ORACLE中开启了多于一个的数据库, 并且处于STARTUP状态.此时要连接必须用:  connect SYS/oracle@SID AS SYSDBA, 必须在F:\oracle\ora92\network\admin\TNSNAMES.ORA里面定义过, 如果连接的是个别数据库,就不会报告这样的错误)
TNSNAMES.ORA 定义方式:
FJDB.SIIC.COM =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = fangjin)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = fjdb)
)
)
** or use the following command in dos state:
C: \ set oracle_sid = “jeandb”
C:\ sqlplus /nolog
SQL» connect sys/jeandb as sysdba;
3. How to create an oracle database in windows manually
Step1: setting environment var


相关文档:

06—PL/SQL编程

PL/SQL: Oracle中的SQL过程化编程语言
1. PL/SQL程序是由块结构构成,格式如下:
  [DECLARE
   --声明部分
 ]
  BEGIN
   -- 主体
   [EXCEPTION
     -- 异常处理块
   ]
  END;
  /  --块的结束
2.变量
简单变量: ......

SQL语句PART1

Oracle SQL(partI)
Data manipulation language(DML): select, insert, update, delete, merge.
Data definition language(DDL): create, alter, drop, rename, truncate, comment
Data control language(DCL): grant, revoke
Transaction control: commit, rollback, savepoint
Arithmetic Expressions:
+, -, *, / ......

SQL语句PART4

GRANT
Name
GRANT -- 定义访问权限
Synopsis GRANT { { SELECT | INSERT | UPDATE | DELETE | RULE | REFERENCES | TRIGGER }
    [,...] | ALL [ PRIVILEGES ] }
    ON [ TABLE ] tablename [, ...]
    TO { username | GROUP groupname | PUBLIC } [, ...] [ WI ......

SQL语句PART6

Differring Constraints:
Constraints can have the following attributes: DEFFERRABLE / NOT DEFFERRABLE,  INITIALLY DEFFERRED / INITIALLY IMMEDIATE.
e.g.:
alter table dept2 add constraint dept2_id_pk primary key (department_id) deferrable initially deferred; // deferring constraint on creation. ......

SQL语句PART7

Merge statement
function benefits: 1) provides the ability to conditionally update, insert or delete data into a database table. 2) performs an update if the row exists, and an insert if it is a new row. --> 1) avoids seperate updates, 2) increase performance and ease of use. 3) is useful in dat ......
© 2009 ej38.com All Rights Reserved. 关于E健网联系我们 | 站点地图 | 赣ICP备09004571号