学习笔记---Oracle操作总结

 

代码

-- ###################################################################################
/*
版本号: Oracle9i或者Oracle9g---i表示internet, g表示grid
iSqlplus (DBA) URL: http://localhost:5560/isqlplus(/dba)
完全卸载oracle: 卸载完成后, 手工删除项 HKEY_LOCAL_MACHINE/SOFTWARE/ORACLE
服务: OracleService实例名---Oracle核心服务
OracleOraDb10g_home1TNSListener---通过网络访问数据库
OracleOraDb10g_home1iSql*Plus---通过浏览器访问
端口号: Oracle---5560 Sql---1433,1434
登陆: 在cmd中输入 sqlplus; 系统用户名: system 或者 sysdba, 密码:安装时设置
强制登陆: 在cmd中输入 sqlplus / as sysdba
默认帐户: scott 密码:tiger, 默认是locked; hr 密码:tiger

Schema方法: 是与用户关联的表、视图、簇、过程和程序包等对象的命名的集合, 该方案schema在Oracle创建用户时自动创建.
Oracle数据库字典:
USER_*:用户视图，即用户schema中的视图
ALL_*：扩展用户视图，即用户可以访问的视图, 包含自己的用户视图和一个其他的可访问的视图
DBA_*：所有方案的视图

缩写:
DDL(Database Definition Language): 数据库定义语言
DML(Database Manipulation Language): 数据库操作语言
DCL(Database Control Language): 数据库控制语言
DTM(Database Trasaction Management): 数据库事物管理

知识概要:
|---1.查询Select
|
|---2.数据库定义语言DDL: 对表,视图等的操作, 包括create,drop,alter,rename,truncate
|
数据库操作--|---3.数据库操作语言DML: 对记录进行的操作, 包括insert,delete,update
|
|---2.数据库控制语言DCL: 对访问权限等的操作, 包括grant,revoke
|
|---2.数据库事物管理DTM: 对事物的操作, 包括commit,rollback,savepoint

约束: 主键(Primary Key)、外键(Foreign Key)、非空(not null)、唯一(unique)和检查(check)

事物的是由DML(insert,delete,update)开启的, 在Oracle中需要执行commit才能看到DML操作的结果;
而引起事物的提交或结束原因有:
1.DTM操作: commit,rollback,savepoint
2.系统崩溃宕机: 隐式rollback
3.正常: 隐式commit
4.DDL和DCL操作: DDL(create,drop,alter,rename,truncate)
DCL(grant,revoke)

备注:
Oracle中, 字符串和日期区分大小写, 且需要用单引号进行表示. 若要输出单引号, 用''表示一个单引号
Oracle中使用||拼接字符串, Sql中使用+拼接字符串
C#数据类型:
整数: sbyte,byte,short,ushort,int,uint,long,ulong
实数: float,double,decimal
字符: char,string
布尔: boolean
日期: datetime
对象: object
Oracle数据类型:
整数: number(38) --n表示位数
字符: char(2000), nchar(1000), varchar2(4000),nvarcahr2(2000), long
日期: date
Sql注入测试串: ' or 1=1 --
解决注入攻击的手段:
1. 不拼接字符串, 有字符串操作时, 要留心.
2. 使用参数进行赋值, 尽量使用强类型.
3. 对用户输入的字符进行过滤.
4. 若非用串的话(如:用户名登陆过程), 可以用代码先到数据库中查一下是否有这个名字, 没有则禁止操作
*/
-- ###################################################################################
-- 查询系统资源:
-- 常用命令
set linesize
set pagesize
clear screen
show user
show errors -- 显示错误, 如在存储过程中显示错误
edit -- --可以使用edit(ed)编辑刚才输入的语句, 编辑结束后用"/"提交
list -- 列出sqlplus缓冲区中的命令, 简写为l
begin
dbms_output.put_line( ' hello, world! ' ); -- Orcle中输出字符, 注意输出语句在begin和end之间, 且注意分号
end ;

-- 表及表信息
decribe user_tables
select * from user_tables
select table_name from all_tables

select procedure_name from user_procedures -- 查询存储过程

select * from user_sequences -- 查询序列
select * from all_sequences
select Seq_Test.nextval from dual -- 查询下一个递增序列的值
select Seq_Test.currval from dual -- 查询当前序列的值

-- 视图
select * from user_views

-- 查看约束名称、表名字、约束类型及字段名
select table_name, constraint_name, constraint_type, search_condition from User_Constraints -- 查看表约束
select table_name,constraint_name, column_name from user_cons_columns -- 查看列约束

select c.table_name,c.constraint_name, u.constraint_type, c.column_name
from user_cons_columns c inner join user_constraints u
on c.constraint_name = u.constraint_name

grant unlimited tablespace to scott -- 无法将表空间授予角色, 只能授予用户
revoke unlimited tablespace from scott

-- 用户及用户角色信息
show user
select * from all_users
select username from user_users
select username,default_tablespace from user_users -- 查看缺省用户表空间
select * from user_role_privs -- 查看用户具有的角色

-- 授权及权限
select * from user_sys_privs -- 授予用户的系统权限
select * from role_sys_privs -- 授予角色的系统权限
select * from role_tab_privs -- 授予角色的表的权限
select * from dba_sys_privs where grantee = ' SYS ' -- 查看'SYS'的具体权限, 注意大小写
select grantee,owner,table_name,privilege from dba_tab_privs -- 查看所有用户的权限
select name from sys.system_privilege_map -- 查看Oracle提供的系统权限
select * from user_tab_privs_made -- 用户对象创建时的权限
select * from user_tab_privs_recd -- 用户对象接收的权限
select * from user_col_privs_made -- 用户对象创建时列上的权限
select * from user_col_privs_recd -- 用户对象列接收的权限
-- 查看一个用户的所有系统权限(包含角色的系统权限)
select privilege from dba_sys_privs where grantee = ' RESOURCE '
union
select privilege from dba_sys_privs where grantee in (
select granted_role from dba_role_privs where grantee = ' RESOURCE '
)


select * from dba_roles

create user TestUser identitied by TestUser account unlock
grant create session to TestUser

create role TestUserRole
grant TestUserRole to scott
grant create session, create view , resource, connect to TestUserRole -- 通常给的用户权限
grant resource, connect to scott

drop user TestUser
revoke create seeion from hr

drop role TestUserRole
revoke resource,connect from TestUserRole

alter user hr identitied by tiger account unlock

-- ###################################################################################

-- 创建表, 约束类型: 主键、外键、唯一、check约束、非空约束
-- 注意事项: 序列不保证连续、关键字不必要连续、业务数据不适合作为关键字
drop table T_Event;
drop sequence seq_T_Event;
create sequence seq_T_Event start with 1 increment by 1 ;
create table T_Event(
seqid number ( 8 ),
constraint pk_T_Event primary key (seqid), -- 单独写约束, 可以方便添加联合主键(在seqid后便添加即可)
title varchar2 ( 200 ) constraint nn_T_Event_title not null ,
startdate date constraint nn_T_Event_startdate not null ,
enddate date constraint nn_T_Event_enddate not null ,
constraint ck_T_Event_enddate check (enddate > startdate),
detail varchar2 ( 500 ),
userid number ( 8 ),
constraint fk_T_Event_userid foreign key (userid) references T_UserInfo(seqid)
)


drop table T_UserInfo;
drop sequence seq_T_UserInfo;
create sequence seq_T_UserInfo start with 1 increment by 1 ;
create table T_UserInfo
(
seqid number ( 8 ),
constraint pk_T_UserInfo primary key (seqid),
username nvarchar2( 30 ) constraint nn_T_UserInfo_username not null ,
constraint uq_T_UserInfo_username unique (username),
create_data date default sysdate
)

-- 修改表结构
alter table T_Event
add userid number ( 8 )

alter table T_Event
drop column test

-- 修改表约束
alter table T_Event
add constraint fk_T_Event_userid foreign key (userid) references T_UserInfo(seqid)

-- ###################################################################################

-- 创建视图
create or replace view View_DepEmp
as
select last_name Given_Name,salary
from employees e inner join departments d on e.department_id = d.department_id

-- ###################################################################################

-- 新增(插入)数据(DML操作, 在commit之后才会提交)
insert into T_Event(seqid,title,startdate,enddate,detail,userid)
values (seq_T_Event.nextval, ' 吃饭 ' ,to_date( ' 2010-11-5 ' , ' yyyy-mm-dd ' ),
to_date( ' 2010-12-1 8:30 ' , ' yyyy-mm-dd hh-mi ' ), ' 有人请吃大餐, 不吃白不吃! ' , ' 1 ' );
insert into T_Event(seqid,title,startdate,enddate,detail,userid)
values (seq_T_Event.nextval, ' 喝酒 ' ,to_date( ' 11-5-2012 ' , ' mm-dd-yyyy ' ),
to_date( ' 13-12-2012 12:45 ' , ' dd-mm-yyyy hh-mi ' ), ' 有人请喝酒, 不喝白不喝! ' , ' 1 ' );
commit


insert into T_UserInfo(seqid,username)
values (seq_T_UserInfo.nextval, ' admin ' );
insert into T_UserInfo(seqid,username)
values (seq_T_UserInfo.nextval, ' user ' );
commit

-- 删除数据
truncate table T_Event -- 只删除表的数据
delete from T_Event where title = ' 喝酒 '
commit

-- 修改数据
update T_UserInfo set username = ' Client ' where username = ' user ' ; -- 注意set后边仍为=
commit

-- ###################################################################################

-- 查询数据
select distinct department_id, job_id from employees -- distinct
select department_id as "部门编号", job_id "职位编号" from employees -- 起别名的方式as和空格, 注意""
select department_id || ' 联合 ' || job_id as "唯一标识" from employees -- 别名双引号, 连接字符串用单引号
select first_name,last_name,salary from employees where salary >= 8000 and salary <= 10000
select first_name,last_name,salary from employees where salary between 8000 and 10000
select department_id, job_id from employees where department_id is null
-- 模糊查询呢: 通配符: %(0-任意字符), _(1个字符),[a,b,c](选择含a或b或c),[^a,b,c](选择不含a或b或c)
select first_name, last_name from employees where last_name like ' D% ' or last_name like ' E% '
select first_name, last_name from employees where last_name like ( ' D% ' ) or last_name like ( ' E% ' )
select first_name, last_name from employees where last_name between ' D ' and ' F ' -- 注意这里取左不取右
select first_name, last_name from employees where substr(last_name, 1 , 1 ) in ( ' D ' , ' E ' )SYSTEM

-- 嵌套查询(子查询): 分为单行子查询和多行子查询, 区别就是子查询的结果是单条记录还是结果集
-- 1. dual是系统的虚表, 可以用不需要From的select语句中
-- 2. rownum成为伪列, 用于生成序号.原理: 生成结果集后, 再加上序号
select next_day(sysdate, 3 ) from dual -- 返回下周2的日期, 周的计算从1开始

-- 利用伪列进行分页: 将子查询作为虚拟表再查询
-- 1. 输出前10条排序后的记录
select rownum,department_name from employees where rownum <= 10 -- 未排序
select rownum, department_name -- 排序后的记录, 因为select基本最后执行, 所以select两次
from (
select rownum, department_name
from departments
order by department_name
)
where rownum <= 10
-- 2. 输出前11-20条排序后的记录
select rn,department_name
from (
select rownum as rn, department_name
from (
select rownum, department_name
from departments
order by department_name
)
)
where rn between 11 and 20 -- rownum根据结果集来生成, 一直没有rownum=1的记录, 所以得不到11的记录

-- 单行子查询: =、>、<
select last_name,salary
from employees
where salary > ( select avg (salary) from employees) -- 子查询结果为单挑记录
-- 多行子查询: in(在结果集里面即可)、any(符合任一个即可)、all(完全符合才可)
select last_name,salary -- 在结果集中
from employees
where salary in ( select salary from employees where department_id = 110 )

select last_name,salary -- 大于所有的, 也就是大于最大的(替代mssql的top?)
from employees
where salary > all ( select salary from employees where department_id = 110 )

selet last_name,salary -- 大于任何一个, 也就是大于最小的
from employees
where salary > any ( select salary from employees where department_id = 110 )

-- ###################################################################################

-- 排序
select distinct department_id, job_id from employees order by job_id desc -- 默认是升序asc

-- ###################################################################################

-- 聚合函数
select sum ( [ sid ] ) from T_StuScore
select count ( [ sid ] ) from T_StuScore -- count(*)表示记录数, 而count(字段)忽略掉null值
select avg ( [ sid ] ) from T_StuScore
select max ( [ sid ] ) from T_StuScore
select min ( [ sid ] ) from T_StuScore

select distinct (department_id), job_id from employees
select distinct department_id, job_id from employees

-- ###################################################################################

-- 分组函数, where用于对记录的筛选, having用于对组的筛选, 并且组函数将忽略结果为null的字段
select gender,Counter = count ( * ) from T_Stuinfo group by gender
select gender,Counter = count ( * ) from T_Stuinfo group by gender having count ( * ) >= 2

-- ###################################################################################

-- 表连接: 笛卡尔积(m*n条记录), 内连接, 外连接(左外连接、右外连接、全外连接), 自连接
-- 内连接: 先从m和n中选择, 然后再连接
select sname,sid,cid,score
from T_StuInfo s inner join T_StuScore c on s.sid = c.sid

-- 左外连接(左连接): 内连接 + 左表剩余记录(右表记录置null)
select sname,sid,cid,score
from T_StuInfo s left join T_StuScore c on s.sid = c.sid

-- 右外连接(右连接): 内连接 + 右表剩余记录(左表记录置null)
select sname,sid,cid,score
from T_StuInfo s right join T_StuScore c on s.sid = c.sid

-- 全外连接(全连接): 内连接 + 左表剩余记录(右表记录置null) + 右表剩余记录(左表记录置null)
select sname,sid,cid,score
from T_StuInfo s full outer join T_StuScore c on s.sid = c.sid

-- 自连接(全连接): 本表与本表连接, 可以是外连接或内连接
select sname,sid,cid,score
from T_StuInfo s inner join T_StuInfo c on s.sid = c.sid

-- ###################################################################################

-- 函数: Oracle中的函数分为单行函数和组函数两种. 组函数用于Group by字句中.
-- 单行函数包括: 字符函数、数字函数、日期函数、Sysdate以及一些其他函数.
-- 系统函数 注意: 如果要在sqlplus中看到输出, 需要先执行set serveroutput on
begin
-- nvl('expression',value)函数, expression为null便用后便的value代替
dbms_output.put_line(nvl( null , 0 ));
dbms_output.put_line(to_char(sysdate));
dbms_output.put_line(to_number( 123.567 ));
dbms_output.put_line(to_char(to_date( ' 2010-08-09 ' , ' yyyy-mm-dd ' )));
end ;

-- 字符函数
begin
dbms_output.put_line( lower ( ' I love CHINA! ' )); -- 大写变小写
dbms_output.put_line( upper ( ' You don '' t even have a dog! ' )); -- 小写变大写
dbms_output.put_line(initcap( ' poo poo! my sweaty! ' )); -- 每单词首字母大写其余小写
dbms_output.put_line(length( ' Rocho^_^J ' )); -- 求串长
dbms_output.put_line(substr( ' I love CHINA! ' , 3 , 4 )); -- 求子串, Oracle中下表从1开始
dbms_output.put_line(instr( ' I love CHINA! ' , ' CHINA ' )); -- 求字串的下表
dbms_output.put_line(instr( ' IN love with CHINA! ' , ' IN ' , 5 )); -- 求第5位后的字串的下表
dbms_output.put_line(concat( ' I love ' , ' you ' )); -- 串连接, 不常用. 可用||代替
dbms_output.put_line( ' I love ' || ' you ' ); -- 串连接||
dbms_output.put_line(lpad( ' I love CHINA! ' , 20 , ' * ' )); -- 填充, 15为总长度, 不够补*
dbms_output.put_line(rpad( ' I love CHINA! ' , 20 , ' * ' ));
dbms_output.put_line(trim( ' C ' From ' Carton MovieC ' )); -- 去除两边的一个字符
dbms_output.put_line( replace ( ' I love CHINA ' , ' CHINA ' , ' you ' )); -- 替换子串
end ;

-- 数字函数
begin
dbms_output.put_line( round ( 156.26789 , 2 )); -- 四舍五入, 2为保留2位小数
dbms_output.put_line( round ( 156.26789 , - 2 )); -- 四射五入, -2为保留整数, 从.开始
dbms_output.put_line(trunc( 123.4567 , 3 )); -- 截取.后多少位, 类似上边, 但是不进位
dbms_output.put_line( 16 mod 3 );
end ;

-- 日期函数: 中文系统下, 默认日期显示格式为: 日-月-年
-- 通过alter Session set nls_date_format = 'yyyy-mm-dd'
begin
dbms_output.put_line(to_char(sysdate, ' yyyy-mm-dd hh:mi:ss ' )); -- DateTime.Now
dbms_output.put_line(to_char(sysdate, ' yyyy ' )); -- 年份
dbms_output.put_line(to_char(sysdate, ' mm ' )); -- 月份
dbms_output.put_line(to_char(sysdate, ' dd ' )); -- 天书
dbms_output.put_line(to_char(sysdate, ' d ' )); -- 星期几, 结果需要-1
dbms_output.put_line(to_char(sysdate, ' ddd ' )); -- 一年中的第几天
dbms_output.put_line(to_char(sysdate, ' hh ' )); -- 小时
dbms_output.put_line(to_char(sysdate, ' hh12 ' )); -- 12小时制小时
dbms_output.put_line(to_char(sysdate, ' hh24 ' )); -- 24小时制小时
dbms_output.put_line(to_char(sysdate, ' mi ' )); -- 分
dbms_output.put_line(to_char(sysdate, ' ss ' )); -- 秒
dbms_output.put_line(to_char(sysdate, ' q ' )); -- 季度
dbms_output.put_line(to_char(sysdate + 2 )); -- 加减2，得到的前或后两天
dbms_output.put_line(to_char(
months_between(
to_date( ' 2011-08-08 ' , ' yyyy-mm-dd ' ),
to_date( ' 2010-11-9 ' , ' yyyy-mm-dd ' )
)
)
); -- 相差几个月
dbms_output.put_line(to_char(
add_months(to_date( ' 2011-08-08 ' , ' yyyy-mm-dd ' ), 3 )
)); -- 相差几个月
dbms_output.put_line(to_char(
add_months(to_date( ' 2011-08-08 ' , ' yyyy-mm-dd ' ), 3 )
)); -- 相差几个月
dbms_output.put_line(to_char(
last_day(to_date( ' 2011-08-08 ' , ' yyyy-mm-dd ' ))
)); -- 该月最后一天
dbms_output.put_line(to_number(
to_char(
to_date( ' 2011-8-8 ' , ' yyyy-mm-dd ' ), ' yyyy ' )
)
); -- 日期变数字
end ;

select next_day(sysdate, 3 ) from dual -- 返回下周2的日期, 周的计算从1开始

-- ###################################################################################

-- 范式: 1NF: 原子性, 2NF: 单主键, 3NF: 去除传递依赖, BCNF: 决定每个记录的因素都包含关键字, 但不是关键字的子集
-- E-R模型(表间关系): 1对1： 任何一个表添加外键; 1对多: 在多的一方添加外键; 多对多: 需要第三个表, 添加前两表的外键

-- ###################################################################################

-- Oracle程序块: declare、begin、exception、end四个关键字, 注意: end后面的";"不能省略, 且打开输出set serveroutput on
-- 系统变量:
sqlcode -- 在异常处理中, 返回当前的错误号
sqlerrm -- 在异常处理中, 返回当前的错误信息
% rowcount -- 获得sql命令影响的行数
% rowtype -- 获得表或视图的记录类型
% type -- 变量类型参考表或视图中字段的类型
% found -- 用于游标, 表示是否找到数据
% notfound -- 用于游标, 表示是否没有找到

-- 自定义变量
declare
v_name nvarchar2( 30 );
v_email nvarchar2( 50 ) default ' webmaster@google.com ' ;
begin
v_name : = ' 张三 ' ;
dbms_output.put_line(v_name);
dbms_output.put_line(v_email);
exception -- 可省略
when others then
dbms_output.put_line( ' 其他异常 ' );
end ;

-- 条件表达式: 相等为:=, 比较相等为=
declare
v_name nvarchar2( 30 ) : = ' 张三 ' ;
v_pass nvarchar2( 10 ) : = ' zhangsan ' ;
v_inputname nvarchar2( 30 );
v_inputpass nvarchar2( 10 );
begin
v_inputname : = ' 张三 ' ;
v_inputpass : = ' zhangsan ' ;
if v_inputname <> v_name then
dbms_output.put_line( ' 不存在用户名 ' || v_inputname);
elsif v_inputpass != v_pass then
dbms_output.put_line( ' 密码错误! ' );
elsif v_inputname = v_name and v_inputpass = v_pass then
dbms_output.put_line( ' 登陆成功! ' );
else
dbms_output.put_line( ' 用户名和密码不匹配! ' );
end if ;
exception
when others then
dbms_output.put_line( ' 系统异常! ' );
end ;

-- 循环
declare -- 输出fibonacci前10项
v_num1 number ( 5 ) : = 1 ;
v_num2 number ( 5 ) : = 1 ;
v_num3 number ( 5 ); -- 前两个数的和
v_i number ( 5 ) : = 0 ; -- 控制循环次数
begin
dbms_output.put(v_num1); -- 输出初始的1,1
dbms_output.put( ' ' || v_num2);
loop
exit when v_i >= 8 ; -- 前两个已有
v_num3 : = v_num1 + v_num2;
dbms_output.put( ' ' || v_num3);
v_num1 : = v_num2;
v_num2 : = v_num3;
v_i : = v_i + 1 ;
end loop;
dbms_output.put_line( '' ); -- 一定要有put_line才能显示put的信息
-- 省略exception
end ;

-- case when
-- 搜索case when(用于一个范围), Oracle中不支持?
-- 简单case when(类似swtich, 用于一个定值)
select
case table_name
when ' JOBS ' then ' 工作表 '
when ' REGIONS ' then ' 地区表 '
when ' DEPARTMENTS ' then ' 部门表 '
else ' 其他表 '
end
from user_tables

declare
v_gender number ( 1 ) : = 0 ;
begin
case v_gender
when 0 then dbms_output.put_line( ' 男 ' );
when 1 then dbms_output.put_line( ' 女 ' );
else dbms_output.put_line( ' 暂时未知 ' );
end case ;
end ;

-- 查询处理: 单行结果可以直接处理, 而多行结果需要用游标处理
-- 单行结果: 必须有一行结果, 不能是多行也不能是空行
declare
v_title nvarchar2( 200 );
v_detail T_Event.detail % type; -- %type参照表或视图的字段类型
begin
select title,detail into v_title,v_detail
from T_Event
where seqid = 1 ;
dbms_output.put_line( ' 记事详情: ' || v_title || ' ' || v_detail);
end ;
-- 定义记录类型
declare
type T_UserInfo is record(
seqid number ( 8 ),
username nvarchar2( 30 ),
create_data date
);
v_row T_UserInfo;
begin
select seqid,username,create_data into v_row
from T_UserInfo
where seqid = 3 ;
dbms_output.put_line( ' 用户详情: 序号< ' || v_row.seqid || ' > , 用户姓名: < ' || v_row.username ||
' > , 用户创建时间: < ' || v_row.create_data || ' > . ' );
end ;
-- 参考记录的类型
declare
v_row T_UserInfo % rowtype; -- 参考原表的记录类型
begin
select seqid,username,create_data into v_row
from T_UserInfo
where seqid = 3 ;
dbms_output.put_line( ' 用户详情: 序号< ' || v_row.seqid || ' > , 用户姓名: < ' || v_row.username ||
' > , 用户创建时间: < ' || v_row.create_data || ' > . ' );
end ;

-- 多行结果--->游标: 1. 定义游标cursor 2. 打开游标open 3. 从游标中读取fetch 4. 判断游标状态 5. 关闭游标close
declare
v_row T_UserInfo % rowtype; -- 参考原表的记录类型
cursor v_cs is select * from T_UserInfo; -- 1. 定义游标
begin
open v_cs; -- 2. 打开游标
loop
fetch v_cs into v_row; -- 3. 从游标读取数据
exit when v_cs % notfound; -- 4. 判断游标状态
dbms_output.put_line( ' 序号< ' || v_cs % rowcount || ' > ' );
dbms_output.put_line( ' 用户姓名: < ' || v_row.username || ' > ' );
dbms_output.put_line( ' 用户创建时间: < ' || v_row.create_data || ' > ' );
end loop;
close v_cs; -- 5. 关闭游标
end ;

-- 游标2：
declare
type type_cs is ref cursor ; -- 系统游标
v_cs type_cs;
v_title T_Event.title % type;
v_startdate date;
v_enddate date;
v_detail T_Event.detail % type;
begin
GetNoticesByDateParametersOut(to_date( ' 2010-7-8 ' , ' yyyy-mm-dd ' ),v_cs); -- 掉存储过程
loop
fetch v_cs into v_title, v_startdate, v_enddate, v_detail;
exit when v_cs % notfound;
dbms_output.put_line( ' 记事详情: 标题< ' || v_title || ' > , 开始时间: < ' || v_startdate ||
' > , 结束时间: < ' || v_enddate || ' > , 内容: < ' || v_detail || ' >. ' );
end loop;
close v_cs;
end ;

-- ###################################################################################

-- 事物: 事物的特性ACID(一致性(Consistency)、原子性(Atomicity)、隔离性(Isolation)、持久性(Durability))
-- Oracle中的事物由任意一条DML语句开始, 不需要显示的开始事物
declare
v_username nvarchar2( 30 );
v_title nvarchar2( 200 );
v_startdate date;
v_enddate date;
v_detail nvarchar2( 500 );
v_userid number ( 8 );
begin
v_username : = ' 李四 ' ;
v_title : = ' 喝喜酒 ' ;
v_startdate : = to_date( ' 2010-11-05 ' , ' yyyy-mm-dd ' );
v_enddate : = to_date( ' 2010-12-05 ' , ' yyyy-mm-dd ' );
v_detail : = ' 准时到, 备好红包 ' ;
insert into T_UserInfo(seqid,username) values (seq_T_UserInfo.nextval,v_username);
insert into T_Event(seqid,title,startdate,enddate,detail,userid)
values (seq_T_Event.nextval,v_title,v_startdate,v_enddate,v_detail,seq_T_UserInfo.currval);
commit ;
exception -- 异常处理部分如果不写, 则异常将升级到程序的调用环境中处理
when others then
rollback ;
dbms_output.put_line( ' 数据写入过程出错, 操作已回滚! ' );
end ;

-- 带异常控制的事物
-- raise_application_error(-20999,'XXXXX')用于抛出xxxx的自定义异常, 异常号必须在-20000~-20999之间
begin
-- raise zero_divide; --即使程序没有错误, 也可以手动抛出异常
insert into T_UserInfo(seqid,username) values (seq_T_UserInfo.nextval, ' 王五 ' );
insert into T_Event(seqid,title,startdate,enddate,detail,userid)
values (seq_T_Event.nextval, ' 生日 ' ,
to_date( ' 2010-11-05 ' , ' yyyy-mm-dd ' ),
to_date( ' 2010-12-05 ' , ' yyyy-mm-dd ' ),
' 准备蛋糕 ' ,
seq_T_UserInfo.currval);
commit ;
exception -- 异常处理部分如果不写, 则异常将升级到程序的调用环境中处理
-- Orale内部定义的异常, 资源忙错误号为: -54; sqlcode和sqlerrm用来返回当前错误号和错误信息
when no_data_found then rollback ;dbms_output.put_line( ' 错误: 没有找到数据 ' ); -- 错误号:-1403
when too_many_rows then rollback ;dbms_output.put_line( ' 错误: 返回了多行数据! ' ); -- 错误号为: -1422
when invalid_cursor then rollback ;dbms_output.put_line( ' 错误: 无效的游标 ' );
when zero_divide then rollback ;dbms_output.put_line( ' 错误: 除0错误 ' ); -- 错误号位: -1476
when dup_val_on_index then
rollback ;
dbms_output.put_line( ' 错误: 唯一索引不能有重复值! ' );
dbms_output.put_line( ' 数据写入过程出错, 操作已回滚! ' || ' 当前错误号: ' || sqlcode || ' 当前错误信息: ' || sqlerrm);
when others then
rollback ; -- 手动抛出异常, 程序将中断执行, 因此提前rollback
raise_application_error( - 20011 , ' 这是自定义的错误信息! ' ); -- 抛出自定义异常
end ;

-- ###################################################################################

-- 索引: 列中包含大范围值、列中包含大量null、经常被用户查询的where或join的连接条件、表中数据多, 但常用的行少于2%-4%
create index lower_Employees_Last_name_Idx on employees( lower (last_name)) -- 为提高查询效率, 先转一下小写

select * from user_indexes -- 查询索引
select * from user_ind_columns -- 索引中列的数据字典

-- ###################################################################################

-- 存储过程(Stored Procedure): 只需要定义参数的类型, 而不能指定参数的宽度,in、out、in out, SP用来存储程序块
-- 带参数的
create or replace procedure SP_ShowFibonacci(
p_num in number
)
is
v_num1 number ( 5 ) : = 1 ;
v_num2 number ( 5 ) : = 1 ;
v_num3 number ( 5 ) : = 0 ;
v_i number ( 5 ) : = 0 ;
begin
dbms_output.put(v_num1 || ' ' );
dbms_output.put(v_num2 || ' ' );
loop
exit when v_i >= p_num - 2 ;
v_num3 : = v_num1 + v_num2;
dbms_output.put(v_num3 || ' ' );
v_num1 : = v_num2;
v_num2 : = v_num3;
v_i : = v_i + 1 ;
end loop;
dbms_output.put_line( '' ); -- 显示结果
end Sp_ShowFibonacci;
-- 调用: sqlplus中, exec ShowFibonacci(15);
begin
ShowFibonacci( 15 ); -- 在程序块中调用存储过程不需要exec
end ;


-- 带输入参数和普通输出参数, in表示传入参数, out表示输出参数, returning用于返回刚刚插入的记录
create or replace procedure SP_InsertAndReturnPKFromT_Event(
p_pkid out number , -- 输出参数
p_title in nvarchar2,
p_startdate in date,
p_enddate date, -- 默认就是in
p_detail in nvarchar2,
p_userid in number
)
is
-- v_pkid number(8);
begin
-- v_pkid := seq_t_event.curral; --暂存currval
insert into T_Event(seqid,title,startdate,enddate,detail,userid)
values (seq_t_event.nextval,p_title,p_startdate,p_enddate,p_detail,p_userid)
returning seqid into p_pkid; -- 注意: returning是insert的子句, 用于返回刚插入的记录, 所以前面无分号
-- p_pkid := seq_t_event.currval; --这种返回方法, 需要在插入前缓存currval
commit ;
exception
when others then
rollback ;
end InsertAndReturnPKFromT_Event;
-- 调用: 带输出参数的存储过程在Sqlplus中调用也需要写程序块
-- 程序块调用, 不需要exec
declare
v_pkid number ( 8 );
begin
SP_InsertAndReturnPKFromT_Event(v_pkid, ' 吃饭 ' ,to_date( ' 2010-11-07 ' , ' yyyy-mm-dd ' ),
to_date( ' 2010-12-05 ' , ' yyyy-mm-dd ' ), ' 带红包 ' , 1 );
dbms_output.put_line(v_pkid);
end ;


-- 带输入参数和系统引用输出游标
create or replace procedure SP_GetEVentByDate(
p_date in date,
p_userid in number ,
p_cs out sys_refcursor
)
is
v_cs sys_refcursor; -- 1. 定义游标
begin
open v_cs -- 2. 打开游标
for
select title,startdate,enddate,detail,userid
from T_Event
where to_date(to_char(startdate, ' yyyy-mm-dd ' ), ' yyyy-mm-dd ' ) = p_date and userid = p_userid;
-- startdate要去掉时间, 才能查到多条记录. to_char之后再to_date即可
-- 3. 将查询结果以游标输出
p_cs : = v_cs;
end GetEventByDate;
-- 调用
declare
v_cs sys_refcursor;
v_title nvarchar2( 200 );
v_startdate date;
v_enddate date;
v_detail nvarchar2( 500 );
v_userid number ( 8 );
begin
-- 程序块使用游标, 不需要加exec
SP_GetEVentByDate(to_date( ' 2010-11-05 ' , ' yyyy-mm-dd ' ), 1 ,v_cs);
-- 使用输出游标, 也不需要打开游标
loop
fetch v_cs into v_title,v_startdate,v_enddate,v_detail,v_userid;
exit when v_cs % notfound;

dbms_output.put_line(v_title || v_startdate || v_enddate || v_detail || v_userid);
end loop;
close v_cs; -- 关闭游标
end ;


-- 通过存储过程插入数据
create or replace procedure SP_NewNoticeItem(
p_date in date,
p_start in date,
p_end in date,
p_title in NoticeItem.title % type,
p_content in NoticeItem.content % type
)
is
v_count calender.itemcount % type;
v_calender_dateid calender.dateid % type;
begin
-- 判断calender中是否有相应的记录
select nvl( sum (itemcount), 0 ), sum (dateid) into v_count, v_calender_dateid
from calender
where dateitem = p_date;

-- 如果有, 则更新数量, 这里同样也需要暂存dateid, 以便在noticeitem表中进行添加
if v_count > 0 then
update calender set itemcount = v_count + 1 -- update set之后是采用=号
where dateitem = p_date;
else
-- 如果无, 则增加新纪录
-- v_calender_dateid := seq_calender.nextval; --不能直接使用sequence, 因多人访问, 需要用局部变量暂存sequence
select seq_calender.nextval into v_calender_dateid from dual; -- 以上表达式的另外一种写法
insert into calender(dateid,dateitem,itemcount)
-- values(seq_calender.nextval, p_date, 1);
values (v_calender_dateid,p_date, 1 );
end if ;
-- 在NoticeItem表中增加活动的内容
insert into NoticeItem(itemid, dateid, start_time, end_time, title, content)
-- values(seq_noticeitem.curval, seq_calender.curval) --因为多人访问, 所以seq_calender.NextVal可能被改变, 需暂存
values (seq_noticeitem.nextval, v_calender_dateid, p_start, p_end, p_title, p_content);
commit ;
-- 如果中间有异常, 则rollback
exception
when others then
rollback ;
end SP_NewNoticeItem;

-- ###################################################################################

-- 函数: 与存储过程相比, 函数必须有返回类型, 内部应只对数据进行运算, 避免在函数中处理记录--用来存储程序块
create or replace function fn_truncdate(
p_date date
)
return date
is
result date;
begin
result : = to_date(to_char(p_date, ' yyyy-mm-dd ' ), ' yyyy-mm-dd ' );
return result;
exception
when others then
dbms_output.put_line( ' 截取日期出现错误 ' );
end ;
-- 调用
select fn_truncdate(to_date( ' 1985-11-11 8:30 ' , ' yyyy-mm-dd hh-mi ' )) from dual

-- ###################################################################################

-- 触发器: 分为基于表(before,after)和基于视图(instead of). :new表示新纪录, :old表示旧记录.
-- 还可以通过deleting、inserting和updating获得引起触发器的动作
-- 视图存储的是Sql语句, 所以通常只对表进行DML操作的触发器
create or replace trigger tg_noticeitem
after delete on noticeitem
for each row
declare
v_count calender.itemcount % type;
begin
-- 触发器中的隐士参数(:new 代表操作之后的记录 :old 代表操作之前的记录)
-- (:old.dateid)表示noticeitem中的外键

-- 读取calender表中的活动的数量itemcount
select itemcount into v_count
from calender
where dateid = :old.dateid;

if v_count > 1 then
update calender
set itemcount = itemcount - 1
where dateid = :old.dateid;
else -- 注意elsif的写法
delete from calender
where dateid = :old.dateid;
end if ;
end tg_noticeitem;


-- 创建监视表
drop table T_Monitor
create table T_Monitor(
table_name nvarchar2( 30 ),
constraint pk_T_monitor primary key (table_name),
count number ( 8 ),
create_date date default (sysdate)
)

insert into T_Monitor(table_name, count ) values ( ' T_EVENT ' , 0 )
commit

create or replace trigger tg_monitor -- 触发器没有参数
after insert or delete or update on T_EVENT
-- for each row --默认为statement level表示语句级, 表示一条语句执行一次, 而each row表示每行
declare

begin
if deleting then
dbms_output.put_line( ' 删除操作! ' );
elsif inserting then
dbms_output.put_line( ' 插入操作! ' );
elsif updating then
dbms_output.put_line( ' 更新操作! ' );
end if ;

update T_Monitor set count = count + 1
where table_name = ' T_EVENT ' ;
end tg_monitor;

 

 

 

//Sql语句注入模拟代码:

代码

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace SqiInjection
{
class Program
{
static void Main( string [] args)
{

// 1. 连接串, 通常写在配置文件中:
string constr = " Data Source=Orcl;User ID=system;Password=sa " ;

// 2. 连接对象
using (Oracle.DataAccess.Client.OracleConnection connection
= new Oracle.DataAccess.Client.OracleConnection(constr))
{
Console.Write( " 请输入用户名: " );
string name = Console.ReadLine();
Console.Write( " \n请输入id: " );
string id = Console.ReadLine();
// 3. 命令对象
string query1 = " select * from T_UserInfo where UserName= ' " + name + " ' and seqid = ' " + id + " ' " ;
string praquery = @" select * from T_UserInfo where UserName=:username and seqid =:seqid " ;

Oracle.DataAccess.Client.OracleCommand command = new Oracle.DataAccess.Client.OracleCommand();
command.Connection = connection;
command.CommandType = System.Data.CommandType.Text;

// 参数对象
Oracle.DataAccess.Client.OracleParameter v_username
= new Oracle.DataAccess.Client.OracleParameter( " :username " , Oracle.DataAccess.Client.OracleDbType.NVarchar2, 30 );
Oracle.DataAccess.Client.OracleParameter v_seqid
= new Oracle.DataAccess.Client.OracleParameter( " :seqid " , Oracle.DataAccess.Client.OracleDbType.NVarchar2, 30 );

v_username.Value = name;
v_seqid.Value = id;


try
{
connection.Open();
Console.WriteLine( " 连接已建立! " );
command.CommandText = query1;
getSelectResult(command);

Console.WriteLine( " \n---------------使用参数登陆-----------------\n " );
command.CommandText = praquery;
command.Parameters.Add(v_username);
command.Parameters.Add(v_seqid);
getSelectResult(command);
}
catch (Oracle.DataAccess.Client.OracleException oex)
{
Console.WriteLine( " 发现异常, 异常信息为: " , oex);
}
finally
{
Console.WriteLine( " 连接已关闭! " );
}

}
}

private static void getSelectResult(Oracle.DataAccess.Client.OracleCommand command)
{
if (command.ExecuteScalar() != null )
{
Console.WriteLine( " 登陆已成功! " );
}
else
{
Console.WriteLine( " 登陆失败! " );
}
}
}
}

 

 

//批量删除多张表

DECLARE
  I INTEGER;
BEGIN
  FOR TODROP IN (SELECT OBJECT_NAME
                   FROM USER_OBJECTS
                  WHERE OBJECT_TYPE = 'TABLE' and  created > to_date('2014-06-01 00:00:00','yyyy-mm-dd hh24:mi:ss'))
  LOOP
     --EXECUTE IMMEDIATE 'drop table ' || TODROP.OBJECT_NAME || '';
     dbms_output.put_line('drop table "' || TODROP.OBJECT_NAME || '";');
  END LOOP;
END;

View Code

 

 

//创建用户和表空间

Oracle建立表空间和用户                 
 
[sql] view plain copy
建立表空间和用户的步骤：  
用户  
建立：create user 用户名 identified by "密码";  
授权：grant create session to 用户名;  
            grant create table to  用户名;  
            grant create tablespace to  用户名;  
            grant create view to  用户名;  

[sql] view plain copy
表空间  
建立表空间(一般建N个存数据的表空间和一个索引空间)：  
create tablespace 表空间名  
datafile ' 路径(要先建好路径)\***.dbf  ' size *M  
tempfile ' 路径\***.dbf ' size *M  
autoextend on  --自动增长  
--还有一些定义大小的命令，看需要  
 default storage(  
 initial 100K,  
 next 100k,  
);  
[sql] view plain copy
例子：创建表空间  
create tablespace DEMOSPACE   
datafile 'E:/oracle_tablespaces/DEMOSPACE_TBSPACE.dbf'   
size 1500M   
autoextend on next 5M maxsize 3000M;  
删除表空间  
drop tablespace DEMOSPACE including contents and datafiles  

[sql] view plain copy
用户权限  
授予用户使用表空间的权限：  
alter user 用户名 quota unlimited on 表空间;  
或 alter user 用户名 quota *M on 表空间;  

完整例子：
[sql] view plain copy
--表空间  
CREATE TABLESPACE sdt  
DATAFILE 'F:\tablespace\demo' size 800M  
         EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;   
--索引表空间  
CREATE TABLESPACE sdt_Index  
DATAFILE 'F:\tablespace\demo' size 512M           
         EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;       
  
--2.建用户  
create user demo identified by demo   
default tablespace demo;  
   
--3.赋权  
grant connect,resource to demo;  
grant create any sequence to demo;  
grant create any table to demo;  
grant delete any table to demo;  
grant insert any table to demo;  
grant select any table to demo;  
grant unlimited tablespace to demo;  
grant execute any procedure to demo;  
grant update any table to demo;  
grant create any view to demo;  
[sql] view plain copy
--导入导出命令     
ip导出方式： exp demo/demo@127.0.0.1:1521/orcl file=f:/f.dmp full=y  
exp demo/demo@orcl file=f:/f.dmp full=y  
imp demo/demo@orcl file=f:/f.dmp full=y ignore=y  

View Code

 

//导入指定用户的表空间

oracle 如何导入dmp文件到指定表空间
1. 打开工具Oracle SQL Plus 以dba身份登录sys用户
user: sys
password: sys 
主机字符串(H)：orcl as sysdba

2. 创建用户并指定表空间  
--create user 用户名 identified by 密码 default tablespace 缺省表空间 Temporary tablespace 临时表空间;
drop user jandardb cascade; 
create user jandardb identified by jandardb; 
alter user jandardb default tablespace jandardb;  
grant connect,resource,dba to jandardb;  --grant
connect,resource,dba to 用户名;
revoke unlimited tablespce from jandardb;     --revoke
unlimited tablespace from 用户名;
alter user jandardb quota 0 on users;      --alter user 用户名 quota 0 on Users; 
alter user jandardb quota unlimited on jandardb;   --alter user 用户名 quota unlimited on 用户缺省表空间;

3. 使用imp工具导入dmp数据文件
imp jandardb/jandardb@orcl file=c:\jandardb.dmp fromuser=jandardb touser=jandardb log=c:\log.txt                 




数据库中用户try的数据一直放在system表空间中；
今天把该用户的所有数据exp到文件try.dmp中，准备再导入到另一个测试数据数据中的test用户中，同时放在test表空间中.

1、在第一个数据库导出数据：exp try/try wner=try file=/try.dmp log=try.log
2、将try.dmp ftp到第二个数据库所在主机上
3、在第二个数据库导入数据：imp test/test fromuser=try touser=test file=/try.dmp log=test.log
但是导完后发现数据任然被导入到了system表空中。       
后通过查询后得知，要成功导入其他表空间需要:
1、先将test用户在system空间中的UNLIMITED TABLESPACE权限回收：REVOKE UNLIMITED TABLESPACE FROM test 
2、设置默认表空间：alter user test default tablespace  test  
3、设置默认的表空间无限配额：alter user test quota unlimited on test 
4、设置特斯通用户对其他表空间的quota为0：alter user test quota 0 on system。。。。。。。  

再重新导入try.dmp，这是数据全部导入到test表空中了。

View Code

 

 

 

 

//Oracle数据库导入导出

删除用户 
drop user monitor cascade; 
删除表空间和数据文件 
drop tablespace monitor_ts including contents and datafiles; 



创建用户 
create user monitor identified by monitor; 
创建表空间 
create tablespace ts_wangf datafile 'C:\oracle\product\10.2.0\tablespaceBIMS\monitor-data.dbf' size 100m autoextend on; 
将表空间分配给用户 
alter user monitor default tablespace monitor_ts; 
给用户授权 
//grant create session,create table,create view,unlimited tablespace to wangf; 
grant dba to monitor; 



//============================imp导入DMP文件到指定表空间所需作的操作=============================================================================================
找了一个几百万行数据的库，准备导入的本地Oracle中： 
SAM用户的缺省表空间是SAM，但是数据却导入到了system表空间。Google了一下，应该这样做： 
1.收回unlimited tablespace权限revoke unlimited tablespace from sam; 
2.设置缺省表空间alter user sam default tablespace sam; 
3.设置SAM表空间的unlimited配额alter user sam quota unlimited on sam; 
4.设置其他表空间的0配额alter user sam quota 0 on system; 

最后在cmd中执行（不要进入sqlplus）：imp wangf/wangf@orcl file='D:\1\内部资源\河北联通BMIS管理平台\数据库结构\BimsManager.DMP' fromuser=monitor touser=wangf 
//===============================================================================================================================================================





我将公司oracle9i中的BimsManager数据库导入我自己的10G的数据库，所做的事： 
1、查看了导出的日志文件，为全库导出，即full=y,而fromuser=monitor 
2、创建BimsManager数据库 
3、创建用户monitor 
create user monitor identified by monitor; 
4、创建表空间(必须要 autoextend on，不然报出表空间配额不够的错,估计是因为导出时的表空间比较大) 
create tablespace monitor_ts datafile 'C:\oracle\product\10.2.0\tablespaceBIMS\monitor-data.dbf' size 100m autoextend on; 
  5、将表空间分配给用户 
  alter user monitor default tablespace monitor_ts; 
  分配之后查看： 
  select username,default_tablespace from dba_users; 
  6、给用户monitor授予dba的权限(因为导出时的monitor用户貌似是dba权限) 
  grant dba to monitor; 
  7、接下来的三条语句都与将DMP文件导入到指定表空间(monitor的表空间monitor_ts)有关，quota是配额 
  revoke unlimited tablespace from monitor; 
  alter user monitor quota unlimited on monitor_ts; 
  alter user monitor quota 0 on system; 
  alter user monitor quota unlimited on monitor_ts;
  8、很重要的一步：删除sysman用户的一个JOB，不然导入的时候最后会报一个违反唯一性约束的错 
  因为导出的9i中的monitor用户占了JOB_ID为1，而10G中sysman占用了JOB_ID是1 
  
  查看系统job：select job from dba_jobs: 
  删除job：以sysman登录sqlplus，首先执行:exec dbms_job.remove(1); 
                                 再执行：commit; 
  
  9、最后在cmd中执行（不要进入sqlplus）： 
  imp monitor/monitor@bimsmana file='D:\1\内部资源\河北联通BMIS管理平台\数据库结构\BimsManager.DMP' fromuser=monitor touser=monitor 
  
  
  最后说一句：oracle的提示“成功终止导入”其实意思是“成功完成导入”，终止是完成的意思。。。。。。。。。。。。 

View Code

 

首先运行 cmd

然后：

C:\Documents and Settings\wzq>imp
Import: Release 10.2.0.1.0 - Production on 星期日 11月 7 13:29:39 2010
Copyright (c) 1982, 2005, Oracle.  All rights reserved.

分别输入 用户名  密码

Username: hr
Password:


Connected to: Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

下面提示你 输入 要导入的 dmp 文件名

Import file: EXPDAT.DMP >
Enter insert buffer size (minimum is 8192) 30720>
Export file created by EXPORT:V10.02.01 via conventional path
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
import server uses WE8MSWIN1252 character set (possible charset conversion)

这里问，是否仅仅列内容，不导入
List contents of import file only (yes/no): no >

这里问 如果对象已经存在了， 是否忽略创建的错误
Ignore create error due to object existence (yes/no): no >

这里问，是否导入权限
Import grants (yes/no): yes > no

这里问，是否导入表数据
Import table data (yes/no): yes >

这里问，是否导入整个文件
Import entire export file (yes/no): no > yes
. importing HR's objects into HR
. importing HR's objects into HR
. . importing table                            "A"          8 rows imported
Import terminated successfully without warnings.

然后就结束了。 

View Code

 

//一个用户2个表空间的导入导出实例

-- -- 删除表空间和数据文件
-- drop tablespace gxHis including contents and datafiles;
-- drop user sa cascade;

-- 创建用户:源用户root，目标用户：sa
create user sa identified by sa123;

-- 创建表空间:tb3，tbExt_DATA
create tablespace tb3 datafile 'D:\Databases\Oracle\oradata\OrclSql\tb3-data.dbf' size 200m autoextend on;
create tablespace tbExt_DATA datafile 'D:\Databases\Oracle\oradata\OrclSql\tbExt-data.dbf' size 200m autoextend on;
alter user sa default tablespace tb3;
grant dba to sa;

-- select * from dba_tablespaces;
-- select username，default_tablespace from dba_users;

revoke unlimited tablespace from sa;
alter user sa quota unlimited on tb3 quota unlimited on tbExt_DATA;
alter user sa quota 0 on system;


-- 在cmd窗口下输入：
 imp sa/sa123 file='D:\Databases\Oracle\oradata\123.dmp' fromuser=root touser=sa log='D:\Databases\Oracle\oradata\log.txt'

View Code

 

 

//Oracle的tnsnames配置，和listener配置

本机PL/Sql、SqlPlus正常， 远程连接OrclSql提示ora-12541，无法解析请求。原因是由于后来配置了新实例，tnsping只是解析是否有实例能通，这个远程执行是通的。但是sqlplus连接时，就需要具体的实例名字，实例名字不对是连不上的。

# listener.ora Network Configuration File: C:\Oracle\product\11.2.0\dbhome_1\network\admin\listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = C:\Oracle\product\11.2.0\dbhome_1)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:C:\Oracle\product\11.2.0\dbhome_1\bin\oraclr11.dll")
    )
    (SID_DESC =
      (GLOBAL_DBNAME = OrclSql)
      (ORACLE_HOME = C:\Oracle\product\11.2.0\dbhome_1)
      (SID_NAME = OrclSql)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.11.103)(PORT = 1521))
    )
  )

ADR_BASE_LISTENER = C:\Oracle



========================


# tnsnames.ora Network Configuration File: C:\Oracle\product\11.2.0\dbhome_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.

ORACLR_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
    (CONNECT_DATA =
      (SID = CLRExtProc)
      (PRESENTATION = RO)
    )
  )

ORCLSQL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.11.103)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = OrclSql)
    )
  )

LISTENER_ORCLSQL =
  (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.11.103)(PORT = 1521))

View Code

 

 

 

//表空间操作

1、查询数据库中的表空间名称

1)查询所有表空间

select tablespace_name from dba_tablespaces; 
select tablespace_name from user_tablespaces; 

2)查询使用过的表空间  

select distinct tablespace_name from dba_all_tables;

select distinct tablespace_name from user_all_tables; 

2、查询表空间中所有表的名称

select table_name from dba_all_tables where tablespace_name = tablespacename

3、查询系统用户

select * from all_users
select * from dba_users

4、查看当前连接用户

select * from v$session

5、查看当前用户权限

select * from session_privs

6、查看所有的函数和存储过程

select * from user_source

其中TYPE包括：PROCEDURE、FUNCTION

7、查看表空间使用情况

select a.file_id "FileNo",
       a.tablespace_name "表空间",
       a.bytes "Bytes",
       a.bytes - sum(nvl(b.bytes, 0)) "已用",
       sum(nvl(b.bytes, 0)) "空闲",
       sum(nvl(b.bytes, 0)) / a.bytes * 100 "空闲百分率"
  from dba_data_files a, dba_free_space b
 where a.file_id = b.file_id(+)
 group by a.tablespace_name, a.file_id, a.bytes
 order by a.tablespace_name;

View Code

 

 

//附录

附录一： 
给用户增加导入数据权限的操作 
第一,启动sql*puls 
第二，以system/manager登陆 
第三，create user 用户名 IDENTIFIED BY 密码 （如果已经创建过用户，这步可以省略） 
第四，GRANT CREATE USER,DROP USER,ALTER USER ,CREATE ANY VIEW , 
   DROP ANY VIEW,EXP_FULL_DATABASE,IMP_FULL_DATABASE, 
      DBA,CONNECT,RESOURCE,CREATE SESSION  TO 用户名字 
第五, 运行-cmd-进入dmp文件所在的目录, 
      imp userid=system/manager full=y file=*.dmp 
      或者 imp userid=system/manager full=y file=filename.dmp 

执行示例: 
F:/Work/Oracle_Data/backup>imp userid=test/test full=y file=inner_notify.dmp 

屏幕显示 
Import: Release 8.1.7.0.0 - Production on 星期四 2月 16 16:50:05 2006 
(c) Copyright 2000 Oracle Corporation.  All rights reserved. 

连接到: Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production 
With the Partitioning option 
JServer Release 8.1.7.0.0 - Production 

经由常规路径导出由EXPORT:V08.01.07创建的文件 
已经完成ZHS16GBK字符集和ZHS16GBK NCHAR 字符集中的导入 
导出服务器使用UTF8 NCHAR 字符集 (可能的ncharset转换) 
. 正在将AICHANNEL的对象导入到 AICHANNEL 
. . 正在导入表                  "INNER_NOTIFY"          4行被导入 
准备启用约束条件... 
成功终止导入，但出现警告。 


附录二： 
Oracle 不允许直接改变表的拥有者, 利用Export/Import可以达到这一目的. 
  先建立import9.par, 
  然后，使用时命令如下：imp parfile=/filepath/import9.par 
  例 import9.par 内容如下： 
        FROMUSER=TGPMS        
        TOUSER=TGPMS2     （注：把表的拥有者由FROMUSER改为TOUSER，FROMUSER和TOUSER的用户可以不同）           
        ROWS=Y 
        INDEXES=Y 
        GRANTS=Y 
        CONSTRAINTS=Y 
        BUFFER=409600 
        file==/backup/ctgpc_20030623.dmp 
        log==/backup/import_20030623.log

View Code