-
编辑 删除
更改归档模式
shutdown immediatestartup mount;alter database archivelog;alter system set log_archive_dest_1='location=1';alter database open;archive log list;alter system switch logfile;select name from v$archived_log;rman target / nocatalog log /oracle/rman/rman_log/rman_full_JDE_...
-
编辑 删除
分区表常见汇总
ERROR at line 16:ORA-14037: partition bound of partition "INPUTDATEP01" is too highSQL> alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';Session altered.
-
编辑 删除
linux下RMAN全备脚本
export ORACLE_BASE=/oracleexport ORACLE_HOME=/oracle/product/11.2.0.4/db_1export PATH=$PATH:$ORACLE_HOME/bin:$HOME/binexport ORACLE_SID=JDEexport NLS_LANG=AMERICAN_AMERICA.ZHS16GBK#export NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1date_format=`date +%y-%m-...
-
编辑 删除
查看表空间的增长情况
首先查看表空间的增长情况： 查询前一周表空间增长情况：select C.tablespace_name, D."Total(MB)", D."Used(MB)" -C."Used(MB)" AS "Increment(MB)", to_char(next_day(trunc(sysda...
-
编辑 删除
异步IO介绍
1，Linux下，ASM数据库和文件系统数据库的AIO设置差别：----(1). ASM的AIO属性是不受 FILESYSTEMIO_OPTIONS 参数的影响（因为ASM会绕过文件系统buffer），只跟DISK_ASYNCH_IO有关系----(2). 文件系统的AIO属性跟 FILESYSTEMIO_OPTIONS 和 DISK_ASYNCH_IO 都有关系2，FILESYSTEMIO_OPTIONS=NONE ： Bug 6733627 – Unacco...
-
编辑 删除
ASM实例内存调整
RAC数据库出现了归档失败的情况，导致单个实例出现HANG死的状况。ARC1: Error 19504 Creating archive log file TO '+DATA02'ARCH: Archival stopped, error occurred. Will continue retryingORACLE Instance orcl1 - Archival ErrorORA-16038: log 14 SEQUENCE# 68244 cannot be archivedORA-19504: ...
-
编辑 删除
查询lob字段
01:43:34 rodman@RODMAN>create table test(tupian clob);Table created.01:44:33 rodman@RODMAN>insert into test values('AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA');1 row created.01:45:30 rodman@RODMAN>commit;Commit complete.01:45:33 rodman@RODMAN>SELECT A.TABLE_NAME, 01:45:41 ...
-
编辑 删除
大表删除数据
大表删数据创建表结构create table new_rodman nologging as select * from rodman where 1=2;INSERT /*+ append parallel */ into select * from rodman where 保留的数据
-
编辑 删除
回收站浅谈
17:57:58 sys@RODMAN>show parameter recycleNAME TYPE VALUE------------------------------------ ----------- ------------------------------buffer_pool_recycle ...
-
编辑 删除
字符集查询
col value for a30select * from nls_database_parameters where parameter like '%CHARACTERSET%';PARAMETER VALUE------------------------------ ------------------------------NLS_CHARACTERSET  ...
-
编辑 删除
统计信息收集
用户级别$ sqlplus / as sysdbaSql> BEGIN? SYS.DBMS_STATS.GATHER_SCHEMA_STATS (? ? ? OwnName ? ? ? ?=> 'ADMIN'? ? ,Estimate_Percent ?=> SYS.DBMS_STATS.AUTO_SAMPLE_SIZE? ? ,Degree ? ? ? ? ? ?=> 4?...
-
编辑 删除
sqlplus配置提示符，防止连错实例，连错用户
vi $ORACLE_HOME/sqlplus/admin/glogin.sql alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';define _editor=viset serveroutput on size 1000000set trimspool onset long 5000set linesize 100set pagesize 9999column plan_plus_exp format a80column global_name new_value gnamese...
-
编辑 删除
11G 创建scott方案
@$ORACLE_HOME/rdbms/admin/utlsampl.sql
-
编辑 删除
dblink调用在存储过程中无法编译，报错ORA-04052
解决办法：通过创建视图的方式处理。create view T_ZQKH_ACCOUNT_COMPARE_VIEW as select * from ADMIN.T_ZQKH_ACCOUNT_COMPARE@ZQZC; SELECT zqkh_id into V_ZQKHID FROM T_ZQKH_ACCOUNT_COMPARE_VIEW WHERE crm_id=V_ACCOUNTID;  ...
-
编辑 删除
expdp impdp
select * from dba_directories;create or replace directory admindb as 'C:\dmp';grant read, write on directory admindb to public;impdp system/system DIRECTORY=admindb SCHEMAS=admin DUMPFILE=adm-160304.dmp logfile=imp_admin.log PARALLEL=4 CLUSTER=N
-
编辑 删除
10046使用
oradebug setmypidoradebug event 10046 trace name context forever,level 12;分析的sql查找trc文件select d.value || '/' || lower(rtrim(i.instance, chr(0))) || '_ora_' ||p.spid|| '.trc' trace_file_name from (select p.spid ...
-
编辑 删除
事务
查询事务执行时间：with ltr as ( select to_char(sysdate,'YYYYMMDDHH24MISS') TM, s.sid, s.sql_id, s.sql_child_number, s.prev_sql_id, &nb...
-
编辑 删除
表空间基本管理
使用率：set line 132set wrap offselect t.*from (SELECT D.TABLESPACE_NAME,SPACE "SUM_SPACE(M)",BLOCKS SUM_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...
-
编辑 删除
用户基本管理
select USERNAME,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE from dba_users where ACCOUNT_STATUS='OPEN';create user rodman?identified by rodman?default tablespace users temporary tablespace TEMP;查看用户系统权限 select * from dba_sys_privs; select * from all_sys_privs; sel...
-
编辑 删除
替换在线日志组
查看日志组的大小和状态select group#,bytes/1024/1024/1024，status from v$log; 查看日志组成员的位置select member from v$logfile; 删除INACTIVE状态的日志组，重建需要的日志组，以此类推。alter database drop logfile group 1;alter database add logfil...
