oracle 常用技能

alert 日志内容：

ALERT日志位置由 Automatic Diagnostic Repository (ADR) 决定。通过新的初始化参数DIAGNOSTIC_DEST 控制 ADR BASE 位置。
如果没有设定这个初始化参数的位置，而设置 ORACLE_BASE 环境变量的话，那么ADR为ORACLE_BASE。
如果没有设定 ORACLE_BASE , 则 ORACLE_HOME/log 即为 DIAGNOSTIC_DEST。
Alert Log 文件父目录的位置在：ADR_BASE/diag/{product_type}/{product_id}/{instance_id}

SQL查询路径：

SQL> select value from v$diag_info;

adrci

dbv

oerr

常用的系统权限集合有以下三个:
CONNECT(基本的连接),RESOURCE(程序开发), DBA(数据库管理)

©数据导出

1 将数据库TEST完全导出,用户名system 密码manager 导出到D:\daochu.dmp中
exp system/manager@TEST file=d:\daochu.dmp full=y

2 将数据库中system用户与sys用户的表导出
exp system/manager@TEST file=d:\daochu.dmp owner=(system,sys)

3 将数据库中的表inner_notify、notify_staff_relat导出
exp aichannel/aichannel@TESTDB2 file= d:\data\newsmgnt.dmp tables=(inner_notify,notify_staff_relat) 

4 将数据库中的表table1中的字段filed1以"00"打头的数据导出
exp system/manager@TEST file=d:\daochu.dmp tables=(table1) query=\" where filed1 like '00%'\"

5 将T1.dmp的数据导入用户KQA1中
imp KQA1/KQA1@orcl FILE=T1.DMP LOG=T1IMP.LOG FULL=Y SHOW=Y

6.抽出索引创建语句
expdp xu/xu directory=dp_dir dumpfile=ind.dmp include=index
impdp xu/xu directory=dp_dir dumpfile=ind.dmp sqlfile=ind.sql

© Flash Back

1.启用闪回数据库特性
startup mount;
alter database archivelog;
# archive log start;
alter database flashback on;
alter database open;
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
SELECT OLDEST_FLASHBACK_SCN, OLDEST_FLASHBACK_TIME FROM V$FLASHBACK_DATABASE_LOG;
select * from V$FLASH_RECOVERY_AREA_USAGE

2.获得当前数据库的SCN值
select dbms_flashback.get_system_change_number fscn from dual; (9I 10G)
select current_scn from v$database;

3.闪回日志
/data5/flash_recovery_area/EYGLE/flashback

4.进行闪回操作
shutdown immediate;
startup mount;
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
select * from V$FLASHBACK_DATABASE_LOG;
flashback database to timestamp to_timestamp ('2005-03-29 17:02:28','yyyy-mm-dd hh24:mi:ss');

5.resetlogs打开数据库
shutdown immediate;
startup mount;
#alter database open resetlogs;
alter database open noresetlogs
select * from v$log;

6.设置闪回地址，大小，过期时间
alter system set db_recovery_file_dest='/flash_recovery_area';
alter system set db_recovery_file_dest_size=3G;
alter system set db_flashback_retention_target=240
alter database flashback on;
select flashback_on from v$database;

7.闪回能够回到的最大SCN
select oldest_flashback_scn, oldest_flashback_time from v$flashback_database_log;

8.flashback的代价（每小时必要的IO）
select end_time,flashback_data,db_data,redo_data from v$flashback_database_stat;

9.查看当前闪回的大小
select * from v$sgastat where name like 'flashback%';

10.闪回到某个SCN或者Sequence
select dbms_flashback.get_system_change_number c_SCN from dual;
flashback database to scn=2728665;
flashback database to sequence=2123 thread=1;

11.两天前的数据
select * from employees as of timestamp (sysdate - 2);

12.用SCN 查询
select * from employees as of SCN ;

13.表的删除和恢复:
flashback table order_items to before drop rename to order_items_old_version;

14.使用flash table
create table test as select * from dba_users;
select count(*) from test as of scn 12742550;
select count(*) from test as of timestamp to_timestamp('21-07-09 14:20:00', 'dd-mm-yy hh24:mi:ss');
alter table test enable row movement;
flashback table test to scn 1391833;
flashback table test to timestamp to_timestamp('22-07-09 13:20:00', 'dd-mm-yy hh24:mi:ss');

15.对table的flashback
flashback table t_user to before drop;
drop table t_user purge;
drop tablespace small including contents and datafiles;
purge table t_user;
purge user_recyclebin;
purge dba_recyclebin;

16.查看Undo信息
select begin_time,end_time, undoblks, maxquerylen,ssolderrcnt,nospaceerrcnt from v$undostat;

17.使用还原点
create restore point restore_point_01 guarantee flashback database;
drop restore point restore_point_01;
flashback database to restore point restore_point_01;

18.使用回收站
show recyclebin;
select count(*) from dba_recyclebin where owner='DROPPER';
select owner, original_name, type, droptime, can_undrop, space from dab_recyclebin;
select object_name, original_name, type from user_recyclebin;
select sum(bytes) from dba_free_space where tablespace_name='SMALL';
select segment_name, bytes from dba_segments where tablespace_name='SMALL';

select table_name fromuser_tables;       ##获取数据库的全部表
select * from all_users;                ##查看所有用户
select name fromv$database;            ##查看当前数据库名
select * fromv$instance;               ##查看所有的数据库实例
select username,password from dba_users; ##查看当前实例中的用户和密码
select member fromv$logfile;            ##查看日志文件
select * fromuser_role_privs;           ##查看当前用户的角色
select username,default_tablespace from user_users; ##查看当前用户的缺省表空间

查找数据库中所有列
select C.column_name,C.TABLE_NAME from dba_tab_columns C where owner=''

表复制
insert into table_a (id,name,age) select b.id,b.name,b.age from table_b;

查看当前Job的执行计划
select job,next_date,next_sec,failures,broken from user_jobs;

删除一个job
begin
dbms_job.remove(46);--46为job号
end;

查看当前库的所有数据表：
select TABLE_NAME from all_tables;
select * from all_tables;
select table_name from all_tables where table_name like ‘u’;

创建用户并赋予权限
create user mpss
identified by "mpss12"
default tablespace TS_MPSS_DATA
temporary tablespace TEMP;

建立临时表空间
CREATE
    TEMPORARY TABLESPACE "SWVIP" TEMPFILE'/app/oracle/oradata/
    sworacle/SWVIP.dbf' SIZE 5M EXTENT MANAGEMENT LOCAL UNIFORM
    SIZE 1M
create tablespace TS_MPSS_DATA datafile '/mpss/data/ts_mpss_data.bdf ' size1024m autoextend on ;

查看表空间大小
SELECT D.TABLESPACE_NAME "Name",
    TO_CHAR(((((A.BYTES - DECODE(F.BYTES, NULL, 0, F.BYTES)) /1024 / 1024)) /(A.BYTES / 1024 /   1024))*100,'99,990.9')"used(%)",
TO_CHAR((DECODE(F.BYTES, NULL, 0, F.BYTES) / 1024 / 1024),'999,990.9')"Free (M)"
FROM SYS.DBA_TABLESPACES D, SYS.SM$TS_AVAIL A, SYS.SM$TS_FREE F
WHERE D.TABLESPACE_NAME = A.TABLESPACE_NAME
AND F.TABLESPACE_NAME (+) = D.TABLESPACE_NAME;

SELECTD.TABLESPACE_NAME,SPACE "SUM_SPACE(M)",BLOCKSSUM_BLOCKS,SPACE-NVL(FREE_SPACE,0) "USED_SPACE(M)",
ROUND((1-NVL(FREE_SPACE,0)/SPACE)*100,2) "USED_RATE(%)",FREE_SPACE"FREE_SPACE(M)"
FROM
(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) SPACE,SUM(BLOCKS)BLOCKS
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME) D,
(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) FREE_SPACE
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
UNION ALL --if have tempfile
SELECT D.TABLESPACE_NAME,SPACE "SUM_SPACE(M)",BLOCKS SUM_BLOCKS,
USED_SPACE "USED_SPACE(M)",ROUND(NVL(USED_SPACE,0)/SPACE*100,2)"USED_RATE(%)",
NVL(FREE_SPACE,0) "FREE_SPACE(M)"
FROM
(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) SPACE,SUM(BLOCKS)BLOCKS
FROM DBA_TEMP_FILES
GROUP BY TABLESPACE_NAME) D,
(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES_USED)/(1024*1024),2) USED_SPACE,
ROUND(SUM(BYTES_FREE)/(1024*1024),2) FREE_SPACE
FROM V$TEMP_SPACE_HEADER
GROUP BY TABLESPACE_NAME) F
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+) ;

查看表空间物理文件的名称及大小;
select tablespace_name, file_id, file_name,
    round(bytes/(1024*1024),0) total_space
    from dba_data_files
    order by tablespace_name;

查看数据文件放置的路径
col file_name format a50
select tablespace_name,file_id,bytes/1024/1024,file_name from dba_data_filesorder by file_id;

查看数据库库对象
select owner, object_type, status, count(*) count# from all_objects group by owner,object_type, status;

检查被长时间锁的对象
selecta.session_id,a.process,a.locked_mode,b.object_name,b.object_type,b.status fromv$locked_object a,dba_objects b where a.object_id=b.object_id;

用系统管理员，查看当前数据库有几个用户连接：
select username,sid,serial# from v$session;

扩表空间
alter tablespace G000 add datafile '/dev/vgbilling/rg000_lv03' SIZE 7500m;

创建DB Link
CREATE DATABASE LINK LK_KQA CONNECT TO KQA1 IDENTIFIED BY KQA1 USING 'ORCL';

创建Materialized View
create materialized view FRAME refresh fast for update as select
ACCOMMODATION_AREA_CODE,
FACILITY_NAME,
FACILITY_NUMBER,
DEVICE_NUMBER,
FACILITY_TYPE,
COUPLER_TYPE,
FLOOR_NAME,
FLOOR_GROUP,
FLOOR_FRAME_NUMBER,
REAL_ACCOMMODATE_BIL_CODE
from k027.FRAME@k027
where ACCOMMODATION_AREA_CODE = '27205001';

sequence使用
create sequence seq_product INCREMENT BY 1 START WITH 1 NOMAXVALUE NOCYCLECACHE 10;
--seq_product.CURRVAL
--seq_product.NEXTVAL

建表语句
CREATE TABLE CENTER_ACROSS_INFO (
ACCOMMODATION_AREA_CODE VARCHAR2(24) NOT NULL ,
FACILITY_NAME VARCHAR2(30) NOT NULL ,
FACILITY_NUMBER VARCHAR2(39) NOT NULL);

ALTER TABLE CENTER_ACROSS_INFO ADD COLUMN DEVICE_NUMBER VARCHAR(30);

ALTER TABLE CENTER_ACROSS_INFO ALTER COLUMN DEVICE_NUMBER NUMBER(2,4);

ALTER TABLE CENTER_ACROSS_INFO DROP COLUMN DEVICE_NUMBER ;

ALTER TABLE CENTER_ACROSS_INFO ADD CONSTRAINT
CENTER_ACROSS_INFO_PK PRIMARY KEY (ACCOMMODATION_AREA_CODE, FACILITY_NAME);

--DROP TABLE CENTER_ACROSS_INFO;

建立表空间
create tablespace DATA
datafile
'd:/DATA/data01.dbf' size 512M reuse autoextend off
extent management local uniform size 10M
segment space management auto;

alter tablespace DATA add datafile 'd:/DATA/data02.dbf' size 512M;

打开监听
lsnrctl start

进入ARCHIVELOG模式
startup mount
alter database archivelog;
alter database open;
archive log list;
archive log all;
select log_mode from v$database;

丢失control文件恢复
alter system setcontrol_files="E:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\CONTROL02.CTL"scope=spfile;
startup force;
select * from v$controlfile;
select recid,name,thread#,sequence# from v$archived_log

丢失数据文件恢复
create tablespace noncrit datafile'E:\oracle\product\10.1.0\oradata\orcl\noncrit.dbf' size 2m;
create table ex203 (c1 date) tablespace noncrit;
insert into ex203 values(sysdate);
alter database datafile 7 online;
alter database datafile 3 offline;

建立一个备份控制文件
Alter database backup control file to trace.

启动记录转移
alter table dept enable row movement;
alter table emp enable row movement;
flashback table emp,dept to timestamp to_timestamp('21-07-09 14:20:00','dd-mm-yy hh24:mi:ss');
flashback table emp,dept to scn 6539425 enable triggers;

查看连接
select sid,username,program from v$session;

测试instance是否能够连的上
tnsping qnetdb

查看系统参数
SQL>show parameter mttr
fast_start_mttr_target integer 300
这个参数说明当instance recovery 的时候，数据库能够在300s之内open。
show parameter recovery
show parameter db_recovery
show parameter log_archive_dest
alter system set fast_start_mttr_target=0;

查询某个SQL语句执行用的cpu时间
select count(*) from dba_objects,dba_objects;
select cpu_time,elapsed_time,disk_reads from v$sql where sql_text='selectcount(*) from dba_objects,dba_objects ';

查询alert信息
select reason,metric_value from dba_outstanding_alerts;

加快创建索引
create index xu.rname_idx on xu.regions(region_name) tablespace indx parallel8;
create index xu.rname_idx on xu.regions(region_name) tablespace indx nologging;

查看table的历史记录
select name, versions_startscn,versions_endscn from countries versions betweenscn minvalue and maxvalue;
create table countries (name varchar2(50));
insert into countries values ('hello');
insert into countries values ('tba');
update countries set name='abc' where name='tba'
alter table countries enable row movement;
flashback table countries to scn 12776941 enable triggers;

获得表空间的名字
select distinct FILE_NAME, TABLESPACE_NAME from dba_data_files;
select owner,table_name,tablespace_name from all_tables where tablespace_name='USERS';
select extent_id, file_id, block_id, blocks from dba_extents where owner='XU'and segment_name='USERS';
select
a.tablespace_name "Name",
round(a.bytes_alloc/1024/1024,2) "all(M)",
round((a.bytes_alloc-nvl(b.bytes_free,0))/1024/1024,2) "used(M)",
round(nvl(b.bytes_free,0)/1024/1024,2) "free(M)",
round((nvl(b.bytes_free,0)/a.bytes_alloc)*100,2) "free(%)"
from (select f.tablespace_name,
sum(f.bytes) bytes_alloc,
sum(decode(f.autoextensible,'YES',f.maxbytes,'NO',f.bytes)) maxbytes
from dba_data_files f
group by tablespace_name) a,
(select f.tablespace_name,
sum(f.bytes) bytes_free
from dba_free_space f
group by tablespace_name) b
where a.tablespace_name = b.tablespace_name;

验证是否有错误
dbv file=/oracle/oradata/new_tbs.dbf blocksize=8192;

查看服务器使用情况
select wait_class,total_waits,time_waited from v$system_wait_class order bytime_waited;

快照保留时间设为2周，生成时间设为20分钟
executedbms_workload_repository.modify_snapshot_settings(retention=>20160,interval=>20);

查看恢复的时间
select recovery_estimated_ios ios, actual_redo_blks redo,target_mttr,estimated_mttr, writes_mttr from v$instance_recovery;

查看Sql缓存的Sql语句
select sql_id from v$sql where sql_text='select max(c1) from t1';

查看表空间的大小，以及是否设置为自动增加
select file_id,file_name,bytes,autoextensible,increment_by,maxbytes fromdba_data_files;
alter database datafile 7 autoextend on;
alter database datafile '/oradata/users01.dbf' autoextend on next 10m maxsize200m;

查看表空间的空闲空间
select tablespace_name, sum(bytes) from dba_free_space group bytablespace_name;
select object_name, reason from dba_outstanding_alerts;

设置挂起的超时时间
alter session enable resumable timeout 60 name 'AR archive';
alter session enable resumable;

查看表占用空间量
select owner,bytes from dba_segments where segment_name='TEST';
alter table test enable row movement;
alter table test shrink space;

查看和压缩索引空间
ANALYZE TABLE table_name VALIDATE STRUCTURE CASCADE;
ANALYZE INDEX index_name VALIDATE STRUCTURE;
analyze index i1 validate structure;
select lf_rows_len, del_lf_rows_len from index_stats where name='I1';
select bytes from dba_segments where segment_name='I1';
alter index i1 shrink space;
analyze index i1 validate structure;
alter index i1 coalesce;
alter index i1 rebuild online;
alter index i1 rebuild online tablespace idx_ts;

重新创建control file
alter database backup controlfile to trace;
alter database backup controlfile to '/oracle/app/product/10.2.0/db_1/tmp';
show parameter user_dump_dest;
shutdown immediate;
startup nomount;
CREATE CONTROLFILE REUSE DATABASE "orcl" NORESETLOGS ARCHIVELOG
MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 100 MAXINSTANCES 8 MAXLOGHISTORY454
LOGFILE
GROUP 1 (
'E:\oracle\product\10.1.0\oradata\orcl\REDO01.LOG'
) SIZE 10M,
GROUP 2 (
'E:\oracle\product\10.1.0\oradata\orcl\REDO02.LOG'
) SIZE 10M,
GROUP 3 (
'E:\oracle\product\10.1.0\oradata\orcl\REDO03.LOG'
) SIZE 10M
-- STANDBY LOGFILE
DATAFILE
'E:\oracle\product\10.1.0\oradata\orcl\SYSTEM01.DBF',
'E:\oracle\product\10.1.0\oradata\orcl\UNDOTBS01.DBF',
'E:\oracle\product\10.1.0\oradata\orcl\SYSAUX01.DBF',
'E:\oracle\product\10.1.0\oradata\orcl\USERS01.DBF',
'E:\oracle\product\10.1.0\oradata\orcl\EXAMPLE01.DBF'
CHARACTER SET WE8MSWIN1252
;

重建logfile
select group#,sequence#,archived,status from v$log;
select group#,status,member from v$logfile order by group#;
alter database clear logfile group 2;
alter database clear unarchived logfile group 2;
recover database until cancel;

查看恢复文件
select * from v$recovery_file_dest;
select reason from dba_outstanding_alerts;

修改redo log
alter system switch logfile;
select group#,sequence#,members,status from v$log;
select group#,status,member from v$logfile;
alter system checkpoint;
ALTER DATABASE ADD LOGFILE MEMBER'E:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\myAddLog' TO GROUP 1

显示当前连接用户
SHOW USER ;

在Oracle服务器上通过SQLPLUS查看本机IP地址
select sys_context('userenv','ip_address') from dual;

查看最大会话数
SELECT * FROM V$PARAMETER WHERE NAME LIKE 'proc%';

查看系统被锁的事务时间
select * from v$locked_object ;

查得数据库的SID
select name from v$database;

快速清空一个大表
truncate table table_name;

将表移动表空间
ALTER TABLE TABLE_NAME MOVE TABLESPACE_NAME;

将索引移动表空间
ALTER INDEX INDEX_NAME REBUILD TABLESPACE TABLESPACE_NAME

解锁
ALTER SYSTEM KILL SESSION ‘SID,SERIR#';

ORACLE产生随机函数
DBMS_RANDOM.RANDOM

查询当前用户对象
SELECT * FROM USER_OBJECTS;
SELECT * FROM DBA_SEGMENTS;

获取错误信息
SELECT * FROM USER_ERRORS;

获取链接状况
SELECT * FROM DBA_DB_LINKS;

查看数据库字符状况
SELECT * FROM NLS_DATABASE_PARAMETERS;
SELECT * FROM V$NLS_PARAMETERS;
select name,value$ from props$ where name like 'NLS%'

查询表空间信息
SELECT * FROM DBA_DATA_FILES;

查看各个表空间占用磁盘情况
select b.file_id 文件ID号, b.tablespace_name 表空间名, b.bytes 字节数,(b.bytes-sum(nvl(a.bytes,0))) 已使用, sum(nvl(a.bytes,0)) 剩余空间,sum(nvl(a.bytes,0))/(b.bytes)*100 剩余百分比 from dba_free_space a,dba_data_files b wherea.file_id=b.file_id group by b.tablespace_name,b.file_id,b.bytes order byb.file_id

在ORACLE中取毫秒
select systimestamp from dual;

在字符串里加回车
select 'Welcome to visit'||chr(10)||'www.CSDN.NET' from dual ;

修改oracel数据库的默认日期
alter session set nls_date_format='yyyymmddhh24miss';

知道数据裤中某个表所在的tablespace
select tablespace_name from user_tables where table_name='TEST';

快速做一个和原表一样的备份表
create table New_table as (select * from Old_table);

看到数据库有多少个tablespace
select * from dba_tablespaces;

把“&”放入一条记录中
insert into table_name values (translate ('at{&}t','at{}','at'));

查有多少个数据库实例
SELECT * FROM V$INSTANCE;

查询数据库有多少表
select * from all_tables;

测试SQL语句执行所用的时间
set timing on ;
select * from tablename;

进行数字和Ascii码的转换
SELECT CHAR(65) FROM DUAL;
SELECT ASCII('A') FROM DUAL;

查询某天的数据
select * from table_name where trunc(日期字段)＝to_date('2003-05-02','yyyy-mm-dd');

知道机器上的Oracle支持多少并发用户数
conn internal ;
show parameter processes ;

在给现有的日期加上2年
select add_months(sysdate,24) from dual;

返回大于等于N的最小整数值
SELECT CEIL(N) FROM DUAL;

返回小于等于N的最小整数值
SELECT FLOOR(N) FROM DUAL;

返回当前月的最后一天
SELECT LAST_DAY(SYSDATE) FROM DUAL

进行两个结果集互减
SELECT * FROM BSEMPMS_OLD MINUS SELECT * FROM BSEMPMS_NEW;

日期的各部分的常用的的写法
SELECT TO_CHAR(SYSDATE,'YYYY') FROM DUAL; -- 年
SELECT TO_CHAR(SYSDATE,'MM') FROM DUAL; -- 月
SELECT TO_CHAR(SYSDATE,'DD') FROM DUAL; -- 日
SELECT TO_CHAR(SYSDATE,'HH24') FROM DUAL; -- 时
SELECT TO_CHAR(SYSDATE,'MI') FROM DUAL; -- 分
SELECT TO_CHAR(SYSDATE,'SS') FROM DUAL; -- 秒
SELECT TO_CHAR(SYSDATE,'D') FROM DUAL; -- 星期
SELECT TO_CHAR(SYSDATE,'DDD') FROM DUAL; -- 第几天
SELECT TO_CHAR(SYSDATE,'WW') FROM DUAL; -- 第几周
SELECT TO_CHAR(SYSDATE,'HH24:MI:SS') FROM DUAL; -- 时间
to_char(1210.78, '9999.9') = '1210.8'
to_char(1210.73, '9999.9') = '1210.7'
to_char(1210.73, '9, 999.99') = '1, 210.73'
to_char(1210.73, '$9, 999.00') = '$1, 210.73'
to_char(21, '000099') = '000021'
to_char(sysdate, 'ss') 取当前时间秒部分 a
to_char(sysdate, 'mi') 取当前时间分钟部分
to_char(sysdate, 'HH24') 取当前时间秒小时部分
to_char(sysdate, 'DD') 取当前时间日期部分
to_char(sysdate, 'MM') 取当前时间月部分
to_char(sysdate, 'YYYY') 取当前时间年部分
to_char(sysdate, 'w') 取当前时间是一个月中的第几周(从1日开始算)
to_char(sysdate, 'ww') 取当前时间是一年中的第几周（从1.1开始算）
to_char(sysdate, 'iw') 取当前时间是一年中的第几周（按实际日历的）
to_char(sysdate, 'd') 取当前时间是一周的第几天，从星期天开始，周六结束
to_char(sysdate, 'day') 取当前日是星期几，和数据库设置的字符集有关，会输出'Tuesday'
to_char(sysdate, 'ddd') 当前日是一年中的第几天
to_char(sysdate, 'yyyy/mm/dd');
to_char(sysdate, 'Month DD, YYYY');
to_char(sysdate, 'FMMonth DD, YYYY');
to_char(sysdate, 'MON DDth, YYYY');
to_char(sysdate, 'FMMON DDth, YYYY');
to_char(sysdate, 'FMMon ddth, YYYY');

转换为日期
SELECT TRUNC(SYSDATE) FROM DUAL;
SELECT TO_DATE('2003/08/01') FROM DUAL;

进行编码转换
SELECT CONVERT('要改变的字符串','US7ASCII','WE8HP')'CONVERSION' FROM PUBS;

锁定某个账户
ALTER USER hr ACCOUNT LOCK;
ALTER USER hr ACCOUNT UNLOCK;

使用块变化追踪
alter database enable block change tracking using file
'e:/oracle/backup/change_tracking.dbf';

设置警告文件大小
alter system set max_dump_file_size='5m';

列出所有指标值
select internal_metric_name from v$alert_types where internal_metric_name like'%_ps';

列出未处理的警告
select reason,object_type type,object_name name from dba_outstanding_alerts;
select reason,object_type type,object_name name from dba_alert_history whereobject_name='SMALL';

设置表空间的临界区大小（50％，75％）
execute dbms_server_alert.set_threshold(-
metrics_id=>dbms_server_alert.tablespace_pct_full,-
warning_operator=>dbms_server_alert.operator_ge,-
warning_value=>'50',-
critical_operator=>dbms_server_alert.operator_ge,-
critical_value=>'75',-
observation_period=>1,consecutive_occurrences=>2,-
instance_name=>null,-
object_type=>dbms_server_alert.object_type_tablespace,-
object_name=>'SMALL');

循环插入数据
begin
for n in 1..500 loop
insert into toobig values('a row');
end loop;
commit;
end;
/

追踪客户端的SQL语句
alter system set sql_trace=true;
select s.username,s.sid,p.spid from v$session s, v$process p wheres.paddr=p.addr;
alter session set sql_trace=true;
execute dbms_monitor.session_trace_disable( -
session_id=>162,serial_num=>14);
alter session set sql_trace=false;

替换受损的临时文件
alter tablespace temp_ts3 add tempfile 'c:/ts2.bdf' size 100m;
alter database tempfile 'c:/ts1.bdf' offline;
alter database tempfile 'c:/ts1.bdf' drop;

替换受损的表空间
create temporary tablespace temp_ts4 tempfile 'c:/ts4.bdf' size 100m;
alter database default temporary tablespace temp_ts4;
drop tablespace temp_ts3 including contents and datafiles;

查询联机日志
select group#,sequence#,bytes,members,archived,status from v$log;
select group#,status,member from v$logfile order by group#;

替换受损的联机日志
alter database drop logfile member 'c:/redo01.log';
alter database add logfile member 'c:/redo02.log' to group 1;
alter database clear logfile group 2;

发现索引文件错误
select name,status from v$datafile where file#=7;
select online_status,error from v$recover_file where file#=7;

监视索引有否被使用
alter index reg_id_pk monitoring usage;
select index_name, table_name,used from v$object_usage;
alter index reg_id_pk nomoinitoring usage;

外部文件表
CREATE DIRECTORY TEST AS 'e:\data\';
GRANT READ ON DIRECTORY TEST TO mydbuser;
GRANT WRITE ON DIRECTORY TEST TO mydbuser;
CREATE TABLE oldempx (empno number,empname char(20))
ORGANIZATION EXTERNAL
(TYPE ORACLE_LOADER DEFAULT DIRECTORY TEST ACCESS PARAMETERS
(RECORDS DELIMITED BY NEWLINE FIELDS TERMINATED BY ','
(empno char, empname char)) LOCATION ('emp1.txt'))
parallel 5 reject limit 200;

查看表结构
desc employees;

用命令行创建数据库
1.指定实例名
export ORACLE_SID=ax
cd $ORACLE_HOME/dbs
2.创建密码文件
orapwd file=orapwclone password=oracle
3.从原数据库拷贝spfile
create pfile from spfile;
4.创建物理文件
mkdir -p ax/(adump,bdump,cdump,udump)
mkdir /u01/app/oracle/oradata/ax
cp initorcl.ora initclone.ora
vi initclone.ora orcl -> ax
*.db_file_name_convert='/u01/app/oracle/oradata/orcl','/u01/app/oracle/oradata/ax'
*.log_file_name_convert='/u01/app/oracle/oradata/orcl','/u01/app/oracle/oradata/ax'
create spfile from pfile;

ROLLUP
如果是ROLLUP(A, B, C)的话，首先会对(A、B、C)进行GROUP BY，然后对(A、B)进行GROUP BY，然后是(A)进行GROUP BY，最后对全表进行GROUP BY操作。如果是GROUP BY CUBE(A, B, C)，则首先会对(A、B、C)进行GROUP BY，然后依次是(A、B)，(A、C)，(A)，(B、C)，(B)，(C)，最后对全表进行GROUP BY操作。
create table emp_rollup as select * from dba_indexes;
select index_type, status, count(*) from emp_rollup group by index_type,status;
select index_type, status, count(*) from emp_rollup group by rollup(index_type,status);
select index_type, status, count(*) from emp_rollup group by cube(index_type,status);

timezone
SELECT DBTIMEZONE,SESSIONTIMEZONE FROM DUAL;
CREATE TABLE TIMESTAMP_TEST(TIME DATE, TIMESTP TIMESTAMP(3), TIMESTP_TZTIMESTAMP(3) WITH TIME ZONE, TIMESTP_LTZ TIMESTAMP(3) WITH LOCAL TIME ZONE);
INSERT INTO TIMESTAMP_TEST VALUES(SYSDATE,SYSDATE,SYSDATE,SYSDATE);
SELECT * FROM TIMESTAMP_TEST;
ALTER SESSION SET TIME_ZONE='+10:00';
SELECT * FROM TIMESTAMP_TEST;

创建任务
DECLARE
task_name varchar2(30);
sql_stmt clob;
BEGIN
sql_stmt := 'select /*+ full(a) use_hash(a) ' ||
' parallel(a,8) full(b) use_hash(b) parallel(b,8) ' ||
' */ a.type, sum(a.amt_paid) ' ||
' from large_table a, large_table2 b ' ||
' where a.key = b.key ' ||
' and state_id = :bnd';
task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_text => sql_stmt,
bind_list => sql_binds (anydata.ConvertNumber(32));
user_name => 'BUTERTB',
scope => 'COMPREHENSIVE',
time_limit => 45,
task_name => 'large_table_task',
description => 'Tune state totals query');
dbms_output.put_line('Task ' || task_name ||
' has been created.');
END;
/

执行任务
BEGIN
DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'large_table_task');
END;
The status of the executing task can be monitored by querying theDBA_ADVISOR_LOG view or V$SESSION_LONGOPS:
select status from dba_advisor_log where task_name = 'large_table_task';
BEGIN
DBMS_SQLTUNE.INTERRUPT_TUNING_TASK( task_name => 'large_table_task');
END;
If you want to interrupt a tuning task that is currently executing, use theINTERRUPT_
TUNING_TASK procedure:
BEGIN
DBMS_SQLTUNE.INTERRUPT_TUNING_TASK( task_name => 'large_table_task');
END;

Task Results
set long 1000
set longchunksize 1000
set linesize 132
select dbms_sqltune.report_tuning_task('large_table_task') from dual;

管理SQL_Profile
Accepting a SQL Profile
DECLARE
sqlprofile_name varchar2(30);
BEGIN
sqlprofile_name := DBMS_SQLTUNE.ACCEPT_SQL_PROFILE(
task_name => 'large_table_task',
profile_name => 'large_table_profile');
END;

改变SQL PROFILE
begin
DBMS_SQLTUNE.ALTER_SQL_PROFILE(
name => 'large_table_profile',
Atribute_name =>'STATUS',
Value => 'ENABLES');
END;

丢弃SQL PROFILE
BEGIN
DBMS_SQLTUNE.DROP_SQL_PROFILE(
Name => 'large_table_profile');
END;

管理SQL调整集
创建SQL调整集
BEGIN
DBMS_SQLTUNE.CREATE_SQLSET(
Sqlset_name => 'load_proc_set',
Description => 'SQL used in load procedure');
END;

加载SQL调整集
DECLARE
sql_cursor DBMS_SQLTUNE.SQLSET_CURSOR;
begin_snap number := 1; /* beginning snapshot id
end_snap number := 5; /* end snapshot id */
BEGIN
open sql_cursor for
select value(p)
from table (DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(begin_snap, end_snap) p;
DBMS_SQLTUNE.LOAD_SQLSET(
sqlset_name => 'load_proc_set',
populate_cursor => sql_cursor);
END;
/

SQL Loader工具
○ sqlldr scott/tiger control=loader.ctl
○ loader.ctl 如下:
load data
infile 'c:\data\mydata.csv'
into table emp
fields terminated by "," optionally enclosed by '"'
(empno, empname, sal, deptno )
○ mydata.csv 如下:
10001,"Scott Tiger", 1000, 40
10002,"Frank Naude", 500, 20

创建Program
BEGIN
DBMS_SCHEDULER.CREATE_PROGRAM(
program_name => 'CALC_STATS2',
program_action => 'HR.UPDATE_HR_SCHEMA_STATS',
program_type => 'STORED_PROCEDURE',
enabled => TRUE);
END;
/

创建Schedules
BEGIN
DBMS_SCHEDULER.CREATE_SCHEDULE(
schedule_name => 'stats_schedule',
start_date => SYSTIMESTAMP,
end_date => SYSTIMESTAMP + 30,
repeat_interval =>
'FREQ=HOURLY;INTERVAL=1',
comments => 'Every hour');
END;
/

创建Time-base Job
BEGIN
DBMS_SCHEDULER.CREATE_JOB(
job_name=>'HR.DO_BACKUP',
job_type => 'EXECUTABLE',
job_action => '/home/usr/dba/rman/nightly_incr.sh',
start_date=> SYSDATE,
repeat_interval=>'FREQ=DAILY;BYHOUR=23',
/* next night at 11:00 PM */
comments => 'Nightly incremental backups');
END;
/

创建Event-base Job
BEGIN
DBMS_SCHEDULER.CREATE_JOB(
job_name=>'ADMIN.PERFORM_DATA_LOAD',
job_type => 'EXECUTABLE',
job_action => '/home/usr/dba/rman/report_failure.sh',
start_date => SYSTIMESTAMP,
event_condition => 'tab.user_data.object_owner =
''HR'' and tab.user_data.object_name = ''DATA.TXT''
and tab.user_data.event_type = ''FILE_ARRIVAL''
and tab.user_data.event_timestamp < 9 ',
queue_spec => 'HR.LOAD_JOB_EVENT_Q');
END;

执行JOB
DBMS_SCHEDULER.SET_ATTRIBUTE('hr.do_backup', 'raise_events',DBMS_SCHEDULER.JOB_FAILED);

查询JOB
SELECT job_name, status, error#, run_duration FROMUSER_SCHEDULER_JOB_RUN_DETAILS;

Disk Group
1.创建
CREATE DISKGROUP dgroupA NORMAL REDUNDANCY
FAILGROUP controller1 DISK
'/devices/A1' NAME diskA1 SIZE 120G FORCE,
'/devices/A2',
'/devices/A3'
FAILGROUP controller2 DISK
'/devices/B1',
'/devices/B2',
'/devices/B3';

2.删除
DROP DISKGROUP dgroupA INCLUDING CONTENTS;

3.修改
ALTER DISKGROUP dgroupA ADD DISK
'/dev/rdsk/c0t4d0s2' NAME A5,
'/dev/rdsk/c0t5d0s2' NAME A6,
'/dev/rdsk/c0t6d0s2' NAME A7,
'/dev/rdsk/c0t7d0s2' NAME A8;
ALTER DISKGROUP dgroupA ADD DISK '/devices/A*';
ALTER DISKGROUP dgroupA DROP DISK A5;

转自：http://blog.csdn.net/u010740597/article/category/2259475