No question at this time
DBA Top 10
1 M. Cadot 13000
2 B. Vroman 11900
3 A. Kavsek 11200
4 J. Schnackenberg 5600
4 M. Hidayathullah ... 5600
6 T. Boles 5400
7 P. Wisse 3200
8 T. P 700
9 A. Rai 600
10 R. Wauben 500
10 H. Steijntjes 500
About
DBA-Village
Download PLATO
The free tool for auditing and tuning your database
Version 55 now available
Sep 02, 2016
The DBA-Village forum
Forum as RSS
as RSS feed
Site Statistics
Ever registered users48147
Total active users1697
Act. users last 24h6
Act. users last hour1
Registered user hits last week261
Registered user hits last month1385
Go up

query
Next thread: flash back tables
Prev thread: exporting oracle tables on command line as sql files

Message Score Author Date
hi I need a dynamic query that select the min d...... RUCHI TANEJA Nov 08, 2017, 08:35
Hello, do you mean something like this?<pre>SET...... Bruno Vroman Nov 08, 2017, 10:54
no i want like. table A having column create da...... RUCHI TANEJA Nov 08, 2017, 11:16
Hello Ruchi, some ambiguity... One thing I don'...... Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts Bruno Vroman Nov 08, 2017, 11:38

Follow up by mail Click here


Subject: query
Author: RUCHI TANEJA, India
Date: Nov 08, 2017, 08:35, 105 days ago
Os info: linux
Oracle info: 11gr2
Message: hi

I need a dynamic query that select the min date as column in a table and same table exist for many users.

so i want the min value of date column of a table userwise.
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: query
Author: Bruno Vroman, Belgium
Date: Nov 08, 2017, 10:54, 105 days ago
Message: Hello,

do you mean something like this?
SET RECSEP OFF

COL x NOPRINT
WITH all_of_them AS
( SELECT owner own
FROM all_tables
WHERE table_name = 'THIS_TABLE'
ORDER BY 1 )
SELECT 1 x
, CASE WHEN rownum > 1 THEN 'UNION ALL' || CHR(10) END ||
'SELECT ''' || a.own ||
CASE WHEN rownum = 1 THEN ''' own' ELSE '''' END ||
', MIN( dte ) ' ||
CASE WHEN rownum = 1 THEN 'min_date ' END ||
'FROM ' || a.own || '.this_table'
FROM all_of_them a
UNION ALL
SELECT 2, 'ORDER BY 1;' FROM dual
ORDER BY 1
;
that gives
SELECT 'BRUNO' own, MIN( dte ) min_date FROM BRUNO.this_table
UNION ALL
SELECT 'RUCHI', MIN( dte ) FROM RUCHI.this_table
UNION ALL
SELECT 'SCOTT', MIN( dte ) FROM SCOTT.this_table
ORDER BY 1
;
that gives
OWN MIN_DATE
------ --------------------
BRUNO 04-NOV-2017 21:08:00
RUCHI 08-NOV-2017 09:22:41
SCOTT 07-NOV-2017 04:58:17
HTH,

Bruno Vroman.
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: query
Author: RUCHI TANEJA, India
Date: Nov 08, 2017, 11:16, 105 days ago
Message: no i want like.

table A having column create date.

table b having column create date

table c haaving column create date

now all these tables exist in 10 different schemas.

My required output is i want that for every user what is the value of minimum create date column for every table.

AS owner details i can get from dba_objects also object_name is there in dba_objects and object_type is table.

owner table min(create_date)

Bruno A 10-NOV-17
B 02-MAR-12
C 03-AUG-83

RUCHI A 1-MAR-65
B 30-NOV-17
C 02-APR-16
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: query
Author: Bruno Vroman, Belgium
Date: Nov 08, 2017, 11:38, 105 days ago
Score:   Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts
Message: Hello Ruchi,

some ambiguity... One thing I don't understand.

If the date to identify is in fact data in the tables (like in my previous reply), than what about modifying "all_of_them" to
WITH all_of_them AS

( SELECT owner own, table_name t
FROM all_tables
WHERE table_name IN ( 'A', 'B', 'C' )
ORDER BY 1 )
and add a column "table_name" in the generated statements?

And if you simply want the creation date of the tables themselves, then there is no need for dynamic SQL:
COL owner FOR A20

COL table_name FOR A30
BREAK ON owner SKIP 1
SELECT owner, object_name table_name, created
FROM dba_objects
WHERE object_type = 'TABLE'
AND object_name IN ( 'A', 'B', 'C' )
ORDER BY 1, 2
;
Best regards,

Bruno.
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here