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中开启了多于一个的数据库, 并且处于ＳＴＡＲＴＵＰ状态．此时要连接必须用: connect SYS/oracle＠SID AS SYSDBA, 必须在F:\oracle\ora92\network\admin＼ＴＮＳＮＡＭＥＳ．ＯＲＡ里面定义过， 如果连接的是个别数据库，就不会报告这样的错误)
ＴＮＳＮＡＭＥＳ．ＯＲＡ 定义方式：
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. Ｈow to create an oracle database in windows manually
Step1: setting environment var
相关文档：
第十一题：
有表students(name,class,grade),请用标准sql语句完成
name class grade
张三 数学 81
李四 语文 70
王五 数学 90
张三 语文 60
李四 数学 100
王五 语文 90
王五 英语 81
要求: 用sql语句输出各门功课都大于80分的同学姓名?
create table students (
name varchar(25),
class varchar(25),
grad ......
PL/SQL: Oracle中的SQL过程化编程语言
1. PL/SQL程序是由块结构构成，格式如下：
[DECLARE
--声明部分
]
BEGIN
-- 主体
[EXCEPTION
-- 异常处理块
]
END;
/ --块的结束
2.变量
简单变量： ......
Confirming granted privileges
Data Dictionary View Description
ROLE_SYS_PRIVS System privileges granted to roles
ROLE_TAB_PRIVS & ......
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 ......
