![](https://img-blog.csdnimg.cn/20201014180756923.png?x-oss-process=image/resize,m_fixed,h_64,w_64)
oracle日记
文章平均质量分 64
jiawancai
IT
展开
-
Remove a job without connecting as job owner
In order to use dbms_job.rmove, you must be connected as the owner of the job. As DBA, you want to remove another user job: - create a procedure owned by the user that does an execute immediate - call转载 2007-01-24 18:52:00 · 698 阅读 · 0 评论 -
size tablespace autoextend and temp managed
SELECT tablespace_name, round(SUM(used)/1024/1024) USED_MB,round((SUM(total)/1024/1024),0) MAXSIZE_MB,TO_CHAR(100*SUM(used)/SUM(total),999,99)|| % Pct_Used FROM ( SELECT what,tablesp转载 2007-01-12 13:42:00 · 627 阅读 · 0 评论 -
Finding duplicate indexes
Ive seen a note on deleting duplicate indexes! This should be done very carefuly, as some indexes can have same leading column, but are both needed! Here is a script that will help you find indexes w转载 2007-01-12 13:37:00 · 432 阅读 · 0 评论 -
怎样安装statspack
STATSPACK is the successor of BSTAT and ESTAT utilities. It is recommended to set timed_statistics to true. To install STATSPACK follow the steps below: 1. Create PERFSTAT Tablespace: SQL> CREATE TABL转载 2006-08-22 22:35:00 · 555 阅读 · 0 评论 -
热备份脚本(从一个服务器到另一个服务器)
/**** HOT Backup Script *******/define INT_PWD =define HOT_BACK_DIR =/* Create Temporary Table with Tablespace Data */drop table system.dsc_hot_stage;create table system.dsc_hot_stage(tablespa转载 2006-08-22 22:10:00 · 715 阅读 · 0 评论 -
列出表空间的使用情况
List tablespaces - space allocated - space used - %free (without maxbytes) - Autoextend Y|N|Y/N - Sum of maxbytes - increment (in Mb) 10/100 or 0/10/.... if more than 2 datafiles with different increm转载 2006-08-22 22:17:00 · 580 阅读 · 0 评论 -
creating recovery catalog
create a recovery catalog The simple steps to create a recovery catalog in rman is as below:- OUTLINE: 1). Create tablespace 2). Create catalog owner 3). Grant privileges 4). Create catalog 5). Connec转载 2006-12-16 13:42:00 · 595 阅读 · 0 评论 -
v$datafile_header related mistake in 9i OCP book
hi as mentioned in oracle 9i DBA FUNDAMENTALS-2 book that when you put a tablespace in begin backup mode.the FUZZY column in V$datafile_header set to YES...............(it was the case before oracle 9转载 2006-12-16 13:34:00 · 583 阅读 · 0 评论 -
Check if index is fragmented (needs reorg or coalesce)
define owner=FRANCK -- table ownerdefine table=SALES -- table namedefine index=SALES_TIME -- index namedefine buckets=10 -- number of bucketsdefine sampl转载 2006-12-16 13:38:00 · 663 阅读 · 0 评论 -
How to change SYSDATE's value to a static date without changing system date
First change the NLS_DATE_FORMAT to a recognizable full format: alter session set nls_date_format = dd.mon.yyyy hh24:mi:ss; Then change the system variable fixed_dates value: alter system set fixed转载 2007-01-12 13:47:00 · 462 阅读 · 0 评论 -
Creating a Data dictionary file to use with LogMi
Creating the Dictionary File To use LogMiner to analyze joedevo’s data, you must create a dictionary file before starting LogMiner. Take the following steps: 1. In the init.ora file, set the initializ转载 2007-01-13 16:06:00 · 832 阅读 · 0 评论 -
Trace event to check explain plan + bind variable peeking + hints + CBO environment
Here is description and example about usefull event in 10g: ORA-10132 dump plan after compilation When events 10046 level 4 and 10053 are too verbose, this event dumps - for each parsed query: - th转载 2007-01-13 16:09:00 · 624 阅读 · 0 评论 -
This script allows capturing and storing occured errors
------------------------------------------------------------------------------------- Filename: log_errors.sql --- Purpose: captures all database errors and writes error --- message a转载 2007-01-16 12:59:00 · 591 阅读 · 0 评论 -
Script to see invalid object (and unusable indexes)
set linesize 145set pagesize 1000set trimout onset trimspool onSet Feedback offset timing offset verify offpromptprompt -- ----------------------------------------------------------转载 2007-01-16 12:50:00 · 568 阅读 · 0 评论 -
Database realtime monitoring
create or replace type MySysstat as object ( REDO_BLOCKS NUMBER, LOGICAL_READS NUMBER, BLOCK_CHANGES NUMBER, PHYSICAL_READS NUMBER, PHYSICAL_WRITES NUMBER, USER_CALLS NUMBER, PARSES NUMBER, H转载 2007-01-16 12:57:00 · 611 阅读 · 0 评论 -
display a history load for a hash_value
WITH p AS (SELECT sysdate-5 /* start date */ bsnap , sysdate /* end date */ esnap, 2383739701 /* hash value */ hashv转载 2007-01-16 12:55:00 · 503 阅读 · 0 评论 -
put your own progress information in V$SESSION_LONGOPS
If only more developers used the DBMS_APPLICATION_INFO package. As a DBA I often see requests to investigate why a particular job is taking longer than expected, or to kill a session running a partic原创 2007-02-27 21:50:00 · 1137 阅读 · 0 评论 -
How to return a long columns size
create or replace function getLongsize (p_query varchar2) return number as v_cursor integer default dbms_sql.open_cursor; i number; v_temp varchar2(250); v_returne转载 2007-02-24 22:02:00 · 631 阅读 · 0 评论 -
Sql tricks: multiple rows in one output value
Hi, I was asked once how to make an SQL that will return the following result: DEPTNO NAME_LIST 1 Komers,Mokrel,Stenko 2 Hung,Tong 3 Hamer 4 Mansur from: LNAME DEPTNO Komers 1 Stenko 1 Mokrel 1 Hung 2转载 2007-01-13 16:14:00 · 656 阅读 · 0 评论 -
tablespace occuped with autoextent managment and tempfile
SELECT ddf.tablespace_name, SUM( distinct ddf.ddfbytes ) / 1048576 maxpossible, SUM( NVL( ds.dsbytes , 0 ) / 1048576 ) occuped ,(SUM( distinct ddf.ddfbytes )/ 1048576) - SUM( NVL( ds.dsby转载 2007-01-13 16:11:00 · 675 阅读 · 0 评论 -
Oracle Application Express 2.2出来了
Oracle Application Express 2.2出来了原创 2006-08-01 14:35:00 · 457 阅读 · 0 评论 -
删除所给模式中的所有对象
SET SERVEROUTPUT ONDECLARE l_count NUMBER; l_cascade VARCHAR2(40);BEGIN FOR i IN 1 .. 3 LOOP EXIT dependency_failure_loop WHEN l_count = 0; l_count := 0; FOR cur_转载 2006-08-01 14:33:00 · 533 阅读 · 0 评论 -
检查临时表空间的使用情况--脚本
select rpad(s.sid,5, ) sid,s.username,rpad(s.program,15, ) prgm,rpad(su.blocks*16384/(1024*1024)|| MB ,10, ) tempsize,su.segtype, su.tablespace from v$session s,v$sort_usage suwhere s.saddr=su转载 2006-08-01 14:32:00 · 535 阅读 · 0 评论 -
Checkpoint Tuning and Troubleshooting
PurposeThis article provides the Database Administrator a better understanding of checkpoint processing and a description of four key initialization parameters used for checkpoint tuning:转载 2006-11-04 21:42:00 · 659 阅读 · 0 评论 -
finding lock details on database
set echo offcol sid form 9999col id1 form 9999999999col id2 form 999999999col lmode head "Lock Held" form a14col request1 head "Lock Request" form a16col type head "Lock Type" form a15col ctime head "转载 2006-11-04 21:04:00 · 435 阅读 · 0 评论 -
RMAN backupset renaming or relocation
version : Oracle 10g R2 Here is the solution if in case we need to restore the backup from diffrent location in RMAN. Scenerio is given below...... 1.we took backup on /u01 for our Database. 2.we copi转载 2006-10-17 18:48:00 · 520 阅读 · 0 评论 -
redo坏了
第一种情况:redo坏了是在数据库关闭后SQL>recover database until cancel;SQL>alter database open resetlogs;第二种情况:多于member在group中, 如果有多于一个则还是可以直接clear 好了原创 2006-10-02 19:45:00 · 476 阅读 · 0 评论 -
我对PGA、sharedpool 、library cache的理解
我对PGA、sharedpool 、library cache的理解现在比较清楚了,现总结一下,以便其他人能参考。请大家指正当SQL开始执行时:1、首先在PGA中进行语法、语义检查,(当然还有打开CURSOR等),通过检查后,消耗CPU资源进行SQL HASH VALUE的计算。2、计算出HASH VALUE后,申请library cache latch,到library cache中检查是否转载 2006-10-02 19:29:00 · 983 阅读 · 0 评论 -
Rman中如何注销目录中已注册的数据库
注销数据库需要db_key和db_id两个值$sqlplus rman/rmanSQL>select * from db;SQL> exec dbms_rcvat.unregisterdatabase(db_key,db_id)原创 2006-10-02 19:28:00 · 601 阅读 · 0 评论 -
Script to monito redo log switch
set linesize 100set pagesize 100column day format a15 heading Daycolumn d_0 format a2 heading 00column d_1 format a2 heading 01column d_2 format a2 heading 02column d_3 format a2 heading转载 2006-08-29 13:11:00 · 509 阅读 · 0 评论 -
辨别文件名出错
set linesize 145set pagesize 1000set trimout onset trimspool onSet Feedback offset timing offset verify offSet Heading Off Set Termout Offcreate or replace function show_bad_file_转载 2006-08-22 22:24:00 · 579 阅读 · 0 评论 -
Amazing Oracle 10g benchmark with a terabyte of RAM
http://www.tpc.org/results/FDR/TPCC/IBM_595_32_20050412_FDR.pdf转载 2006-11-04 21:47:00 · 606 阅读 · 0 评论 -
estimate table size after reorg (using dbms_space)
When table had huge amount of records deleted, you may need a reorg to lower the high water mark and improve full scan performance. If using ASSM, you may use dbms_space.space_usage to estimate the em转载 2006-11-04 21:51:00 · 704 阅读 · 0 评论 -
Comparison of Oracle, MySQL and PostgreSQL DBMS
http://www-css.fnal.gov/dsg/external/freeware/mysql-vs-pgsql.html转载 2006-11-04 21:48:00 · 512 阅读 · 0 评论 -
调优SGA脚本--转贴
源代码:DECLARE libcac number(10,2); rowcac number(10,2); bufcac number(10,2); redlog number(10,2); spsize number; blkbuf number; logbuf number;BEGINselect value into转载 2006-07-25 19:46:00 · 628 阅读 · 0 评论 -
Improving SQL efficiency using CASE
IntroductionSome time ago I wrote "The Power of Decode", a paper on using the DECODE function to improve report performance. I was aware at the time that DECODE was being replaced by CASE but wanted转载 2006-11-24 12:43:00 · 553 阅读 · 0 评论 -
Rename DB with NID
Rename Database 9i and above Check the Db name SQL> select name from v$database; NAME --------- SUN START : 1.shutdown immediate 2.startup mount 3.$nid target=sys as sysdba dbname=orcl setname=yes OUT转载 2006-11-24 12:45:00 · 691 阅读 · 0 评论 -
Reducing CPU problem caused due to high buffer gets (after large delete)
Reducing CPU problem caused due to high buffer gets. Couple of month’s back I was working on tuning one of the telecom databases which was occupying almost 90% of the CPU in peak times. While interpre转载 2006-11-14 13:16:00 · 675 阅读 · 0 评论 -
using INSTR function for obtaining data from a table, based on a selection from the master table (FK relation)
Suppose that we have a departments table (dep_id, dep_name,...) and another table with persons from every department (person_id, dep_id,...). If many operations are required on the data from the perso转载 2006-11-14 13:14:00 · 538 阅读 · 0 评论 -
Locks tree sessions
This script displays information on all database sessions with the username column displayed as a heirarchy if locks are present. It is assumed that the user running the script has access to the V$ vi转载 2006-11-04 21:58:00 · 517 阅读 · 0 评论