col opname for a40
col units for a10
SELECT sid
,opname
,sofar
,totalwork
,units
,elapsed_seconds
,time_remaining
FROM v$session_longops
WHERE sofar != totalwork;
Oracle: long running queries
Reply
Category Archives: Oracle
Oracle: PERFSTAT.STATS$MUTEX_SLEEP_PK violated
There is a known problem with Oracle statspack report in 10g, which could cost You at least one lost statspack snapshot.
The following message is written into the alert log:
ORA-00001: unique constraint (PERFSTAT.STATS$MUTEX_SLEEP_PK) violated
ORA-06512: at “PERFSTAT.STATSPACK”, line 5264
ORA-06512: at “PERFSTAT.STATSPACK”, line 104
ORA-06512: at line 1
There is a Note 382993.1 in Metalink, which describes this bug.
The workaround is simple:
alter table perfstat.stats$mutex_sleep disable constraint STATS$MUTEX_SLEEP_PK;
create index perfstat.STATS$MUTEX_SLEEP_PK on
STATS$MUTEX_SLEEP(SNAP_ID,DBID,INSTANCE_NUMBER,MUTEX_TYPE,LOCATION);
… and check the Metalink for the news, if the bug was fixed.
[amazon-product]007222360X[/amazon-product]
Oracle: read the entries from alert log
Sometimes it’s necessary to read the information from the alert log together with the timestamp of the error.
The very simple Unix-shell script helps to do this:
echo
echo 'Enter # of lines:'
read NUM
tail -$NUM alert_${ORACLE_SID}.log | awk '
BEGIN {prev="" ; ret=1 }
/^(...-|Error)/ { if ( prev !~ /^(...-|Error)/ ) { print "" ; print prev;} print $0;ret=0}
{prev=$0}
END { exit ret } '
This code could be assigned to some hotkey in Your favorite telnet application, so You just need to press something like “Alt+A” to see the quick review of the recent Oracle errors.
Example:
Enter # of lines:
1000
Sep 01 19:42:03 2009
Errors in fileARC0: Archiving not possible: error count exceeded
Sep 01 19:42:06 2009
Errors in file testsrv_arc0_23486.trc:
ORA-16038: log 1 sequence# 112 cannot be archived
ORA-19502: write error on file “”, blockno (blocksize=)
ORA-00312: online log 1 thread 1: ‘redo_g1_m1.log’
Oracle: export/import using compressed file
Export to the compressed file
/etc/mknod my_own_pipe p
gzip -c -9 < my_own_pipe > mydump.gz &
exp username/password ...... file=my_own_pipe
rm -f my_own_pipe
Import from the compressed file:
/etc/mknod my_own_pipe p
gunzip -c mydump.gz > my_own_pipe &
imp username/password ....... file=my_own_pipe
rm -f my_own_pipe
Oracle: export as sysdba
The following trick could be used, if You want to start Oracle export as sysdba: use double quotes around the single quotes.
Warning! Dump consistency could not be guaranteed, if the export is done under sys user!
exp userid=" '/ as sysdba' " parfile=exp.par
Possible parameter file:
file=mydump.dmp
compress=n
log=mydump.log
direct=y
full=n
owner=SCOTT
consistent=y
feedback=10000
Oracle: convert LONG to VARCHAR2
set serveroutput on
declare
s_sql varchar2(2000);
s_rez varchar2(32767);
begin
s_sql := 'select LONG_COLUMN from SOME_TABLE where ID_COLUMN = 123' ;
execute immediate s_sql into s_rez;
-- print it on the screen or convert/transform/search...
dbms_output.put_line ( s_rez ) ;
end;
Oracle: recreate database links
SELECT
'create '
||DECODE(U.NAME,'PUBLIC','public ')
||'database link '||CHR(10)
||DECODE(U.NAME,'PUBLIC',Null, U.NAME||'.')
|| L.NAME||chr(10)
||'connect to ' || L.USERID || ' identified by '''
||L.PASSWORD||''' using ''' || L.host || ''''
||chr(10)||';' TEXT
FROM
sys.link$ L,
sys.user$ U
WHERE L.OWNER# = U.USER# ;
Oracle: grant permissions to the whole schema
Technically saying, this is granting permissions to all tables in the schema
set verify off
set feedback off
set pagesize 10000
set linesize 120ACCEPT main_owner CHAR PROMPT 'MAIN OWNER: '
ACCEPT grant_to CHAR PROMPT 'GRANT TO: '
ACCEPT permissions CHAR PROMPT 'PERMISSIONS (i.e. SELECT,UPDATE): '
spool grant_from_&&main_owner._to_&&grant_to..sql
select 'grant &&permissions on "&&main_owner"."'
||table_name
||'" to "&&grant_to";' "---"
from dba_tables
where owner=upper('&&main_owner');
spool off
undefine permissions
undefine main_owner
undefine grant_to
Oracle: text of the running SQL queries
SELECT s.sid, s.serial#, st.sql_text sql_text
FROM v$session s, v$sql st, v$process p
WHERE s.sql_hash_value = st.hash_value
AND s.sql_address = st.address
AND s.paddr = p.addr
and ( s.sid=&which_sid. )
Full text
select x.sql_text
from v$session s,v$sqltext x
where s.sql_address=x.address and s.sid = &which_sid
order by sid, piece asc
Oracle: transpose data table (rows into columns)
create table MAIN_TBL (
magazine varchar2(10),
region varchar2(5),
quantity int );
insert into MAIN_TBL values ( 'Playboy', 'Nord', 1 );
insert into MAIN_TBL values ( 'Playboy', 'East', 2 );
insert into MAIN_TBL values ( 'AutoWeek', 'Nord', 3 );
insert into MAIN_TBL values ( 'AutoWeek', 'West', 4 );
insert into MAIN_TBL values ( 'Wired', 'Nord', 5 );
insert into MAIN_TBL values ( 'Wired', 'Nord', 6 );
insert into MAIN_TBL values ( 'Wired', 'West', 7 );
insert into MAIN_TBL values ( 'Wired', 'East', 8 );
insert into MAIN_TBL values ( 'Wired', 'South', 9 );
declare
report_exists number;
report_owner varchar(30) := 'FRODO' ;
--
report_name varchar(30) := 'REPORT_TBL' ;
query_main varchar(16000) :=
'create table ' || report_owner || '.'
|| report_name || ' as select MAGAZINE ' ;
--
query_part varchar(1024) ;
my_var varchar2(5);
cursor cur_region is
select distinct REGION
from MAIN_TBL
order by region;
begin
select count(*) into report_exists
from dba_tables
where table_name = report_name and owner = report_owner;
if ( report_exists = 1 ) then
execute immediate 'drop table ' || report_owner || '.' || report_name ;
end if;
open cur_region ;
loop
fetch cur_region into my_var ;
exit when cur_region%NOTFOUND;
query_part := 'select nvl(sum(quantity),0) from MAIN_TBL x where x.magazine = main.magazine and x.region='''||my_var||'''' ;
query_main := query_main || chr(10) || ',(' || query_part || ')"' || my_var || '"';
end loop;
close cur_region ;
query_main := query_main || ' from (select distinct MAGAZINE from MAIN_TBL ) main' ;
execute immediate query_main ;
end;
/
select * from FRODO.REPORT_TBL
/
Output:
MAGAZINE East Nord South West
———- —– —– —— ——-
AutoWeek 0 3 0 4
Playboy 2 1 0 0
Wired 8 11 9 7
There is also discussion of the similar subject on AskTom site.
[amazon-product]0137142838[/amazon-product]