ORACLE常用命令整理

解锁用户:
alter user name account unlock;

修改用户密码:

alter user user01 identified by user10

希望用户xiaoming可以去查询scott的emp的表/还希望把这个权限继续给其他的人
--如果是对象权限（用户对其他用户数据对象操作的权限），就加入 wiht grant option
grant select on scott.emp to xiaoming grant with option
revoke select on scott.emp from xiaoming(删除小明对scott用户中的emp表的查询权限)

--如果是系统权限(用户对数据库的相关权限)时
grant select on emp to xiaoming with grant option

用户口令管理(设定一个用户登录3次，如果密码错误就锁定3天)

create profile lock_account(规则名称) limit
failed_login_attempts 3(输入密码次数) password_lock_time 2(锁定时间);

启用
alter user xiaoming profile lock_account(规则名称)

要求用户在规定的时间内修改密码且宽限期为几天。
create profile 规则名 limit
password_life_time 10 password_grace_time 2;

alter user xiaoming profile 规则名

提示用户在修改密码的时候不能用以前的密码
create profile 规则名 limit
password_life_time 10 password_grace_time 2
password_reuse_time 10;

dorp profile 规则名 [cascade]

终止口令
create profile lock_account(规则名称) limit
password_file_time 10 password_grace_time 2;
(要求该用户,每隔10天修改一次密码，宽限期为2天)

启用
alter user xiaoming profile lock_account;

修改日期默认格式
alter session set nls_date_format ='yyyy-MM-dd';

添加主键约束

ALTER TABLE stuInfo

ADD CONSTRAINT PK_stuNo PRIMARY KEY(stuNo)

添加唯一约束
ALTER TABLE stuInfo

ADD CONSTRAINT UQ_stuID UNIQUE(stuNo)

添加默认约束

ALTER TABLE stuInfo

ADD CONSTRAINT DF_stuAddress DEFAULT('地址不详') FOR stuAddress

添加检查约束

ALTER TABLE stuInfo

ADD CONSTRAINT CK_stuAge Check(stu_Age BETWEEN 15 AND 40)

alter table auction_user
add constraint ck_card check(length(AU_IDENTITY_CODE) =18 or length(AU_IDENTITY_CODE) = 15);

添加外键约束

ALTER TABLE stuMarks

ADD CONSTRAINT FK_stuNo

FOREIGN KEY(stuNo) references stuInfo(stuNo)

创建视图

create or replace view myview
as
select * from books ;


spool保存查询选项

spool c:/Oracle.txt

spool off
edit c:/Oracle.txt

exit-退出
l-查看内存区里面的临时sql语句
c/n/m修改内存区里面的临时sql语句
/ 执行内存区里面的sql语句

desc--查看表结构

查看控制文件

select name from v$controlfile

查看日志文件

select member from v$logfile

远程判断Oracle数据库的安装平台

修改表名

ALTER TABLE AA RENAME TO BB;

查询各个用户的默认表空间

select username,default_tablespace from dba_users;

冷备份
停止服务
shoutdown immediate

查看所有日志文件

archive log list

创建序列

create sequence users_id
start with 1
increment by 1
nomaxvalue
cache 10;

创建表空间
create tablespace epet_tablespaces
datafile 'F:\Oracle11g\oradata\JBITDB\epet.dbf'
size 100M

修改表空间名称

alter tablespace epet_tablespaces rename to epet

创建用户
create user epet
identified by bdnq
default tablespace epet_tablespaces;

赋予权限

grant connect,resource to epet;

删除权限

revoke connect from xiaoming

打开输出选项
set serveroutput on/off;

创建存储过程

create or replace procedure ShowInfo(ids in number)
as
cursor s_info is select name,age from
temp_user where id=ids;
begin
for s_stu in s_info loop
dbms_output.put_line(s_stu.name||''||s_stu.age);
end loop;
end;


create or replace procedure my_pro1 is

enames varchar2(20);
sals number(7,2);
begin
select ename,sal into enames,sals from emp where empno=&no;
dbms_output.put_line('查询出的名称为:'||enames||'薪水为:'||sals);
exception
when no_data_found then
dbms_output.put_line('输入出错了哦!');
end;

调用

begin
ShowInfo(2);
end;


创建函数


create or replace function getcount(ids in number)
return number as f_count number;
begin
select count(*) into f_count from temp_user where id=ids;
return f_count;
end;

declare
v_count number;
begin
v_count:=getcount(2);
dbms_output.put_line(v_count);

end;


create function my_fun1(enames varchar2)
return number is
sals number;
begin
select sal into sals from emp where ename=enames;


return sals;
end;


包的创建

//包头的创建

create or replace package emp_package as

procedure my_proc(
len_name varchar2,
len_age number);
end emp_package;

//包体的创建
create or replace package body emp_package as

procedure my_proc(
len_name varchar2,
len_age number)
is
begin

insert into temp_user values(temp_user_seq.nextval,len_name,len_age);
end my_proc;
end emp_package;

begin
emp_package.my_proc('苹果',12);
end;


--包头
create or replace package p_store_car as

procedure store_proc(
lend_type varchar2,
lend_size number,
lend_price number,
lend_year date,
lend_card varchar2);

function store_fun(
typ varchar2)return number;

end p_store_car;
--包体
create or replace package body p_store_car as
--存储过程
procedure store_proc(
lend_type varchar2,
lend_size number,
lend_price number,
lend_year date,
lend_card varchar2)
is
begin
insert into store_car values(lend_type,lend_size,lend_price,lend_year,lend_card);
commit;
end store_proc;
--函数
function store_fun(typ varchar2)
return number is
f_count number;
begin
select count(*) into f_count from store_car where types=typ;
return f_count;
end store_fun;

end p_store_car;

--测试
declare
f_count number;
begin
p_store_car.store_proc('ssss',111,111,sysdate,'aaaa');
f_count:=p_store_car.store_fun('ssss');
dbms_output.put_line(f_count);
end;

创建视图

create or replace view product_view as

select supplier.name,product.address,product.price,product.store_id from product inner join supplier on product.id=supplier.id;


select * from product_view;


database创建


create database link link_goods
connect to epet identified by bdnq
using
'
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = BDQNML-PC)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
';


数据库链
create database link link_good
connect to epet identified by bdnq
using
'
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = BDQNML-PC)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = JBITDB)
)
)


drop database link link_good;
select * from users@link_good;


创建索引


create unique index i_id on temp_emp(id);


修改索引


alter index i_id rebuild storage(initial 1m nex 512k)


删除索引


drop index schema.index_name


范围索引分区
create table Score(
id number not null,
name varchar2(20) not null,
subject varchar2(20) not null,
score number)


partition by range(score)(
partition id_1 values less than (40),
partition id_2 values less than (60),
partition id_3 values less than (maxvalue));


select * from score partition(id_1);


列表分区


create table Sales_info(
name varchar2(20),
dept varchar2(20),
location varchar2(50))
partition by list(location)(
partition local_n values('北京'),
partition local_s values('广州'),
partition local_e values('上海'));


两个查询结果之间取并集 union,自动去掉所有结果集中重复的行。
union all 不去掉重复的行。
intersect 取两个查询结果的交集
minus 取两个查询结果的差集。


Oracle 分页语句:
select * from (select a1.*,rownum rn from (select * from emp) a1 where rownum<=pageSize*pageNum) a2 where a2.rn>=pageSize*(pageNum-1)+1;


--分页存储过程
--创建一个包，和一个接收返回数据结果集的游标。
create or replace package testpackage as
type test_cursor is ref cursor;
end testpackage;


--编写分页存储过程 参数1 tableName,PageSize,pageNow,myrows,mypageCount


create or replace procedure
fengye(tableName in varchar2,--表名
pageSizes in number,--每一页显示的大小
pageNow in number,--当前的页数
myrows out number,--总记录数
mypageCount out number,--总共多少页
my_cursor out testpackage.test_cursor--接收返回结果集的游标
)
is
v_sql varchar2(1000);--存储分页的sql语句
v_begin_num number:=pageSizes*(pageNow-1)+1;--开始记录数
v_end_num number:=pageSizes*pageNow;--结束记录数
begin
--分页sql语句
v_sql:='select * from (select a1.*,
rownum rn from (select * from '||tableName||')
a1 where rownum<='||v_end_num||') where rn>='||v_begin_num;
--将sql语句返回的结果集与游标关联在一起
open my_cursor for v_sql;
v_sql:='select count(*) from '||tableName;--查询总记录数
execute immediate v_sql into myrows;--将查询总记录数的sql语句执行后，将值赋给myrows(输入参数)变量
--计算总页数
if mod(myrows,pageSizes)=0
then mypageCount:=myrows/pageSizes;
else
mypageCount:=myrows/pageSizes+1;
end if;
-- close my_cursor;--关闭游标
end;