set serveroutput on
BEGIN
for i in (select file_name,autoextensible from dba_data_files where autoextensible = ‘YES’) loop
dbms_output.put_line(‘File name is: ‘||i.file_name||’ and autoextensible is: ‘||i.autoextensible);
execute immediate ‘alter database datafile ”’||i.file_name||”’ autoextend off’;
end loop;
END;
/
I use this simple script to find bind variables with or without sql_id.
select
sql_id,
t.sql_text SQL_TEXT,
b.name BIND_NAME,
b.value_string BIND_STRING
from
v$sql t
join v$sql_bind_capture b
using (sql_id)
where
b.value_string is not null
– and sql_id=’974ju5zc5whfn’;
set serveroutput on
declare
row_cnt number;
BEGIN
for i in (select table_name from user_tables) loop
execute immediate ‘select count(*) from ‘|| i.table_name into row_cnt;
dbms_output.put_line(‘Table sum for ‘||i.table_name||’ is ‘ ||row_cnt);
end loop;
END;
/
Depending on tool one can grant access to users if they need access to look at code for store procedure, function and package without giving the user execute access.
For AquaStudio for example one can set up user with following access which would allow to look at the code, the access needs to be granted by sys. In this setup you are replace the view all_objects with dba_objects and all_source with dba_source.
grant select on sys.dba_source to ;
create synonym .all_source for sys.dba_source;
grant select on sys.dba_objects to ;
create synonym .all_objects for sys.dba_objects;
Simple script to generate DDL for all database links in the database:
set pagesize 0
set long 90000
SELECT DBMS_METADATA.GET_DDL(‘DB_LINK’,a.db_link,a.owner) || ‘/’ FROM dba_db_links a
/
Here is a simple script to generate the DDL code for all indexes in a particular table.
set heading off;
set echo off;
Set pages 999;
set long 90000;
SELECT DBMS_METADATA.GET_DEPENDENT_DDL(‘INDEX’,UPPER(‘&table_name’),UPPER(‘&schema_name’))
from dual
/
set heading on;
set echo on;
select to_char(sysdate,’DD/MM/YYYY HH24:MI:SS’) from dual;
Would return something like: 20/04/2010 19:42:35
Format mask Description CC Century
SCC – Century BC prefixed with –
YYYY – Year with 4 numbers
SYYY – Year BC prefixed with –
IYYY – ISO Year with 4 numbers
YY – Year with 2 numbers
RR – Year with 2 numbers with Y2k compatibility
YEAR – Year in characters
SYEAR – Year in characters, BC prefixed with –
BC – BC/AD Indicator *
Q – Quarter in numbers (1,2,3,4)
MM – Month of year 01, 02…12
MONTH – Month in characters (i.e. January)
MON – JAN, FEB
WW – Weeknumber (i.e. 1)
W – Weeknumber of the month (i.e. 5)
IW – Weeknumber of the year in ISO standard.
DDD – Day of year in numbers (i.e. 365)
DD – Day of the month in numbers (i.e. 28)
D – Day of week in numbers(i.e. 7)
DAY – Day of the week in characters (i.e. Monday)
FMDAY – Day of the week in characters (i.e. Monday)
DY – Day of the week in short character description (i.e. SUN)
J – Julian Day (number of days since January 1 4713 BC, where January 1 4713 BC is 1 in Oracle)
HH – Hournumber of the day (1-12)
HH12 – Hournumber of the day (1-12)
HH24 – Hournumber of the day with 24Hours notation (0-23)
AM – AM or PM
PM – AM or PM
MI – Number of minutes (i.e. 59)
SS – Number of seconds (i.e. 59)
SSSSS – Number of seconds this day.
DS – Short date format. Depends on NLS-settings. Use only with timestamp.
DL – Long date format. Depends on NLS-settings. Use only with timestamp.
E – Abbreviated era name. Valid only for calendars: Japanese Imperial, ROC Official and Thai Buddha.. (Input-only)
EE – The full era name
FF – The fractional seconds. Use with timestamp.
FF1..FF9 – The fractional seconds. Use with timestamp. The digit controls the number of decimal digits used for fractional seconds.
FM – Fill Mode: suppresses blianks in output from conversion
FX – Format Exact: requires exact pattern matching between data and format model.
IYY or IY or I – the last 3,2,1 digits of the ISO standard year. Output only
RM – The Roman numeral representation of the month (I .. XII)
RR – The last 2 digits of the year.
RRRR – The last 2 digits of the year when used for output. Accepts fout-digit years when used for input.
SCC – Century. BC dates are prefixed with a minus.
CC – Century
SP – Spelled format. Can appear of the end of a number element. The result is always in english. For example month 10 in format MMSP returns “ten”
SPTH – Spelled and ordinal format; 1 results in first.
TH – Converts a number to it’s ordinal format. For example 1 becoms 1st.
TS – Short time format. Depends on NLS-settings. Use only with timestamp.
TZD – Abbreviated time zone name. ie PST.
TZH – Time zone hour displacement.
TZM – Time zone minute displacement.
TZR – Time zone region
X – Local radix character. In america this is a period (.)
SQL>SQLPLUS ‘/AS SYSDBA’
CREATE OR REPLACE TRIGGER block_tools_from_prod
AFTER LOGON ON DATABASE
DECLARE
v_program sys.v_$session.program%TYPE;
BEGIN
SELECT program INTO v_program
FROM sys.v_$session
WHERE audsid = USERENV(‘SESSIONID’)
AND audsid != 0 — Don’t Check SYS Connections
AND ROWNUM = 1; — Parallel processes will have the same AUDSID’s
IF UPPER(v_program ) LIKE ‘%TOAD%’ OR UPPER(v_prog) LIKE ‘%T.O.A.D%’ OR — Toad
UPPER(v_program ) LIKE ‘%SQLNAV%’ OR — SQL Navigator
UPPER(v_program ) LIKE ‘%PLSQLDEV%’ OR — PLSQL Developer
UPPER(v_program ) LIKE ‘%BUSOBJ%’ OR — Business Objects
UPPER(v_prog) LIKE ‘%EXCEL%’ — MS-Excel plug-in
THEN
RAISE_APPLICATION_ERROR(-20000, ‘Development tools are not allowed here.’);
END IF;
END;
/
SHOW ERRORS
If you need to capture SQL statements to replay in another instance you can turn trace on and capture in the trace files located in the udump folder.
– turn timing on.
alter session set timed_statistics=true;
– set to unlimited otherwise you may get an incomplete trace file when dump file size limit is reached.
alter session set max_dump_file_size=unlimited;
alter session set events ’10046 trace name context forever, level 12′;
– ******* run all of your processing here *******
alter session set events ’10046 trace name context off’;