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
相关文档：
PL/SQL: Oracle中的SQL过程化编程语言
1. PL/SQL程序是由块结构构成，格式如下：
[DECLARE
--声明部分
]
BEGIN
-- 主体
[EXCEPTION
-- 异常处理块
]
END;
/ --块的结束
2.变量
简单变量： ......
1、对象类型规范
创建对象类型规范的语法如下
CREATE [OR REPLACE] TYPE [schema.] type_name
[AUTHID {CURRENT_USER|DEFINER}] AS OBJECT (
Attribute1 datatype,
[attribute2 datatype,…]
[method 1]
[method 2]);
/
其中AUTHID指示将来执行该方法时， ......
constraint Example:
1. grammer:
create table [schema.]table
(column datatype [DEFAULT expr]
[column_constraint], ...
[table_constraint] [,......]);
2. example of a column_level constraint:
create table empl ......
CREATE VIEW MYVIEW
AS
SELECT * from bjxxdiweb_database2007.dbo.bm_tongji
UNION ALL
SELECT * from aa.DBO.chen
select * into aa..chen from bjxxdiweb_database2007.dbo.bm_tongji where 1=2
说明：数据库A的表的字段名必须和数据库B的表的字段名相同，包括数据类型等。 ......
oracle tips
Exist的用法：
select gw.ndocid from
(select ndocid from wf_doc_gw_shouwen union select ndocid from wf_doc_gw_fawen) gw
where
not exists (select null from wf_doc_gw_sn sn where sn.ndocid=gw.ndocid)
2。把GW表和SN表里相同的NDOCID显示出来
select gw.ndocid from
(se ......
