oracle问题整理
SELECT DBMS_METADATA.GET_DDL('VIEW',u.view_name) FROM USER_VIEWS u;
归档/非归档inactive/ active/ current
CTIVE、INACTIVE、KILLED、 CACHED、SNIPED
CREATE OR REPLACE PROCEDURE SYS.DB_KILL_IDLE_CLIENTS AUTHID DEFINER AS
job_no number;
num_of_kills number := 0;
BEGIN
FOR REC IN
(SELECT SID, SERIAL#, INST_ID, MODULE,STATUS
FROM gv$session S
WHERE S.USERNAME IS NOT NULL
AND UPPER(S.PROGRAM) IN ('xxx', 'xxx.EXE')
AND S.LAST_CALL_ET >= 2*60*60
AND S.STATUS= 'INACTIVE'
ORDER BY INST_ID ASCto_timestamp
) LOOP
---------------------------------------------------------------------------
-- kill inactive sessions immediately
---------------------------------------------------------------------------
DBMS_OUTPUT.PUT('LOCAL SID ' || rec.sid || '(' || rec.module || ')');
【execute immediate 'alter system kill session ''' || rec.sid || ', ' ||
rec.serial# || '''immediate' ;】
/【execute immediate 'alter system disconnect session ''' || rec.sid || ', ' ||rec.serial# || '''immediate' ;】
DBMS_OUTPUT.PUT_LINE('. killed locally ' || job_no);
num_of_kills := num_of_kills + 1;
END LOOP;
DBMS_OUTPUT.PUT_LINE ('Number of killed xxxx system sessions: ' || num_of_kills);
END DB_KILL_IDLE_CLIENTS;
1.term命令：
当和SPOOL命令联合使用时，可以取消SQLPLUS输出，查询结果仅仅存在于假脱机文件中
set term on：查询结果既显示于假脱机文件中，又在SQLPLUS中显示；
set term off：查询结果仅仅显示于假脱机文件中。
2.其他命令：
SQL>set colsep'|'; //-域输出分隔符
SQL>set echo off; //显示start启动的脚本中的每个sql命令，缺省为on
SQL> set echo on //设置运行命令是是否显示语句
SQL> set feedback on; //设置显示“已选择XX行”
SQL>set feedback off; //回显本次sql命令处理的记录条数，缺省为on
SQL>set heading off; //输出域标题，缺省为on
SQL>set pagesize 0; //输出每页行数，缺省为24,为了避免分页，可设定为0。
SQL>set linesize 80; //输出一行字符个数，缺省为80
SQL>set numwidth 12; //输出number类型域长度，缺省为10
SQL>set termout off; //显示脚本中的命令的执行结果，缺省为on
SQL>set trimout on; //去除标准输出每行的拖尾空格，缺省为off
SQL>set trimspool on; //去除重定向（spool）输出每行的拖尾空格，缺省为off
SQL>set serveroutput on; //设置允许显示输出类似dbms_output
SQL> set timing on; //设置显示“已用时间：XXXX”
SQL> set autotrace on; //设置允许对执行的sql进行分析
set wrap on;设置换行
set verify off //可以关闭和打开提示确认信息old 1和new 1的显示.
将oracle的ddl定义语言保存成txt文，在命令窗口执行
set echo off --设置信息
set feedback off
set heading off
Spool f:\temp\test.txt --开始spool命令
select * from t_workforms; --记住，这里有一个“;”，你想执行的sql语句
SELECT DBMS_METADATA.GET_DDL('VIEW',u.view_name)FROM USER_VIEWS u;
spool off; --记住，这里也有一个“;”
到处数据到处txt文件
set time off echo off head off;
set heading off;
set term off;
set pagesize 0;
set colsep ","; ---对导出数据相关信息列后以 逗号分隔
set trims on;
set feedback off;
set linesize 120;
spool C:\辽宁号码.txt;
select a,b from tac;
spool off;
/quit/
删除数据库数据创建的所有表
1.plsql种先查出语句
select 'drop table '|| table_name ||' cascade constraints' sql1 from user_tables;
2切换成一条语句
3执行语句
1.oracle resource/角色(create
cluster,indextype,table,sequence,type,procedure,trigger)
grant imp_full_database to gyrx_plm1;
unlimited tablespace, execute any procedure
查询权限
select * from user_sys_privs
被赋权用户只能有自己用户下的有限对象
2.SELECT ... FOR UPDATE [OF column_list][WAIT n|NOWAIT][SKIP LOCKED];
select xxx for update nowait;
3.创建唯一索引
create unique index idx_name on table
4.定时任务调度
删除job:dbms_job.remove(jobno);
修改要执行的操作:job:dbms_job.what(jobno,what);
修改下次执行时间：dbms_job.next_date(job,next_date);
修改间隔时间：dbms_job.interval(job,interval);
停止job:dbms.broken(job,broken,nextdate);
启动job:dbms_job.run(jobno);
第一种调度任务需求的日期算法比较简单，即'SYSDATE+n',这里n是一个以天为单位的时间间隔。
描述 Interval参数值
每天运行一次 'SYSDATE + 1'
每小时运行一次 'SYSDATE + 1/24'
每10分钟运行一次 'SYSDATE + 10/（60*24）'
每30秒运行一次 'SYSDATE + 30/(60*24*60)'
每隔一星期运行一次 'SYSDATE + 7'
不再运行该任务并删除它 NULL
第二种调度任务需求相对于第一种就需要更复杂的时间间隔（interval）表达式
描述 INTERVAL参数值
每天午夜12点 'TRUNC(SYSDATE + 1)'
每天早上8点30分 'TRUNC(SYSDATE + 1) + （8*60+30）/(24*60)'
每星期二中午12点 'NEXT_DAY(TRUNC(SYSDATE ), ''TUESDAY'' ) + 12/24'
每个月第一天的午夜12点 'TRUNC(LAST_DAY(SYSDATE ) + 1)'
每个季度最后一天的晚上11点 'TRUNC(ADD_MONTHS(SYSDATE + 2/24, 3 ), 'Q' ) -1/24'
每星期六和日早上6点10分 'TRUNC(LEAST(NEXT_DAY(SYSDATE, ''SATURDAY"), NEXT_DAY(SYSDATE, "SUNDAY"))) + （6×60+10）/（24×60）'
5.大量操作数据，划分事物
6.存储过程遍历字段值
存储过程
s_sql clob:='';
------------------------------------------------------------------------------------
varname varchar2(20);
for wf in(SELECT sequence_name FROM user_sequences where sequence_name like 'GZ%')
loop
(wf.sequence_name)条件
s_sql:=s_sql||'select 12345';
end loop;
select sequence_name into varname from user_sequences where rownum = 1;
例如
declare
s_sql clob:='';
varname varchar2(20);
begin
dbms_output.enable(buffer_size => NULL);
for wf in(SELECT dwzd_bh FROM stat_dwzd)
loop
if length(wf.dwzd_bh)<7 then
s_sql := s_sql||'create';
dbms_output.put_line(s_sql);
end if;
end loop;
end;
塞值应用
declare
varname varchar2(20);
begin
select sequence_name into varname from user_sequences where rownum = 1;
dbms_output.put_line(varname);
end;
——————————————————————————————————————————
declare
cursor [del_cursor] is select a.*, a.rowid row_id from [table_name] a order by a.rowid;
begin
for v_cusor in [del_cursor] loop
if v_cusor.[time_stamp] < to_date('2014-01-01','yyyy-mm-dd') then
delete from [table_name] where rowid = v_cusor.row_id;
end if;
if mod([del_cursor]%rowcount,1000)=0 then
commit;
end if;
end loop;
commit;
end;
例如
declare
cursor rcursor is select * from stat_dwzd;
i int;
begin
i:=0;
dbms_output.enable(buffer_size => NULL);
for v_cusor in rcursor loop
if substr(v_cusor.dwzd_bh,0,1)='6' then
dbms_output.put_line(v_cusor.dwzd_bh);
end if;
if mod(rcursor%rowcount,10)=0 then
i :=i+1;
dbms_output.put_line(v_cusor.dwzd_bh);
dbms_output.put_line(i);
end if;
end loop;
end;
7.
declare
maxrows number default 1000;
delete_ct number default 0;
begin
select count(1)/maxrows into delete_ct from [table_name] where [time_stamp] < to_date('2014-01-01','yyyy-mm-dd');
for i in 1..TRUNC(delete_ct)+1
loop
delete [table_name] where [time_stamp] < to_date('2014-01-01','yyyy-mm-dd') and rownum <= maxrows;
commit;
end loop;
end;
8.对于报错的索引或
alter index idx_state unusable;
alter index idx_state rebuild;
9.当FETCH语句从游标获得数据(当前记录的数据加载到变量中)
10.用call D:\xx\xxx.bat command执行bat文件
11.lpad(14,5,8)对14左填充8补足5位
12.(to_char(sysdate,'DY')='星期日') THEN
RAISE_APPLICATION_ERROR(-20600,'不能在周末修改表tb_emp');将服务器错误传给客户端
9、NVL2(E1, E2, E3)的功能为：如果E1为NULL，则函数返回E3，若E1不为null，则返回E2。
10、oracle sign 函数>0返回1 <0返回-1 等于0 返回0
11、修改oracle数据库名和实例名
nid target=sys/oracle dbname=jsl setname=y
alter database backup controlfile to trace;
alter database backup controlfile to trace as '/home/oracle/c1.sql' reuse;
12.控制、参数、数据、日志
select * from V$CONTROLFILE
select * from V$PARAMETER where name like '%spfile%'
select * from V$DATAFILE
select * from V$LOGFILE
如果连接密码中有符号需要在前边加反斜杠
15.密码文件
dbhome_1\database\PWDorcl.ora
16.截取小数，不四舍五入
TRUNC（89.985，2）=89.98
select trunc(123.567,-2) from dual;
--100,第二个参数可以为负数，表示将小数点左边指定位数后面的部分截去，即均以0记;
trunc（sysdate,'yyyy'） --返回当年第一天。
trunc（sysdate,'mm'） --返回当月第一天。
trunc（sysdate,'d'） --返回当前星期的第一天。星期天
trunc（sysdate,'q'） --本季度第一天日期
trunc（sysdate,'dd'）--返回当前年月日
trunc（sysdate,'ww'）--返回当前星期的星期一。
trunc( sysdate,'hh24')--得到按小时取整的时间
to_char(trunc(sysdate,'ww'),'yyyy-ww')--返回当年+当前年的周序号
trunc(sysdate ,'HH24')--返回本小时的开始时间
trunc(sysdate ,'MI')+3--返回本分钟的开始时间后推三天
17.oracle 分析函数
整理杂乱的英文initcap
initcap('this TEXT hAd UNpredictABLE caSE')
找子串在字符串中的位置instr
instr('this is line one','line',1)
userenv('terminal')
返回增减月后的日期ADD_MONTHS（起始日期，增减月数）
select add_months('26-10月-05',2) from dual;
select add_months('26-10月-05',-2)from dual;
返回当前月最后一台年
select last_day(’21-2月-80‘) from dual;
返回两个日期间的月份
months_between(’12-10月-05‘,’12-9月-03‘)
18.格式化数字
to_char(5764.12345,'99,999.9999')
19.修改数据库用户的session变更时间格式
select * from v$nls_parameters;
AM/PM用上午/下午代替
alter session set NLS_DATE_LANGUAGE = 'SIMPLIFIED CHINESE'; /AMERICAN
alter session set NLS_DATE_FORMAT='MM/DD/YYYY HH:MI:SS AM';
修改数据库编码，乱码问题
20.备份数据 快照恢复
ALTER TABLE tableName ENABLE row movement ;
flashback table tableName to timestamp to_timestamp(''2018-03-12 09:40:00'',''yyyy-mm-dd hh24:mi:ss'');
flashback table CW_CB_YYFYmX to timestamp to_timestamp('2018-03-12 09:40:00','yyyy-mm-dd hh24:mi:ss');
ALTER TABLE CW_CB_YYFYmX ENABLE row movement ;
insert into TEST select * from TEST as of timestamp to_Date('2019-02-21 11:35:00', 'yyyy-mm-dd hh24:mi:ss');
21.查询表的所有列
SELECT LISTAGG(F_NAME,',') WITHIN GROUP (ORDER BY F_SX) AS F_
NAME FROM DW_PZB
22.oracle会话数、连接数修改
ORACLE的会话数和连接数参数配置
以sysdba身份登录
sqlplus sys/xxxx as sysdba;
查看最大连接数：
show parameter processes;
show parameter sessions;
查看当前最大连接数：
select count(*) from v$process;
select count(*) from v$session;
修改最大连接数：
alter system set processes=1500 scope=spfile;
alter system set sessions=2000 scope=spfile;
当前连接数（并发）
Select count(*) from v$session where status='ACTIVE'
数据库允许得最大连接数
select value from v$parameter where name = 'processes'
创建pfile
create pfile from spfile;
重启数据库：
shutdown immediate;
startup;
23.操作系统身份认证、密码文件认证、数据库认证
nolog是不进行登录，仅仅进入sqlplus软件而已
24.排序
order by case when trim(F_MXBH) is not null THEN TO_NUMBER(F_MXBH) else 100 end
25锁表
非线程类里边调Thread.sleep(100);是什么意思
alter system kill session '16,21404';
select s.sid,
s.serial#,
object_name,
machine,
s.username,
s.OSUSER,
s.CLIENT_INFO,
s.logon_time,
s.PROGRAM,
s.MODULE
from v$locked_object l, dba_objects o, v$session s
where l.object_id = o.object_id
and l.session_id = s.sid;
锁表杀掉进程
select spid,osuser,s.program from v$session s,v$process p where s.paddr=p.addr and s.sid = 24
windows orakill sid thread
26.将列拼接用‘，’隔开
SELECT LISTAGG(F_NAME,',') WITHIN GROUP (ORDER BY F_SX) AS F_NAME FROM DW_PZB
27.查询时间格式TRUNC（TO_DATE（'24-Nov-1999 08:00 pm'），'dd-mon-yyyy hh:mi am'）
='24-Nov-1999 12:00:00 am'
TRUNC（TO_DATE（'24-Nov-1999 08:37 pm','dd-mon-yyyy hh:mi am'），'hh'） ='24-Nov-1999 08:00:00 am'
SQL> select TO_Date( '22/10/2009 12:00:00 上午', 'DD/MM/YYYY HH:MI:SS AM') from dual;
SQL> alter session set NLS_DATE_FORMAT='MM/DD/YYYY HH:MI:SS AM';
28.数据库参数表对应视图无法修改
dynamic performance tables not assible automatic statistics disabled for this session you can disable statistics in the perference menu ,or obtain select priviliges on the v$seesion,v$sesstat and v$statname tables
V_$session是V$session的具体视图，因为V$session是同名不是具体的视图
V_$sesstat
V_$statname
29.组合查找唯一关联编号
select substr(f_mc,1,instr(f_mc,'--',-1)-1) F_QSRBH,substr(f_mc,instr(f_mc,'-',-1)+1) F_QSRMC from (select DISTINCT F_QSRBH||'--'||F_QSRMC f_mc from CF_ZJ_CDHPTZ);
30.修改用户对表的权限
1.grant select on V_$session to user_sec;
2.grant SELECT ANY DICTIONARY to user_sec;
ALTER TABLE tableName ENABLE row movement;//允许行移动操作，使用Flashback table 的时候必须先操作
flashback table tableName to timestamp to_timestamp(''2018-03-12 09:40:00'',''yyyy-mm-dd hh24:mi:ss'');闪回表将表回复到之前一个时间点
3.查看所有用户
select * from dba_users; 查看数据库里面所有用户，前提是你是有dba权限的帐号，如sys,system
select * from all_users; 查看你能管理的所有用户！
select * from user_users; 查看当前用户信息 ！
配置归档
alter system set cluster_database=false scope=spfile
alter system switch logfile;切换重做日志
startup mount
startup nomount
alter database archivelog;
修改归档日志间隔
alter system set archive_lag_target=1800;（秒,默认0）
归档日志使用查看开启状态
archive log list
shutdown immediate; –关闭数据库
4.2 startup mount; – 打开数据库
4.3 alter database archivelog;—开启归档日志
5.3 alter database noarchivelog; --关闭
4.4 alter database open;–开启数据库
手动开启：
alter system archive log current;
自动开启：
alter system archive log start;
查询对应归档日志文件/修改归档日志路径
alter system set log_archive_dest_1='location=D:\oracle\archlog mandatory';//mandatory表时强制性
show parameter log_archive_format
linux查看归档日志crontab -l
修改闪回空间大小，路径
ALTER SYSTEM SET db_recovery_file_dest_size=4g scope=both;
alter system set db_recovery_file_dest='+DATAASM/orcl/archivelog' scope=spfile;
show parameter db_recovery_file_dest_size;
1.1. 获取当前数据库的scn号
select current_scn from v$database; (切换到sys用户或system用户查询)
select dbms_flashback.get_system_change_number from dual;
查询到的scn号为：1499220
flashback table tble to before drop rename to plm_basicinfo1;
select *from tble as of timestamp(systimestamp - interval '36000' second)
select tablespace_name,block_size,extent_management , segment_space_management,contents,retention from dba_tablespaces;
alter system set undo_retention=900 scope=both;--修改快照保留时间
alter tablespace undotbs add datafile 'c:\oracle\undotbs1.dbf' size 100M autoextend on next 128M maxsize 14G;
select max(undoblks / ((end_time - begin_time)*24*3600)) from v$undostat;--业务高峰期每秒产生undo数据块的个数
show parameter undo_retention;--保留的最长时间
show parameter db_blo;--数据块大小
alter tablespace undotbs retention guarantee;--需要最小表控件为上三个参数相乘
1.2. 查询当前scn之前的scn
select * from 表名 as of scn 1499220; (确定删除的数据是否存在，如果存在，则恢复数据；如果不是，则继续缩小scn号)
查看是否闪回
select log_mode,open_mode,flashback_on from v$database;
--显示归档日志信息
select name,sequence#,first_change# from v$archived_log;
--归档日志位置
select destination ,binding,target,status from v$archive_dest;
手动归档：
ALTER DATABASE ARCHIVELOG MANUAL
ALTER SYSTEM ARCHIVELOG ALL|CURRENT
使用归档日志恢复数据库
全部restore archivelog all
5到8这四个归档日志restore archivelog from logseq 5 until logseq 8;
从第5个归档日志起restore archivelog from logseq 5
恢复7天内的归档日志restore archivelog from time 'sysdate-7' ;
restore archivelog sequence between 1 and 3;
恢复到哪个日志文件为止
restore archivelog low logseq 5;
到第5个日志为止 restore archivelog high logseq 5;
恢复到另外路径下
run{
set archivelog destination to 'd:\backup'
allocate channel ci type disk;
set archivelog destination to 'd:\backup' ;
restore archivelog all ;
release channel ci;
}
配置归档日志进程 ：ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=3（默认进程数目为2，
一般不需要修改该参数，Oracle会根据归档情况自动启动额外的归档进程）
配置归档日志位置：默认归档日志位于db_recovery_file_dest 缺省为$ORACLE_BASE/flash_recovey_area
配置1-2个：
ALTER SYSTEM SET LOG_ARCHIVE_DEST='路径'；
ALTER SYSTEM SET LOG_ARCHIVE_duplex_DEST='路径'；
配置多个：
ALTER SYSTEM SET LOG_ARCHIVE_DEST=''；
ALTER SYSTEM SET LOG_ARCHIVE_duplex_DEST=''；
ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='location=路径'
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='location=路径'
ALTER SYSTEM SET LOG_ARCHIVE_DEST_3='location=路径'
ALTER SYSTEM SET LOG_ARCHIVE_DEST_4='service=standby'
（ 对于远程归档位置,SERVICE选项需要指定远程数据库的网络服务名(在tnsnames.ora文件中配置)）
归档日志命名格式
设置LOG_ARCHIVE_FORMAT参数，Unix下的缺省设置为LOG_ARCHIVE_FORMAT=%t_%s_%r.dbf
%s: 日志序列号:
%S: 日志序列号(带有前导)
%t: 重做线程编号.
%T: 重做线程编号(带有前导)
%a: 活动ID号
%d: 数据库ID号
%r RESETLOGS的ID值.
alter system set log_archive_format = 'arch_%t_%s_%r.arc';
4、查看下归档日志空间情况：select * from v$recovery_file_dest;
5、增加归档日志空间大小： alter system set db_recovery_file_dest_size=20G scope=both;（这里增加20G，可以自行调整）
rman使用
rman，rman target 用户名/口令@例程
输入list archivelog all； -- 显示所有的日志及日志地址
输入crosscheck archivelog all; --运行这个命令可以把无效的expired的archivelog标出来
输入delete expired archivelog all； ---删除所有的无效日志或者输入delete noprompt archivelog until time "sysdate -3"; -- -即删 除3天前的归档日志
恢复数据库recover database;
recover database skip tablespace TS0611_NEW;
recover database skip forever tablespace ts0611_new;
alter tablespace ts0611_new online;
alter tablespace db320 read only;
执行带until time报错解决 方法（执行下方之后）
run{sql 'alter session set NLS_DATE_FORMAT="YYYY-MM-DD HH24:MI:SS"';
sql 'alter session set NLS_LANGUAGE="AMERICAN"';}
recover database until scn 12723362144133;move
recover database until scn 12723362144133 skip tablespace ts0611_new;
restore database until time '20150625 09:52:50';
recover database until time '20150625 09:52:50';
recover database skip forever tablespace ts0625_1 until time '20150625 09:52:50';
restore controlfile from autobackup until time '2009-03-10 18:15:00';
时间与scn转化
select ('31-OCT-18 01.29.58.000000000 PM') from dual;
select to_char(scn_to_timestamp(1123574))from dual;
用控制文件恢复
startup nomount;
restore controlfile from 'C:\app\sxshe\oradata\orcl\CONTROL01.CTL'
list incarnation;
[SQLCOMMAND]recover database using backup controlfile;
recover database using backup controlfile until cancel;
recover database until cancel using backup controlfile ;
recover database until cancel
指定日志文件C:\app\sxshe\oradata\orcl\REDO03.LOG
或者 自动获取归档日志文件 系统提示日志时输入 auto
startup mount;
recover database;
alter database open resetlogs;
alter system set "_allow_resetlogs_corruption"=TRUE scope=spfile;
_allow_resetlogs_corruption=true
_allow_error_simulation=true
重启
验证有效性数据/控制文件/参数文件备份
restore validate database/controlfile/spfile;
RMAN> backup database;
backup as compressed backupset database;
list backup 列出详细备份
list expired backup; 列出过期备份;
list backup of database; 列出所有数据文件的备份集；
list backup of tablespace user01; 列出特定表空间的所有数据文件备份集；
list backup of controlfile 列出控制文件备份集;
list backup of archivelog all 列出归档日志备份集详细信息;
list archivelog all;列出归档日志备份简要信息
list backup of spfile 列出spfile备份集
list copy of datafile 5列出数据文件映像副本
list copy of controlfile 列出控制文件映像副本
list copy of archivelog all;列出归档日志映像副本
list incarnation of database 列出对应物/列出数据库副本
list backup summary概述可用得备份
list backup by file按数据/归档/控制列出备份
delete archivelog until time "sysdate-10"; --删除10天前的归档日志。或者：delete noprompt archivelog until time "sysdate - 10"
delete archivelog until time "to_date('2018-08-01 23:59:59','yyyy-mm-dd hh24:mi:ss')"; --删除指定日期之前的所有归档日志
crosscheck archivelog all; -- 运行这个命令可以把无效的expired的archivelog标出来。
delete expired archivelog all; ----删除过期的归档日志
归档日志空间使用select sum(percent_space_used) from v$flash_recovery_area_usage;
指定retention得策略(防止备份占用过大)configure retention policy to recovery window of 7 days;
srvctl status database -d orcl -v
srvctl config database -d orcl
查看所有表空间文件位置
select tablespace_name,file_id,bytes/1024/1024,file_name from dba_data_files
LIST INCARNATION查看隐身
list incarnation of database "test";
reset database to incarnation 4;
sql "alter session set nls_date_format=''yyyy-mm-dd hh24:mi:ss''";
run{
set until scn 765649534;
restore database;
recover database tablespace SYSTEM01.DBF;
alter database open resetlogs;
}
表空间恢复
SQL 'ALTER TABLESPACE EXAMPLE OFFLINE IMMEDIATE';
RESTORE TABLESPACE EXAMPLE;
RECOVER TABLESPACE EXAMPLE;
SQL 'ALTER TABLESPACE EXAMPLE ONLINE'
数据文件恢复
SQL 'ALTER DATABASE DATAFILE 5 OFFLINE';
RESTORE DATAFILE 5;
RECOVER DATAFILE 5;
SQL 'ALTER DATABASE DATAFILE 5 ONLINE';
恢复归档日志文件
RUN{
SET ARCHIVELOG DESTINATION TO 'BACKUP\ARCLOG1';
RESTORE ARCHIVELOG SEQUENCE BETWEEN 15 AND 20;
SET ARCHIVELOG DESTINATION TO 'BACKUP\ARCLOG2';
RESTORE ARCHIVELOG SEQUENCE BETWEEN 21 AND 30;
SET ARCHIVELOG DESTINATION TO 'BACKUP\ARCLOG3';
RESTORE ARCHIVELOG SEQUENCE BETWEEN 31 AND 40;
}
控制文件的恢复1自动备份中恢复2从备份集中恢复
SET DBID=1415261003;
STARTUP NOMOUNT;
arc文件
RESTORE controlfile from autobackup/tag/double-quoted-string/single-quoted-string /'backup/261003-20090413-00';
bkp 文件
1.spfile文件恢复移动到当前spfile文件夹下替换当前
restore controlfile from 'C:/2019_04_11/1JW_.BKP';
run{
restore database;
recover database;
};
SET DBID=1415261003;
STARTUP NOMOUNT;
RESTORE SPFILE （to 'dbs/spfile.ora'） FROM AUTOBACKUP;
RESTORE SPFILE （to 'dbs/spfile.ora'） FROM 'c:/GBXN41JW_.BKP';
指定'backup spfile'命令备份的备份集
restore spfile from 'backupset/TAG20121125T153635_8c3ln41t_.bkp';
select name,status from v$datafile;
对数据库进行全备 BACKUP DATABASE; ORACLE_HOME/database目录下。
备份表空间backup tablespace jweb;
LIST BACKUP OF TABLESPACE JWEB;
备份指定数据文件
select file_name,file_id,tablespace_name from dba_data_files
BACKUP DATAFILE n;
LIST BACKUP OF DATAFILE n;
备份控制文件 CONFIGURE CONTROLFILE AUTOBACKUP ON;
跟踪备份控制文件alter database backup controlfile to trace;
手动执行备份BACKUP CURRENT CONTROLFILE;
指定INCLUDE CURRENT CONTROLFILE参数 BACKUP DATABASE INCLUDE CURRENT CONTROLFILE;
LIST BACKUP OF CONTROLFILE;
备份归档日志文件
BACKUP ARCHIVELOG ALL;
BACKUP DATABASE PLUS ARCHIVELOG;
ll $ORACLE_BASE/flash_recovery_area
plsql设置快捷输入方式
用户界面-编辑器-自动替换-定义文件->添加txt文件
在txt里边写select * from 当输入select回车就可以自动补全
手动增加数据文件尺寸
ALTER DATABASE DATAFILE '/oracle/oradata/db/GAME.dbf'
RESIZE 4000M;
修改表空间
设定数据文件自动扩展
ALTER DATABASE DATAFILE '/oracle/oradata/db/GAME.dbf
AUTOEXTEND ON NEXT 100M
MAXSIZE 10000M;
增加数据文件
ALTER TABLESPACE game
ADD DATAFILE '/oracle/oradata/db/GAME02.dbf' SIZE 1000M;
创建表空间
create （temporary/undo） tablespace SIRM2
datafile/tempfile 'D:\oracle\product\10.2.0\oradata\orcl\SIRM2.dbf' size 1024M
UNIFORM SIZE 128k; #指定区尺寸为128k,
autoextend on next 10M maxsize unlimited无限制扩展
EXTENT MANAGEMENT local autoallocate
segment space management auto;
创建用户
create user test identified by test
default tablespace SIRM2
temporary tablespace TEMP
profile DEFAULT;
删除表空间
DROP TABLESPACE data01 INCLUDING CONTENTS AND DATAFILES;
误删掉表空间恢复数据库方法
alter system set "_ALLOW_RESETLOGS_CORRUPTION"=true scope=spfile;
shutdown immediate;
startup mount;
alter database datafile '.../X1000_1.dbf' offline drop;
alter database open;
ALTER SYSTEM SET undo_tablespace=UNDOTBS02;
脱机 ALTER TABLESPACE game OFFLINE;
意外删除数据文件文件用 ALTER TABLESPACE game OFFLINE FOR RECOVER;
联机ALTER TABLESPACE game ONLINE;
使表空间只读
ALTER TABLESPACE game READ ONLY;
使表空间可读写
ALTER TABLESPACE game READ WRITE;
创建序列
create sequence SEQ_LOG_ID minvalue 1
maxvalue 9999999999 --/NOMAXvalue
start with 1 increment by 1
cache 50 --设置缓存cache个序列，如果系统down掉了或者其它情况将会导致序列不连续，也可以设置为---NOCACHE防止跳号
cycle;
alter sequence seq_name increment by 1;
SELECT SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5) Day,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'00',1,0)) H00,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'01',1,0)) H01,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'02',1,0)) H02,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'03',1,0)) H03,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'04',1,0)) H04,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'05',1,0)) H05,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'06',1,0)) H06,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'07',1,0)) H07,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'08',1,0)) H08,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'09',1,0)) H09,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'10',1,0)) H10,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'11',1,0)) H11,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'12',1,0)) H12,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'13',1,0)) H13,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'14',1,0)) H14,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'15',1,0)) H15,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'16',1,0)) H16,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'17',1,0)) H17,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'18',1,0)) H18,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'19',1,0)) H19,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'20',1,0)) H20,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'21',1,0)) H21,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'22',1,0)) H22,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'23',1,0)) H23,
COUNT(*) TOTAL
FROM v$log_history a
WHERE first_time>=to_char(sysdate-10)
GROUP BY SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5)
ORDER BY SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5) DESC
将所有表明和所有字段名变成大写
begin
for c in (select table_name tn from user_tables where table_name <> upper(table_name)) loop
begin
execute immediate 'alter table "'||c.tn||'" rename to '||c.tn;
exception
when others then
dbms_output.put_line(c.tn||'已存在');
end;
end loop;
end;
begin
for cl in (SELECT table_name,column_name from user_tab_columns WHERE column_name<>upper(column_name) and upper(column_name) not in('SIZE','CHECK')) loop
begin
execute immediate 'alter table '||cl.table_name||' rename column "'|| cl.column_name ||'" to '||upper(cl.column_name);
exception
when others then
dbms_output.put_line(cl.table_name||'.'||cl.column_name||'已存在');
end;
end loop;
end;
批量将表名变为大写
begin
for c in (select table_name tn from user_tables where table_name <> upper(table_name)) loop
begin
execute immediate 'alter table "'||c.tn||'" rename to '||c.tn;
exception
when others then
dbms_output.put_line(c.tn||'已存在');
end;
end loop;
end;
批量将空间内所有表的所有字段名变成大写 此方法可能导致溢出
begin
for t in (select table_name tn from user_tables) loop
begin
for c in (select column_name cn from user_tab_columns where table_name=t.tn) loop
begin
execute immediate 'alter table "'||t.tn||'" rename column "'||c.cn||'" to '||c.cn;
exception
when others then
dbms_output.put_line(t.tn||'.'||c.cn||'已经存在');
end;
end loop;
end;
end loop;
end;
将特点表PROPERTY_INFO的所有列名小写变大写
begin
for c in (select COLUMN_NAME cn from all_tab_columns where table_name='PROPERTY_INFO') loop
begin
execute immediate 'alter table PROPERTY_INFO rename column "'||c.cn||'" to '||c.cn;
exception
when others then
dbms_output.put_line('PROPERTY_INFO'||'.'||c.cn||'已经存在');
end;
end loop;
end;
将表名和字段名改为小写
begin
for c in (select table_name tn from user_tables where table_name <> lower(table_name)) loop
begin
execute immediate 'alter table '||c.tn||' rename to "'||lower(c.tn)||'"';
exception
when others then
dbms_output.put_line(c.tn||'已存在');
end;
end loop;
end;
begin
for t in (select table_name tn from user_tables) loop
begin
for c in (select column_name cn from user_tab_columns where table_name=t.tn) loop
begin
execute immediate 'alter table '||t.tn||' rename column '||c.cn||' to "'||lower(c.cn)||'"';
exception
when others then
dbms_output.put_line(t.tn||'.'||c.cn||'已经存在');
end;
end loop;
end;
end loop;
end;
