oracle 常用命令
1.增加主键
alter table TABLE_NAME add constraint KEY_NAME primary key (TABLE_COLUMN);
2.增加外键
alter table TABLE_NAME add constraint FK_NAME foreign key (TABLE_COLUMN) references KEY_TABLE_NAME;
3.使主键或外键失效、生效
alter table TABLE_NAME disable(enable) constraint KEY_NAME;
4、查看各种约束
select constraint_name,table_name,constraint_type,status from user_constraints;
5、删除主键或外键
alter table TABLE_NAME drop constraint KEY_NAME;
6、在initorcl.ora中加入rollback_segments=(rb0,rb1,...)
其中rb0,rb1为自己定义的回滚段,可使这些回滚段在启动时生效
7、svgmgr>show sga 显示全局区
8、copy命令
格式: copy from <db> to <db> <opt> <table>
{(<cols>)} using <sel>
其中:<db>:database string ;e.g:scott/tiger@oracle
9、客户端注册表修改
HKEY_LOCAL_MACHINE/SOFTWARE/ORACLE
NLS_LANG_BAK ----语言字符集
简体中文:SIMPLIFIED CHINESE_CHINA.ZHS16CGB231280
美国英文:AMERICA.WE8ISO8859P1
10、在win95/win98中在注册表中:
HKEY_LOCAL_MACHINE/SOFTWARE/ORACLE
填加一个字符串键值
local="oracle" (可根据情况而定,指数据库别名)
即可以在sql*plus中不用输入连接串
或在autoexec.bat 中添加"set local=alias_name"
11、在sqlplus快捷方式中,在属性窗口中,在目标中,在"d:\orawin95\bin\plus33w.exe"
后面加空格和"scott/tiger@oracle"即可快速进入
12、修改数据库的字符集
在表props$中
update props$ set value$='ZHS16CGB231280'
13、oracle 安全与审计
user_sys_privs,user_tab_privs;
配置文件
主要参数
session_per_user 每个用户可同时进行几个会话
cpu_per_session 每个用户可用多少个(cpu的)百分之一秒
cpu_per_call 语法分析、执行、取数可用多少个百分之一秒
connect_time 用户连接数据库的时间(分钟)
idle_time 用户不调用数据库的时间(分钟)
logical_reads_per_session 整个会话过程中用户访问oracle的块数
logical_reads_per_call 一次调用用户访问oracle的块数
private_SGA 一个用户进程所用SGA的内存数量
composite_limit 复合限制数
failed_login_attempts 连续多次注册失败引起一个帐户死锁
password_life_time 一个口令在其终止前可用的天数
password_reuse_time 一个口令在其n天才能重新使用
password_reuse_max 一个口令在重新使用之前必须改变多少次
password_lock_time 一个口令帐户被锁住的天数
14、管理初始化文件
show parameters
经常修改的项目有 v$parameter
shared_pool_size 分配给共享的字节数
rollback_segments 回滚段的个数
sessions 会话个数
processes 进程个数
15、管理控制文件
控制文件保存文件有关数据库创建时间、数据库名以及数据库运行时使用的所有文件位置和名称。
增加控制文件,在initorcl.ora中,找到control_file项,增加一项即可
删除控制文件,在initorcl.ora中去掉,然后删除物理文件
建立新的控制文件
create controlfile [reuse] [set] database 数据库名
logfile [group 整数] 文件名 [,[group 整数] 文件名],...
对于现有的数据库,可以间接地通过
alter database backup controlfile to trace命令生成控制文件,即可在\orant\rmb7\trace
下有ora00289.trc文件,其内容为文本
16、管理回滚段:
存放事务的恢复信息
建立回滚段
create public rollback segment SEG_NAME tabelspace TABLESPACE_NAME;
alter rollback segment SEG_NAME online;
删除回滚段
首先改变为offline状态
直接使用回滚段
sql>set transaction use rollback segment SEG_NAME;
17、管理日志
建立日志组
sql>select * from v$logfile;
sql>alter database add logfile group 3
('f:\orant\database\log1_g3.ora'
'f:\orant\database\log2_g3.ora') size 100k;
sql>select * from v$logfile;
删除日志组
alter database drop logfile group 1;
但是其物理文件并没有被删除掉
系统至少需要2个日志组,如果只有2个,就不能删除
不能删除正活动的日志组
手工归档
通过alter system 的archive log 子句来实现
archive log [thread 整数]
[seq 整数][change 整数][current][group 整数]
[logfile '文件名'][next][all][start][to '位置']
18 系统控制
alter system ...
alter system enable restricted session; 只允许具有restricted系统特权的用户登录
alter system flush shared_pool 清除共享池
alter system checkpoint 执行一 个检查点
alter system set license_max_session=64,license_session_warning=54 会话限制为64,会话的警界为54
alter system set license_max_session=0 会话数为无限制
alter system set license_max_users=300 用户限制为300个
alter system switch logfile 强制执行日志转换
19 会话控制
alter session
alter session set sql_trace=true 当前会话具有sql跟踪功能
alter session set NLS_language=French 出错信息设为法语
alter session set NLS_date_format='YYYY MM DD HH24:MI:SS';缺省日期格式
alter session set optimizier_goal=first_row改变优化方法为基于开销方法,具有快速响应速度
update student@teach set sold=sold+1 where sno='98010';
commit;
alter session close database link teach; 关闭远程链路
20、封锁机制
数据封锁:保护表数据,在多个用户并行存取数据时候,保证数据的完整性。
DML操作又在两个级别获取数据封锁:指定记录封锁和表封锁
表封锁可以有下列方式:行共享(RS),行排他(RX),共享封锁(S),共享行排他(SPX)和排他 封锁(X) 行共享表封锁(RS),允许其他事务并行查询、插入,修改和删除及再行封锁
select ...from 表名 ... for update of ...;
lock table 表名 in row share mode;
行排他表封锁(RX) 对该行有独占权利
insert into 表名 ...;
update 表名 ...;
delete from 表名 ...;
lock table 表名 in row exclusive mode;
允许并行查询、插入、删除或封锁其他行,但禁止其他事务使用下列命令进行并发封锁:
lock table 表名 in share mode;
lock table 表名 in share exclusive mode;
lock table 表名 in exclusive mode;
共享表封锁(S)
lock table 表名 in share mode;
允许其他事务可在该表上做查询和再实现共享表操作,但不能修改该表,同时也不能做如下封锁:
lock table 表名 in share row exclusive mode;
lock table 表名 in exclusive mode;
lock table 表名 in row exclusive mode;
共享排他表封锁(SRX)
lock table 表名 in share row exclusive mode;
排他表封锁(SRX)
lock table 表名 in exclusive mode;
21、通常oracle需要启动OracleServiceORCL,OracleStartORCL,OracleTNSListener 任务
在NT上至少要启动两个服务
oraclestartID和oracleserverID
22、每个数据库都有一个系统标识符(SID),典型安装的数据库使用的系统标识符是ORCL
23、删除带约束的表
Drop table 表名 cascade constraints;
24、设置事务
set transaction [read only][read write][use rollback segment 回滚段名]
25、建外键
单字段时:create table 表名 (col1 char(8),
cno char(4) REFERENCE course);
多个字段时,在最后加上 Foreign Key (字段名) REFERENCE 表名(字段)
连带删除选项 (on delete cascade
当指定时,如果父表中的记录被删除,则依赖于父表的记录也被删除
REFERENCE 表名() on delete cascade;
26、启动关闭数据库
关闭:
svrmgr>connect internal/oracle
>shutdown --正常关闭数据库
svrmgr>shutdown immediate --立即关闭数据库
svrmgr>shutdown abort --一种最直接的关闭数据库的方式,执行之后,重新启动需要花6-8小时
启动:
$>svrmgrl
svrmgr>startup --正常启动
--等价于:startup nomount;
alter database mount;
alter database open;
svrmgr>startup mount; --安装启动:用于改变数据库的归档或执行恢复状态
svrmgr>startup nomount; --用于重建控制文件或重建数据库
svrmgr>startup restrict; --约束启动,能启动数据库,但只允许具有一定特权的用户访问
如果希望改变这种状态,连接成功后
alter system disable restricted session;
svrmgr>startup force;当不能关闭数据库时,可采用强制启动数据库来完成数据库的关闭操作。
svrmgr>startup pfile=d:\orant\database\initorcl.ora --带初始化参数文件的启动
27、缺省用户和密码
<1>. Oracle安裝完成后的初始口令?
internal/oracle
sys/change_on_install
system/manager
scott/tiger
sysman/oem_temp
<2>. ORACLE9IAS WEB CACHE的初始默认用户和密码?
administrator/administrator
28、对公共授予访问权
grant select on 表名 to public;
create public synonym 同义词名 for 表名;
29、填加注释
comment on table 表名 is '注释';
comment on column 表名.列名 is '注释';
30 oracle loader
控制文件的格式
load data
infile '数据文件名'
into table 表名
(first_name position(01:14) char,
surname position(15:28) char,
clssn position(29:36) char,
hire_data position(37:42) date 'YYMMDD')
31、程序中报错:maxinum cursor exceed!
应该调整数据库初始化文件
加如一项 open_cursors=200
32、生成用户时指定缺省表空间
create user 用户名 identified by 口令 default
tablespace 表空间名;
33、重新指定用户的缺省表空间
alter user 用户名 default tablespace 表空间名
34、约束条件
create table employee
(empno number(10) primary key,
name varchar2(40) not null,
deptno number(2) default 10,
salary number(7,2) check salary<10000,
birth_date date,
soc_see_num char(9) unique,
foreign key(deptno) references dept.deptno)
tablespace users;
关键字(primary key)必须是非空,表中记录的唯一性
not null 非空约束
default 缺省值约束
check 检查约束,使列的值符合一定的标准范围
unqiue 唯一性约束
foreign key 外部键约束
35、分布式数据库
create [public] database link LINKNAME
[connect to USERNAME identified by PASSWORD]
[using 'CONNECT_STRING']
可以在服务器端,也可以在客户端建立,但必须注意,两台服务器之间
数据库必须可以互访,必须各有各自的别名数据库
36、alter user语句的quota子句限制用户的磁盘空间
如:alter user jf quota 10M on system;
37 create table a
storage(
initial 1M /*第一次创建时分配空间*/
next 1M /*第一次分配的存储空间用完时在分配*/
)
as select * from b;
38、确定可用空间
select tablespace_name,sum(blocks),sum(bytes) from sys.dba_free_space group by tablespace_name;
39、创建序列
select * from user_sequences;
create sequence SEQ_NAME start with 1000
maxvalue 1000 increment by 1;
alter sequence SEQ_NAME minvalue 50 maxvalue 100;
40、删除重复行
update a set aa=null where aa is not null;
delete from a where rowid!=
(select max(rowid) from a b where a.aa=b.aa);
41、删除同其他表相同的行
delete from a where exits
(select 'X' from b where b.no=a.no);
或
delete from a where no in (select no from b);
42、查看数据库链路
select * from all_db_links;
select * from user_db_links;
查询 select * from TABLENAME@DBLNKNAME;
创建远程数据库同义词
create synonym for TABLENAME@DBLNKNAME;
操纵远程数据库记录
insert into TABLENAME@DBLNKNAME (a,b) values (va,vb);
update TABLENAME@DBLNKNAME set a='this';
delete from TABLENAME@DBLNKNAME;
43、快照
create snapshot SNAPSHOT_NAME
[storage (storage parameter)]
[tablespace TABLESPACE_NAME]
[refresh [fast\complete\force]
[start with START_DATE next NEXT_DATE]
as QUERY;
create snapshot snapshot_to_study as select * from TABLE_NAME@to_study;
创建角色
create role aa identified by aaa;
授权 grant create snapshot,alter snapshot to aaa;
grant aaa to emp;
create snapshot SNAPSHOT_TO_HTML refresh complete start with sysdate next sysdate+5/(24*60*60) as select * from a@to_html;
删除 drop snapshot snap_to_html
手工刷新快照,(调用DBMS_SNAPSHOT包中的refresh过程)DBMS_SNAPSHOT.refresh(snapshot_name,refresh_type);
begin
DBMS_SNAPSHOT.REFRESH('snap_to_html','c');
end;
对所有快照进行刷新
begin
DBMS_SNAPSHOT.REFRESH_ALL;
end;
怎样执行远程的内嵌过程
begin
otherdbpro@to_html(参数);
end;
44、包
包说明(package specification),包头,存放关于包的内容的信息,定义包的用户可见的过程、
函数,数据类型和变量
create or replace package tt_aa as
v1 varchar2(10);
v2 varchar2(10);
v3 number;
v4 boolean;
procedure proc1(x number);
procedure proc2(y varchar2);
procedure proc3(z number);
function my_add(x number,y number) return number;
end;
包主体(package body)是可选的
create or replace package body tt_aa as
procedure proc1(x number) as
begin
v1:=to_char(x);
end;
procedure proc2(y varchar2) as
begin
v2:=y;
end;
procedure proc3(z number) as
begin
v1:=z;
end;
procedure proc4(x number,y number) return number as
begin
return x+y;
end;
end;
调用
begin
tt_aa.proc1(6);
dbms_output.put_line(to_char(tt_aa.my_add(1,3));
end;
45、调度程序 DBMS_JOB
broken 中止一个任务调度
change 修改任务的属性
internal 改变间隔
submit 任务发送到任务队列中去
next_date 改变任务的运行时间
remove 删除一个任务
run 立即执行一个任务
submit 提交一个任务
user_export 任务说明
what 改变任务运行的程序
查询
select * from user_job;
建立一存储过程
create or replace procedure log_proc as
begin
insert into test(aa) values(sysdate);
commit;
end;
提交一个任务
declare
job_num number;
begin
dbms_job.submit(job_num,'log_proc;',sysdate,sysdate+5/(24*60*60),false);
dbms_output.put_line('Job numer='||to_char(job_num));
end;
移走任务
begin
dbms_job.remove(1);
end;
中止任务
begin
dbms_job.broken(1,true);
end;
46、动态sql
create or replace procedure my_execute(sql_string in varchar2) as
v_cursor number;
v_numrows interger;
begin
v_cursor:=dbms_sql.open_cursor;
dbms_sql.parse(v_cursor,sql_string,dbms_sql.v7);
v_numrows:=dbms_sql.execute(v_cursor);
dbms_sql.close_cursor(v_cursor);
end;
则可以
sql>exec my_execute('select * from tab');
sql>exec my_execute('insert into test value'||'('||'''ddd'''||')');
sql>exec my_execute('commit');
对于查询方面的可以如下方式:
比如想用游标查询一个表,但是这个表是分月的,每个月可能表名都会改变。
create or replace procedure proc_test as
v_curid integer;
v_result integer;
v_strSql varchar2(255);
v_userid okcai.userid%type;
v_username okcai.username%type;
begin
v_strSql := 'select * from okcai_'||to_char(sysdate,'yyyymm');
v_curid := dbms_sql.open_cursor;
dbms_sql.parse(v_curid,v_strSql,dbms_sql.v7);
dbms_sql.define_column(v_curid,1,v_userid);
dbms_sql.define_column(v_curid,2,v_username,10); --必须指定大小
v_result := dbms_sql.execute(v_curid);
loop
if dbms_sql.fetch_rows(v_curid) = 0 then
exit; --没有了 ,退出循环
end if;
dbms_sql.column_value(v_curid,1,v_userid);
dbms_sql.column_value(v_curid,2,v_username);
dbms_output.put_line(v_userid);
dbms_output.put_line(v_username);
end loop;
dbms_sql.close(v_curid);
end;
46.1 用EXECUTE IMMEDIATE
<1>. 在PL/SQL运行DDL语句
begin
execute immediate 'set role all';
end;
<2>. 给动态语句传值(USING 子句)
declare
l_depnam varchar2(20) := 'testing';
l_loc varchar2(10) := 'Dubai';
begin
execute immediate 'insert into dept values (:1, :2, :3)'
using 50, l_depnam, l_loc;
commit;
end;
<3>. 从动态语句检索值(INTO子句)
declare
l_cnt varchar2(20);
begin
execute immediate 'select count(1) from emp'
into l_cnt;
dbms_output.put_line(l_cnt);
end;
<4>. 动态调用例程.例程中用到的绑定变量参数必须指定参数类型.黓认为IN类型,其它类型必须显式指定
declare
l_routin varchar2(100) := 'gen2161.get_rowcnt';
l_tblnam varchar2(20) := 'emp';
l_cnt number;
l_status varchar2(200);
begin
execute immediate 'begin ' || l_routin || '(:2, :3, :4); end;'
using in l_tblnam, out l_cnt, in out l_status;
if l_status != 'OK' then
dbms_output.put_line('error');
end if;
end;
<5>. 将返回值传递到PL/SQL记录类型;同样也可用%rowtype变量
declare
type empdtlrec is record (empno number(4),
ename varchar2(20),
deptno number(2));
empdtl empdtlrec;
begin
execute immediate 'select empno, ename, deptno ' ||
'from emp where empno = 7934'
into empdtl;
end;
<6>. 传递并检索值.INTO子句用在USING子句前
declare
l_dept pls_integer := 20;
l_nam varchar2(20);
l_loc varchar2(20);
begin
execute immediate 'select dname, loc from dept where deptno = :1'
into l_nam, l_loc
using l_dept ;
end;
<7>. 多行查
1.增加主键
alter table TABLE_NAME add constraint KEY_NAME primary key (TABLE_COLUMN);
2.增加外键
alter table TABLE_NAME add constraint FK_NAME foreign key (TABLE_COLUMN) references KEY_TABLE_NAME;
3.使主键或外键失效、生效
alter table TABLE_NAME disable(enable) constraint KEY_NAME;
4、查看各种约束
select constraint_name,table_name,constraint_type,status from user_constraints;
5、删除主键或外键
alter table TABLE_NAME drop constraint KEY_NAME;
6、在initorcl.ora中加入rollback_segments=(rb0,rb1,...)
其中rb0,rb1为自己定义的回滚段,可使这些回滚段在启动时生效
7、svgmgr>show sga 显示全局区
8、copy命令
格式: copy from <db> to <db> <opt> <table>
{(<cols>)} using <sel>
其中:<db>:database string ;e.g:scott/tiger@oracle
9、客户端注册表修改
HKEY_LOCAL_MACHINE/SOFTWARE/ORACLE
NLS_LANG_BAK ----语言字符集
简体中文:SIMPLIFIED CHINESE_CHINA.ZHS16CGB231280
美国英文:AMERICA.WE8ISO8859P1
10、在win95/win98中在注册表中:
HKEY_LOCAL_MACHINE/SOFTWARE/ORACLE
填加一个字符串键值
local="oracle" (可根据情况而定,指数据库别名)
即可以在sql*plus中不用输入连接串
或在autoexec.bat 中添加"set local=alias_name"
11、在sqlplus快捷方式中,在属性窗口中,在目标中,在"d:\orawin95\bin\plus33w.exe"
后面加空格和"scott/tiger@oracle"即可快速进入
12、修改数据库的字符集
在表props$中
update props$ set value$='ZHS16CGB231280'
13、oracle 安全与审计
user_sys_privs,user_tab_privs;
配置文件
主要参数
session_per_user 每个用户可同时进行几个会话
cpu_per_session 每个用户可用多少个(cpu的)百分之一秒
cpu_per_call 语法分析、执行、取数可用多少个百分之一秒
connect_time 用户连接数据库的时间(分钟)
idle_time 用户不调用数据库的时间(分钟)
logical_reads_per_session 整个会话过程中用户访问oracle的块数
logical_reads_per_call 一次调用用户访问oracle的块数
private_SGA 一个用户进程所用SGA的内存数量
composite_limit 复合限制数
failed_login_attempts 连续多次注册失败引起一个帐户死锁
password_life_time 一个口令在其终止前可用的天数
password_reuse_time 一个口令在其n天才能重新使用
password_reuse_max 一个口令在重新使用之前必须改变多少次
password_lock_time 一个口令帐户被锁住的天数
14、管理初始化文件
show parameters
经常修改的项目有 v$parameter
shared_pool_size 分配给共享的字节数
rollback_segments 回滚段的个数
sessions 会话个数
processes 进程个数
15、管理控制文件
控制文件保存文件有关数据库创建时间、数据库名以及数据库运行时使用的所有文件位置和名称。
增加控制文件,在initorcl.ora中,找到control_file项,增加一项即可
删除控制文件,在initorcl.ora中去掉,然后删除物理文件
建立新的控制文件
create controlfile [reuse] [set] database 数据库名
logfile [group 整数] 文件名 [,[group 整数] 文件名],...
对于现有的数据库,可以间接地通过
alter database backup controlfile to trace命令生成控制文件,即可在\orant\rmb7\trace
下有ora00289.trc文件,其内容为文本
16、管理回滚段:
存放事务的恢复信息
建立回滚段
create public rollback segment SEG_NAME tabelspace TABLESPACE_NAME;
alter rollback segment SEG_NAME online;
删除回滚段
首先改变为offline状态
直接使用回滚段
sql>set transaction use rollback segment SEG_NAME;
17、管理日志
建立日志组
sql>select * from v$logfile;
sql>alter database add logfile group 3
('f:\orant\database\log1_g3.ora'
'f:\orant\database\log2_g3.ora') size 100k;
sql>select * from v$logfile;
删除日志组
alter database drop logfile group 1;
但是其物理文件并没有被删除掉
系统至少需要2个日志组,如果只有2个,就不能删除
不能删除正活动的日志组
手工归档
通过alter system 的archive log 子句来实现
archive log [thread 整数]
[seq 整数][change 整数][current][group 整数]
[logfile '文件名'][next][all][start][to '位置']
18 系统控制
alter system ...
alter system enable restricted session; 只允许具有restricted系统特权的用户登录
alter system flush shared_pool 清除共享池
alter system checkpoint 执行一 个检查点
alter system set license_max_session=64,license_session_warning=54 会话限制为64,会话的警界为54
alter system set license_max_session=0 会话数为无限制
alter system set license_max_users=300 用户限制为300个
alter system switch logfile 强制执行日志转换
19 会话控制
alter session
alter session set sql_trace=true 当前会话具有sql跟踪功能
alter session set NLS_language=French 出错信息设为法语
alter session set NLS_date_format='YYYY MM DD HH24:MI:SS';缺省日期格式
alter session set optimizier_goal=first_row改变优化方法为基于开销方法,具有快速响应速度
update student@teach set sold=sold+1 where sno='98010';
commit;
alter session close database link teach; 关闭远程链路
20、封锁机制
数据封锁:保护表数据,在多个用户并行存取数据时候,保证数据的完整性。
DML操作又在两个级别获取数据封锁:指定记录封锁和表封锁
表封锁可以有下列方式:行共享(RS),行排他(RX),共享封锁(S),共享行排他(SPX)和排他 封锁(X) 行共享表封锁(RS),允许其他事务并行查询、插入,修改和删除及再行封锁
select ...from 表名 ... for update of ...;
lock table 表名 in row share mode;
行排他表封锁(RX) 对该行有独占权利
insert into 表名 ...;
update 表名 ...;
delete from 表名 ...;
lock table 表名 in row exclusive mode;
允许并行查询、插入、删除或封锁其他行,但禁止其他事务使用下列命令进行并发封锁:
lock table 表名 in share mode;
lock table 表名 in share exclusive mode;
lock table 表名 in exclusive mode;
共享表封锁(S)
lock table 表名 in share mode;
允许其他事务可在该表上做查询和再实现共享表操作,但不能修改该表,同时也不能做如下封锁:
lock table 表名 in share row exclusive mode;
lock table 表名 in exclusive mode;
lock table 表名 in row exclusive mode;
共享排他表封锁(SRX)
lock table 表名 in share row exclusive mode;
排他表封锁(SRX)
lock table 表名 in exclusive mode;
21、通常oracle需要启动OracleServiceORCL,OracleStartORCL,OracleTNSListener 任务
在NT上至少要启动两个服务
oraclestartID和oracleserverID
22、每个数据库都有一个系统标识符(SID),典型安装的数据库使用的系统标识符是ORCL
23、删除带约束的表
Drop table 表名 cascade constraints;
24、设置事务
set transaction [read only][read write][use rollback segment 回滚段名]
25、建外键
单字段时:create table 表名 (col1 char(8),
cno char(4) REFERENCE course);
多个字段时,在最后加上 Foreign Key (字段名) REFERENCE 表名(字段)
连带删除选项 (on delete cascade
当指定时,如果父表中的记录被删除,则依赖于父表的记录也被删除
REFERENCE 表名() on delete cascade;
26、启动关闭数据库
关闭:
svrmgr>connect internal/oracle
>shutdown --正常关闭数据库
svrmgr>shutdown immediate --立即关闭数据库
svrmgr>shutdown abort --一种最直接的关闭数据库的方式,执行之后,重新启动需要花6-8小时
启动:
$>svrmgrl
svrmgr>startup --正常启动
--等价于:startup nomount;
alter database mount;
alter database open;
svrmgr>startup mount; --安装启动:用于改变数据库的归档或执行恢复状态
svrmgr>startup nomount; --用于重建控制文件或重建数据库
svrmgr>startup restrict; --约束启动,能启动数据库,但只允许具有一定特权的用户访问
如果希望改变这种状态,连接成功后
alter system disable restricted session;
svrmgr>startup force;当不能关闭数据库时,可采用强制启动数据库来完成数据库的关闭操作。
svrmgr>startup pfile=d:\orant\database\initorcl.ora --带初始化参数文件的启动
27、缺省用户和密码
<1>. Oracle安裝完成后的初始口令?
internal/oracle
sys/change_on_install
system/manager
scott/tiger
sysman/oem_temp
<2>. ORACLE9IAS WEB CACHE的初始默认用户和密码?
administrator/administrator
28、对公共授予访问权
grant select on 表名 to public;
create public synonym 同义词名 for 表名;
29、填加注释
comment on table 表名 is '注释';
comment on column 表名.列名 is '注释';
30 oracle loader
控制文件的格式
load data
infile '数据文件名'
into table 表名
(first_name position(01:14) char,
surname position(15:28) char,
clssn position(29:36) char,
hire_data position(37:42) date 'YYMMDD')
31、程序中报错:maxinum cursor exceed!
应该调整数据库初始化文件
加如一项 open_cursors=200
32、生成用户时指定缺省表空间
create user 用户名 identified by 口令 default
tablespace 表空间名;
33、重新指定用户的缺省表空间
alter user 用户名 default tablespace 表空间名
34、约束条件
create table employee
(empno number(10) primary key,
name varchar2(40) not null,
deptno number(2) default 10,
salary number(7,2) check salary<10000,
birth_date date,
soc_see_num char(9) unique,
foreign key(deptno) references dept.deptno)
tablespace users;
关键字(primary key)必须是非空,表中记录的唯一性
not null 非空约束
default 缺省值约束
check 检查约束,使列的值符合一定的标准范围
unqiue 唯一性约束
foreign key 外部键约束
35、分布式数据库
create [public] database link LINKNAME
[connect to USERNAME identified by PASSWORD]
[using 'CONNECT_STRING']
可以在服务器端,也可以在客户端建立,但必须注意,两台服务器之间
数据库必须可以互访,必须各有各自的别名数据库
36、alter user语句的quota子句限制用户的磁盘空间
如:alter user jf quota 10M on system;
37 create table a
storage(
initial 1M /*第一次创建时分配空间*/
next 1M /*第一次分配的存储空间用完时在分配*/
)
as select * from b;
38、确定可用空间
select tablespace_name,sum(blocks),sum(bytes) from sys.dba_free_space group by tablespace_name;
39、创建序列
select * from user_sequences;
create sequence SEQ_NAME start with 1000
maxvalue 1000 increment by 1;
alter sequence SEQ_NAME minvalue 50 maxvalue 100;
40、删除重复行
update a set aa=null where aa is not null;
delete from a where rowid!=
(select max(rowid) from a b where a.aa=b.aa);
41、删除同其他表相同的行
delete from a where exits
(select 'X' from b where b.no=a.no);
或
delete from a where no in (select no from b);
42、查看数据库链路
select * from all_db_links;
select * from user_db_links;
查询 select * from TABLENAME@DBLNKNAME;
创建远程数据库同义词
create synonym for TABLENAME@DBLNKNAME;
操纵远程数据库记录
insert into TABLENAME@DBLNKNAME (a,b) values (va,vb);
update TABLENAME@DBLNKNAME set a='this';
delete from TABLENAME@DBLNKNAME;
43、快照
create snapshot SNAPSHOT_NAME
[storage (storage parameter)]
[tablespace TABLESPACE_NAME]
[refresh [fast\complete\force]
[start with START_DATE next NEXT_DATE]
as QUERY;
create snapshot snapshot_to_study as select * from TABLE_NAME@to_study;
创建角色
create role aa identified by aaa;
授权 grant create snapshot,alter snapshot to aaa;
grant aaa to emp;
create snapshot SNAPSHOT_TO_HTML refresh complete start with sysdate next sysdate+5/(24*60*60) as select * from a@to_html;
删除 drop snapshot snap_to_html
手工刷新快照,(调用DBMS_SNAPSHOT包中的refresh过程)DBMS_SNAPSHOT.refresh(snapshot_name,refresh_type);
begin
DBMS_SNAPSHOT.REFRESH('snap_to_html','c');
end;
对所有快照进行刷新
begin
DBMS_SNAPSHOT.REFRESH_ALL;
end;
怎样执行远程的内嵌过程
begin
otherdbpro@to_html(参数);
end;
44、包
包说明(package specification),包头,存放关于包的内容的信息,定义包的用户可见的过程、
函数,数据类型和变量
create or replace package tt_aa as
v1 varchar2(10);
v2 varchar2(10);
v3 number;
v4 boolean;
procedure proc1(x number);
procedure proc2(y varchar2);
procedure proc3(z number);
function my_add(x number,y number) return number;
end;
包主体(package body)是可选的
create or replace package body tt_aa as
procedure proc1(x number) as
begin
v1:=to_char(x);
end;
procedure proc2(y varchar2) as
begin
v2:=y;
end;
procedure proc3(z number) as
begin
v1:=z;
end;
procedure proc4(x number,y number) return number as
begin
return x+y;
end;
end;
调用
begin
tt_aa.proc1(6);
dbms_output.put_line(to_char(tt_aa.my_add(1,3));
end;
45、调度程序 DBMS_JOB
broken 中止一个任务调度
change 修改任务的属性
internal 改变间隔
submit 任务发送到任务队列中去
next_date 改变任务的运行时间
remove 删除一个任务
run 立即执行一个任务
submit 提交一个任务
user_export 任务说明
what 改变任务运行的程序
查询
select * from user_job;
建立一存储过程
create or replace procedure log_proc as
begin
insert into test(aa) values(sysdate);
commit;
end;
提交一个任务
declare
job_num number;
begin
dbms_job.submit(job_num,'log_proc;',sysdate,sysdate+5/(24*60*60),false);
dbms_output.put_line('Job numer='||to_char(job_num));
end;
移走任务
begin
dbms_job.remove(1);
end;
中止任务
begin
dbms_job.broken(1,true);
end;
46、动态sql
create or replace procedure my_execute(sql_string in varchar2) as
v_cursor number;
v_numrows interger;
begin
v_cursor:=dbms_sql.open_cursor;
dbms_sql.parse(v_cursor,sql_string,dbms_sql.v7);
v_numrows:=dbms_sql.execute(v_cursor);
dbms_sql.close_cursor(v_cursor);
end;
则可以
sql>exec my_execute('select * from tab');
sql>exec my_execute('insert into test value'||'('||'''ddd'''||')');
sql>exec my_execute('commit');
对于查询方面的可以如下方式:
比如想用游标查询一个表,但是这个表是分月的,每个月可能表名都会改变。
create or replace procedure proc_test as
v_curid integer;
v_result integer;
v_strSql varchar2(255);
v_userid okcai.userid%type;
v_username okcai.username%type;
begin
v_strSql := 'select * from okcai_'||to_char(sysdate,'yyyymm');
v_curid := dbms_sql.open_cursor;
dbms_sql.parse(v_curid,v_strSql,dbms_sql.v7);
dbms_sql.define_column(v_curid,1,v_userid);
dbms_sql.define_column(v_curid,2,v_username,10); --必须指定大小
v_result := dbms_sql.execute(v_curid);
loop
if dbms_sql.fetch_rows(v_curid) = 0 then
exit; --没有了 ,退出循环
end if;
dbms_sql.column_value(v_curid,1,v_userid);
dbms_sql.column_value(v_curid,2,v_username);
dbms_output.put_line(v_userid);
dbms_output.put_line(v_username);
end loop;
dbms_sql.close(v_curid);
end;
46.1 用EXECUTE IMMEDIATE
<1>. 在PL/SQL运行DDL语句
begin
execute immediate 'set role all';
end;
<2>. 给动态语句传值(USING 子句)
declare
l_depnam varchar2(20) := 'testing';
l_loc varchar2(10) := 'Dubai';
begin
execute immediate 'insert into dept values (:1, :2, :3)'
using 50, l_depnam, l_loc;
commit;
end;
<3>. 从动态语句检索值(INTO子句)
declare
l_cnt varchar2(20);
begin
execute immediate 'select count(1) from emp'
into l_cnt;
dbms_output.put_line(l_cnt);
end;
<4>. 动态调用例程.例程中用到的绑定变量参数必须指定参数类型.黓认为IN类型,其它类型必须显式指定
declare
l_routin varchar2(100) := 'gen2161.get_rowcnt';
l_tblnam varchar2(20) := 'emp';
l_cnt number;
l_status varchar2(200);
begin
execute immediate 'begin ' || l_routin || '(:2, :3, :4); end;'
using in l_tblnam, out l_cnt, in out l_status;
if l_status != 'OK' then
dbms_output.put_line('error');
end if;
end;
<5>. 将返回值传递到PL/SQL记录类型;同样也可用%rowtype变量
declare
type empdtlrec is record (empno number(4),
ename varchar2(20),
deptno number(2));
empdtl empdtlrec;
begin
execute immediate 'select empno, ename, deptno ' ||
'from emp where empno = 7934'
into empdtl;
end;
<6>. 传递并检索值.INTO子句用在USING子句前
declare
l_dept pls_integer := 20;
l_nam varchar2(20);
l_loc varchar2(20);
begin
execute immediate 'select dname, loc from dept where deptno = :1'
into l_nam, l_loc
using l_dept ;
end;
<7>. 多行查