2007年01月_jiawancai

转载 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 694

转载 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 587

转载 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 603

转载 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 499

转载 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 564

转载 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 653

转载 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 666

转载 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 621

转载 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 827

转载 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 459

转载 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 621

转载 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 428

oursoft的专栏