1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41
|
CREATE OR REPLACE VIEW LISTE_TABLES
(TABLE_NAME)
AS
select o.name
from sys.user$ u, sys.obj$ o
where o.owner# = u.user#
and u.name = 'USER_OWNER_GC'
and o.type# = 2;
commit;
SET LINESIZE 1000000;
SET PAGESIZE 50000;
SET head on;
SET feed off;
SET trimspool ON;
set colsep "|" ;
SET LINE 32767;
spool &1\maRequete.sql;
SELECT listetable
FROM ( SELECT table_name, 0 as num, 'SET LINESIZE 1000000;'AS listetable from LISTE_TABLES
UNION ALL
SELECT table_name, 1 as num, 'SET PAGESIZE 50000;' from LISTE_TABLES
UNION ALL
SELECT table_name, 2 as num, 'SET FEEDBACK OFF;' from LISTE_TABLES
UNION ALL
SELECT table_name, 3 as num, 'SET head on;' from LISTE_TABLES
UNION ALL
SELECT table_name, 4 AS num, 'Spool &1\' || table_name || '.txt;' FROM LISTE_TABLES
UNION ALL
SELECT table_name, 5, ('select * from ' || table_name || ';') FROM LISTE
UNION ALL
SELECT table_name, 6 AS num, 'Spool off;' FROM LISTE_TABLES
)
ORDER BY table_name, num;
spool off;
@&1\maRequete;
DROP VIEW LISTE_TABLES;
COMMIT;
exit; |
Partager