注意:此文档是本人由学习资料整理而成

comment on table test is 'this is a test table';
comment on column p.id is 'This is a id';

select * from user_tab_comments;
select * from user_col_comments where comments is not null;
集合运算:intersect,minus,union[all]
select empno,extract(month from hiredate) from emp;

--找7788员工的领导

select empno,ename,mgr from emp start with empno=7788
connect by prior mgr=empno;

--找7788可以管理到的员工
select empno,ename,mgr from emp start with empno=7788
connect by prior empno=mgr;

SELECT EMPNO,RPAD(' ',LEVEL * 3) || ENAME ENAME ,MGR FROM EMP WHERE ENAME<>'CLARK' START WITH ENAME='KING'
CONNECT BY PRIOR EMPNO=MGR AND ENAME <> 'SCOTT';

merge into copy_emp c
using emp e
on(c.empno=e.empno)
when matched then
update set --注意这里不能出现修改empno
c.ename=e.ename,
c.job=e.job,
c.mgr=e.mgr,
c.hiredate=e.hiredate,
c.sal=e.sal,
c.comm=e.comm,
c.deptno=e.deptno
when not matched then
insert values(e.empno,
e.ename , e.job,
e.mgr , e.hiredate,
e.sal , e.comm,
e.deptno );

rename t to t1;

alter table child add constraint child_fk foreign key(rid)
references p(id) on delete cascade;

alter table p disable constraint p_pk cascade;

alter table p drop column id cascade constraints;

oracle server --- oracle instance
oracle database

oracle instance --- memory structures
background structures

oracle database --- data file
log file
control file
memory structures --- sga
pga

sga --- shared pool
database buffer cache
redo log buffer (log_buffer)
large pool (large_pool_size)
java pool (java_pool_size)
shared pool --- library cache
data dictionary cache(row cache)
library cache --- shared sql area
shared pl/sql area
database buffer cache-- db_cache_size
db_keep_cache_size
db_recycle_cache_size


在数据库中如对数据的更改，是先写日志后写数据

select * from v$bgprocess where paddr <> '00' ;

1)orapwd file=' ' password=xxx entries=5
2)remote_login_passwordfile = exclusive
3)grant sysdba to xxx
4)select * from v$pwfile_users;

startup restrict
alter system enable restricted session;

shutdown normal|transactional|immediate|abort

alter database clear [unarchived] logfile group 3

logmnr:
1)alter system set utl_file_dir='C:oracleoradatautl' scope=spfile
2)shutdown immediate;
3)startup
4)exec dbms_logmnr_d.build('whxdir.ora','c:oracleoradatautl');
5)exec dbms_logmnr.add_logfile('c:oracleoradatawhxredo02.log',dbms_logmnr.new);
6)exec dbms_logmnr.start_logmnr(dictfilename=>'C:oracleoradatautlwhxdir.ora');
7)desc v$logmnr_contesnts

create tablespace test
datafile 'c:oracleoradatawhxtest.dbf' size 5m
extent management dictionary | local -- 如果是local则不能指定下面的存储参数
default storage
(initial 100k next 100k maxextents 999 pctincrease 10) online;
create tablespace test1
datafile 'c:oracleoradatawhxtest1.dbf' size 5m
extent management local
[uniform size 1m | autoallocate ]
segment space management auto;


create undo tablespace test2
datafile 'c:oracleoradatawhxtest2.dbf' size 5m
extent management local; --不能指定存储参数,只能是本地管理

create temporary tablespace test3
tempfile 'c:oracleoradatawhxtest3.dbf' size 5m
extent management local --建议用本地管理
uniform size 1m;

alter database datafile 'c:oracleoradatawhxtest1.dbf' resize 10m;

alter tablespace test1 add datafile
'c:oracleoradatawhxtest1a.dbf' size 5m;

--修改系统文件名或路径
1)shutdown immediate;
2)startup mount;
3)在操作系统上移动或修改文件
4)alter database rename file 'c:oracleoradatawhxsystem01.dbf'
to 'c:oracleoradatawhxsystem001.dbf';
5)alter database open;

alter table test
allocate extent
(size 1m datafile 'c:oracleoradatawhxusers01.dbf');

alter table test
deallocate unused keep 0m;

alter table test
move tablespace users; --此步操作后，此表上的索引需要rebuild

alter table test.emp
set unused column job;

alter table test.emp
drop unused columns;


drop table test cascade constraint;

1) show parameter db
2) alter system set db_cache_size=8k;
3) alter system set db_8k_cache_size=8k;
4) create tablespace test5 datafile 'c:oracleoradatawhxtest5.dbf'
size 1m blocksize 8k;


alter tablespace users offline normal;

1)alter tablespace users offline immediate;--后删除文件,拷贝原先的备份文件
2)alter tablespace users online;
3)recover datafile 'c:oracleoradatawhxusers01.dbf';
4)alter tablespace users online;

1)delete from test.emp;
2)commit;
3)select * from test.emp;
4)show parameter undo_retention
5)connect test/test
5)exec dbms_flashback.enable_at_time(to_date('2006-10-04 15:24','yyyy-mm-dd hh24:mi'));
6)select * from emp;
7)exec dbms_flashback.disable;

1)alter system set undo_management=manual scope=spfile;
2)shutdown immediate;
3)show parameter undo
4)show parameter rollback;
5)create [public] rollback segment rbs1 tablespace undotbs;
6)alter rollback segment rbs1 online;
7)alter system set rollback_segments=rbs1,rbs2 scope=spfile;

select rowid,dbms_rowid.rowid_block_number(rowid),
dbms_rowid.rowid_row_number(rowid) from scott.emp;

create global temporary table test
on commit delete rows --事务级
as select * from scott.emp;

create global temporary table test
on commit preserve rows --会话级
as select * from scott.emp;

create table test(id int)
tablespace users
pctfree 30 pctused 50 --创建索引时PCTUSED不可用
storage(initial 500k next 500k
pctincrease 0 minextents 1 maxextents 100);


create index testindex on
test.emp(sal)
tablespace indx
pctfree 30
storage(initial 100k next 100k pctincrease 0
minextents 2 maxextents 200)
nologging;

alter index testindex
rebuild
tablespace users --改变索引所在表空间
reverse --改变索引为反转
online
coalesce --碎片整理
monitoring usage | nomonitoring usage; --监视


analyze index testindex validate structure;
desc index_stats;

--往表里装载很多数据时，可以考虑先删除表里的
--索引，然后装载数据，数据装载完后，重新建立索引

create index emp_ind1 on emp(sal+comm); --这个也是函数索引
select * from user_ind_expressions; --查看函数索引

alter table emp add constraint
emp_name_ck check(ename like 'J%')
initially deferred | initially immediate
disable novalidate | disable validate
enable novalidate | enable validate ;

alter table emp enable novalidate constraint emp_ck1;
alter table emp modify constraint emp_ck1 initially deferred;

alter session set constraint=immediate | deferred

create profile whx_profile
limit
failed_login_attempts 3
password_lock_time 1/(24*60);

alter profile whx_profile
limit
password_life_time 1/(24*60)
password_grace_time 3/(24*60)
password_reuse_time 10
password_reuse_max 2
password_verify_function verify_function
sessions_per_user 3 --后面这些参数要生效首先要设置resource_limit参数
cpu_per_session 10000
connect_time 60
idle_time 5
cpu_per_call 100
logical_reads_per_call 10;

drop profile whx_profile;

alter system set resource_limit=true;

--用户的管理包括：
--权限的管理
--资源的管理

create user whx_user
identified by user_001 | externally
default tablespace users
temporary tablespace temp
quota 15m on tools
password expire
account lock;

alter user whx_user quota 0 on users; -- whx_user在 users表空间上没有新空间分配
-- 已分配的空间不受影响

grant system_privs ,[...] to [user/role/public],[...] with admin option
grant opject_privs (column,...),[...] on object_name to [user/role/public] with grant option

1)alter system set o7_dictionary_accessibility=true scope=spfile;
2)grant select any table to whx_user; --whx_user能够访问系统视图

1)系统权限不能级连拿回
2)对象权限是级连拿回

--audit_trail ------ db/os/none
--audit create table
select/update/insert [on object]
by session / by access
whenever successful / not successful

1)alter system set audit_trail=db scope=spfile;
2)audit select on scott.emp by session;
3)audit create table;
4)audit update on scott.emp whenever successful;
5)noaudit select on scott.emp;
6)select * from dba_audit_trail/dba_audit_exists/dba_audit_object
dba_audit_session/dba_audit_statement;

1) create public database link wenhx connect to scott
identified by tiger using 'remote';--using 后面为服务名
2) select * from dept@wenhx;

create role whx_role identified by role_001;

--role no schema

alter user whx_user default role role1,[role2...]
all | all [ except role3,role4 ] none

alter user testuser01 default role testrole1,testrole2;

set role testrole2 identified by role_002,testrole01;

--查看某个用户有什么角色
select * from dba_role_privs where grantee='SCOTT';

--查看某个ROLE包含那些ROLE角色权限
select * from role_role_privs WHERE role='TESTROLE2';

--查看某个用户有那些系统权限
select * from dba_sys_privs where grantee='TESTUSER01';

--查看某个ROLE有那些系统权限
select * from role_sys_privs where role='CONNECT';

--查看某个ROLE有那些表的权限
SELECT * FROM ROLE_TAB_PRIVS WHERE ROLE='TESTROLE01';

--查看当前会话有那些角色的权限
select * from session_roles;

--AL16UTF16 固定两个字节，比较时比较快，因此性能比较好
--UTF8 可变个字节（1-3），节省空间

--设定环境变量 set nls_lang=simplified chinese_china.us7ascii
alter session set nls_language='SIMPLIFIED CHINESE';

alter system set dispatchers='(protocol=tcp)(dispatchers=3)';

select * from v$archive_processes;

alter system archive log start | stop |
alter system archive log current | all | sequence 032

alter system set log_archive_dest_1='location=c:oracleadminwhxbdump';
alter system set log_archive_dest_2='location=c:oracleadminwhxudump mandatory reopen=600';
alter system set log_archive_dest_3='location=c:oracleadminwhxpfile optional';
alter system set log_archive_dest_state_3=defer|enable;

ALTER DATABASE DATAFILE 9 OFFLINE DROP; --非归档模式下

list backup of database;
LIST BACKUP OF TABLESPACE USERS;
LIST BACKUP OF DATAFILE 'C:oracleoradatawhxINDX01.DBF';
list copy of datafile 'c:oracleoradatawhxindx01.dbf';
list copy of tablespace users;
RMAN> list copy of controlfile;
RMAN> list copy of datafile 3

report schema;
REPORT NEED BACKUP DAYS 5;
report need backup redundancy 3;
report need backup incremental 3;

RMAN> run{
allocate channel d1 type disk;
copy datafile 'c:oracleoradatawhxusers01.dbf' to 'c:user01.bak';

copy current controlfile to 'c:backupcontrol.bak';
release channel d1;
}

--NOARCHIVELOG下没有日志文件的恢复
1)shutdown immediate;
2)恢复数据文件和控制文件
3)startup mount;
4)recover database until cancel;
5)alter database open resetlogs;

--ARCHIVELOG下文件损坏的恢复（有备份文件）
1)shutdown immediate;
2)startup mount;
3)alter database datafile 7 offline;
4)恢复数据文件
5)recover database ; | recover datafile 7;
6)alter database datafile 7 online;
7)alter database open;

--ARCHIVELOG下没有备份文件的恢复
1）shutdown immediate;
2）删除要做测试的文件
3）start mount;
4）alter database datafile 7 offline;
5）alter database open;
6）alter database create datafile 'c:oracleoradatawhxtools.dbf' as
'c:oracleoradatawhxtools.dbf';
7）recover datafile 7 ;
8）alter database datafile 7 online;

--ARCHIVELOG下把文件恢复到其他路径
RUN {
ALLOCATE CHANNEL D1 TYPE DISK;
SET NEWNAME FOR DATAFILE 'C:ORACLEORADATAWHXUSERS01.DBF' TO 'C:USERS01.DBF';
RESTORE TABLESPACE USERS;
SWITCH DATAFILE 'C:USERS01.DBF';
RECOVER TABLESPACE USERS;
RELEASE CHANNEL D1;
}

--不完全恢复下的表空间恢复
1）关闭数据库
2）备份数据文件
3）打开数据库
4）备份控制文件
5）模拟误删某个表空间
6）关闭数据库
7）RESTORE数据文件和控制文件
8）MOUNT数据库
9）RECOVER DATABASE UNTIL TIME ‘2006-10-23 14：50：00’ USING
BACKUP CONTROLFILE；
10）ALTER DATABASE OPEN RESETLOGS
11）备份数据库

--控制文件和日志文件损坏，只有数据文件的恢复
1）ALTER DATABASE BACKUP CONTROLFILE TO TRACE；
2）SHUTDOWN IMMEDIATE；
3）删除当前的日志文件和控制文件
4）到TRACE文件里，找到控制文件的创建脚本
5）START NOMOUNT ，重建控制文件
6）ALTER DATABASE OPEN RESETLOGS；
7）备份数据库

--RMAN的数据库不完全恢复
--先在操作系统下设置环境变量 SET NLS_DATE_FROMAT=YYYY-MM-DD HH24:MI:SS
run
{allocate channel d1 type disk;
allocate channel d2 type disk;
allocate channel d3 type disk;
set until time='2006-10-23 15:44:00;
restore database;
recover database;
release channel d1;
release channel d2;
release channel d3;
alter database open resetlogs;}

crosscheck copy;
crosscheck backup;
crosscheck backup of tablespace users;
crosscheck copy of datafile 1;

delete copy of datafile 1;
delete noprompt expired backup of tablespace users;
delete obsolete;

catalog datafilecopy 'c:backupusers01.dbf';

--RMAN中配置CATALOG数据库
1）创建用于RMAN的表空间test_rman
2）create user rman identified by rman default tablespace test_rman
quota unlimited on test_rman
3）grant connect,recovery_catalog_owner to rman
4）在RMAN命令符下执行create catalog tablespace rman1;
5）在RMAN命令符下执行connect target sys/whx;
6）在RMAN命令符下执行register database;

RMAN> resync catalog;

create script test_script { ... ;}
replace script test_script { ... ;}
print script test_script;
delete script test_script;

alter session enable parallel dml;
insert /*+append*/ into dept select * from dept;
insert /*+parallel(whx.dept,2) */ into dept nologging select * from scott.dept;

使用Statspack工具查看系统运行状况
1)确认timed_statistics参数为true,job_queue_processes的值大于0
2)创建一个专门用于收集信息的表空间
create tablespace perfstat datafile 'c:oracleoradatawhxperfstat.dbf'
size 100m extent management local;
3)运行spcreate.sql脚本,相反操作运行spdrop.sql
4)connect perfstate/perfstate
execute statspack.snap进行拍照
运行spauto.sql脚本设置作业
运行select snap_id from stats$snapshot;查看快照的次数
移除作业
5)运行spreport.sql脚本产生报告

使用utlstat工具查看系统运行状况
1)运行utlbstat.sql脚本
2)运行utlestat.sql脚本
3)查看当前目录下的report.txt文件

http://127.0.0.1:7778/isqlplus

自动导出数据:
cd c:
set d=%date:~0,4%%date:~5,2%%date:~8,2%-%time:~0,2%%time:~3,2%%time:~6,2%
exp scott/tiger file=c:orabk%d%.dmp log=c:orabk%d%.log
c:orabkrar a c:orabk%d%.rar c:orabk*.dmp c:orabk*.log
del c:orabk%d%.dmp
del c:orabk%d%.log

查锁：
select a.sid,a.serial#,a.username,a.terminal,c.sql_text
from v$session a,v$lock b,v$sqltext c
where a.lockwait=b.kaddr and a.sql_address=c.address;
SELECT * FROM V$SQLTEXT WHERE ADDRESS IN
(SELECT SQL_ADDRESS FROM V$SESSION WHERE LOCKWAIT IS NOT NULL AND SID IN
(SELECT SESSION_ID FROM V$LOCKED_OBJECT))；


$ORACLE_HOME/network/admin/protocol.ora文件中控制对ORACLE的访问：
tcp_validnode_checking=yes
tcp_invited_nodes=(192.168.0.1,192.168.0.2)
tcp_exclued_nodes=(192.168.0.222)

查活动的SQL语句：
SELECT A.USER_NAME,A.SQL_TEXT FROM V$OPEN_CURSOR A,
V$SESSION B WHERE A.SID=B.SID AND B.STATUS='ACTIVE'

exec dbms_utility.analyze_schema('HR','ESTIMATE',NULL,5);

--打开状态下，复制数据库
1）创建所需要的目录：
/%ORACLE_BASE%/ORADATA/%ORACLE_SID%/ (里面的内容是备份来的数据文件），
/%ORACLE_BASE%/ADMIN/%ORACLE_SID%/ （里面的内容是可以去拷贝）
2）在/%ORACLE_HOME/DATABASE/下创建所需的参数文件，密码文件
3）创建服务： oradim
4）创建控制文件：alter database backup controlfile to trace;
然后修改这个脚本文件，startup nomount数据库，运行修改后的脚本
修改后的脚本如下：
CREATE CONTROLFILE SET DATABASE "CLONE" RESETLOGS ARCHIVELOG
MAXLOGFILES 5 MAXLOGMEMBERS 5 MAXDATAFILES 100 MAXINSTANCES 1 MAXLOGHISTORY 113
LOGFILE
GROUP 1 'C:ORACLEORADATAcloneREDO01.LOG' SIZE 10M,
GROUP 2 'C:ORACLEORADATAcloneREDO02.LOG' SIZE 10M
DATAFILE
'C:ORACLEORADATAcloneSYSTEM01.DBF',...
CHARACTER SET ZHS16GBK;

5）set logsource 'c:ab';
recover database using backup controlfile until cancel;
6)alter database open resetlogs;

----RAC
crs_stat -t
crs_stop service
crs_start service

crsctl check crs
crsctl stop crs
crsctl start crs

olsnodes

srvctl status database -d devdb
srvctl status asm -n rac1
srvctl status nodeapps -n rac1

srvctl stop database -d devdb
srvctl stop asm -n rac1
srvctl stop nodeapps -n rac1

srvctl start database -d devdb
srvctl start asm -n rac1
srvctl start nodeapps -n rac1
srvctl start listener -n rac1
srvctl start instance -d devdb -i devdb1

srvctl relocate service -d devdb -s crm -i devdb2 -t devdb1


假设$ORACLE_SID=ORCL,SERVICE_NAME = ORCLRAC
1、进入$ORA_CRS_HOME/bin目录
[oracle@nod1 bin]$ cd $ORA_CRS_HOME/bin

2、运行crs_stat -p，可以得知当前某个服务的一些参数，自动运行的参数是AUTO_START,1是自动启动，0是手工。
[oracle@nod1 bin]$ ./crs_stat -p ora.ORCL.db

3、要想让数据库不自动启动，就要修改下面几个服务的AUTO_START参数为0(10g根据版本不一样，默认的设置也不一样，例如我用的10.1.0.3就是0)。
a、ora.ORCL.db
b、ora.ORCL.ORCLRAC.cs
c、ora.ORCL.ORCLRAC.ORCL1.srv/ora.ORCL.ORCLRAC.ORCL2.srv……（几个节点就有几个）

4、修改参数，例：
[oracle@nod1 bin]$ ./crs_profile -update ora.ORCL.db -o AUTO_START=0

5、更新，例：
[oracle@nod1 bin]$ ./crs_register -u ora.ORCL.db