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
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
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;
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# ;
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
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
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]
This query helps to find the objects, that depend from the specified one
select CONSTRAINT_NAME, OWNER,TABLE_NAME from DBA_CONSTRAINTS
where R_CONSTRAINT_NAME in
( select CONSTRAINT_NAME
from dba_constraints
where
OWNER=upper('&user_dependent_from') and
TABLE_NAME=upper('&object_dependent_from') )
and STATUS='ENABLED';
Example:
create table city ( city_id int ,
name char(10) ,
primary key ( city_id ) ) ;
create table country (
cou_id int,
name char(19),
city_id int
constraint ZZZ references city ( city_id ) on delete cascade,
primary key(cou_id) ) ;
insert into CITY values ( 1, 'Paris' ) ;
insert into CITY values ( 2, 'Berlin') ;
insert into COUNTRY values ( 101, 'France', 1 ) ;
insert into COUNTRY values ( 102, 'Germany', 2 );
Output:
Enter value for user_dependent_from: FRODO
Enter value for object_dependent_from: CITYCONSTRAINT_NAME OWNER TABLE_NAME
ZZZ FRODO COUNTRY
[amazon-product]1590599683[/amazon-product]
There is also standard script from Oracle, which displays the dependencies: ${ORACLE_HOME}/rdbms/admin/utldtree.sql
execute deptree_fill('table', 'scott', 'emp');
select * from deptree order by seq#;
Small query to list Oracle tablespaces, that are in backup mode:
select d.tablespace_name, b.time
from dba_data_files d, v$backup b
where
d.file_id = b.FILE#
and b.STATUS = 'ACTIVE' ;
The following undocumented function could be used to write to the alert log:
execute sys.dbms_system.ksdwrt(code, message);
Parameters:
1 – Write to trace file.
2 – Write to alertlog.
3 – Write to both.
Example:
execute sys.dbms_system.ksdwrt(2,to_char(sysdate, 'Dy Mon DD HH24:MI:SS YYYY')||'Hello!' );
create or replace trigger trg_delme
BEFORE UPDATE on frodo.delme
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
begin
sys.dbms_system.ksdwrt(2,to_char(sysdate, 'Dy Mon DD HH24:MI:SS YYYY') );
end;