Tracnac

Note: You may also use DBMS_FILE_TRANSFER.COPY_FILE procedure to copy files. For doing that, you'd need to create 2 directory objects and copy the file. For example, to copy the above controlfile -

create directory D1 as '/home/pwprd/ravi';
create directory D2 as'+PWPRD_DATA/PWPRDDR/CONTROLFILE';
exec dbms_file_transfer.copy_file('D1','stby_PWPRD.ctl','D2', 'control01.ctl');

Tracnac

#SQL #Oracle

Oracle Pin lock

select decode(lob.kglobtyp, 0, 'NEXT OBJECT', 1, 'INDEX', 2, 'TABLE', 3, 'CLUSTER',
4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE',
7, 'PROCEDURE', 8, 'FUNCTION', 9, 'PACKAGE',
11, 'PACKAGE BODY', 12, 'TRIGGER',
13, 'TYPE', 14, 'TYPE BODY',

Tracnac

10046 and 10053 Events

If you've got an interest in Oracle performance tuning, you'll probably have seen references to something called event 10046. Event 10046 is an undocumented, but well known feature within the Oracle database that generates detailed information on how a statement is parsed, what the values of bind variables are, and what wait events occurred during a particular session. Activating Event 10046 gives us a more detailed trace.

Tracnac

with drh as (
select max(regexp_replace(comments, '[^[:digit:].]'))
keep (dense_rank last order by action_time) as vers
from dba_registry_history
)
, v$v as (
select regexp_substr(banner, '(\d+\.?){5}', 1) as vers
from v$version
where lower(banner) like 'oracle%'

Tracnac

COLUMN parameter_value FORMAT A30


SELECT parameter_name, parameter_value
FROM dba_advisor_parameters
WHERE task_name = 'SYS_AUTO_SQL_TUNING_TASK'
AND parameter_name IN ('ACCEPT_SQL_PROFILES',
'MAX_SQL_PROFILES_PER_EXEC',
'MAX_AUTO_SQL_PROFILES');

Tracnac

GUIDE TO RUNNING EXTERNAL JOBS ON 10g WITH DBMS_SCHEDULER

NOTE: Users using 11g should use the new method of specifying a credential which eliminates many of the issues mentioned in this note.

This guide covers several common questions and problems encountered when using dbms_scheduler to run external jobs, either on Windows or on UNIX.

Tracnac

Tous les paramètres cachés d’Oracle.

Vérifié à partir de la 10g fonctionne peut être sur 9i ?! Remplacer le %optim% par le paramètre recherché.

SELECT
x.ksppinm name,
y.ksppstvl VALUE,
decode(ksppity,

Tracnac

CREATE OR REPLACE TRIGGER open_pdbs
AFTER STARTUP ON DATABASE
BEGIN
EXECUTE IMMEDIATE 'ALTER PLUGGABLE DATABASE ALL OPEN';
END open_pdbs;
/

Tracnac

How does one SELECT a value from a table into a Unix variable?

One can select a value from a database column directly into a Unix environment variable. Look at the following shell script examples:

#!/bin/sh
VALUE=`sqlplus -silent user/password@instance << END
set pagesize 0 feedback off verify off heading off echo off
select max(c1) from t1;

Tracnac

select
lpad(' ', 2*level) || granted_role "User, his roles and privileges"
from
(
/* THE USERS */
select
null grantee,
username granted_role
from