IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Linking Oracle tables to each other


IBM Mainframe Forums -> All Other Mainframe Topics
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
superk

Global Moderator


Joined: 26 Apr 2004
Posts: 4652
Location: Raleigh, NC, USA

PostPosted: Thu Mar 27, 2008 10:10 pm
Reply with quote

We have a software product that uses an Oracle database for its data storage. I need to produce some daily management reports from the content. There is one schema with about 250 tables. The vendor does not and will not publish the contents of the schema.

I'm using Oracle SQL*Plus on z/OS V1R7 for my task. I don't have access to any desktop-based tools for this.

I've been able to find out how to list the contents of the schema, how to find the contents of each table, and how to retrieve the data from the tables. One thing I'd like to do is to document the relationships of the indexes between all of the tables, so we know which tables to look at for JOIN operations.

Is there a SQL*Plus command that will show all of these relationships?
Back to top
View user's profile Send private message
shrivatsa
Warnings : 1

Active User


Joined: 17 Mar 2006
Posts: 174
Location: Bangalore

PostPosted: Sat Mar 29, 2008 9:46 pm
Reply with quote

Modify below query and let me know whether that is your requirement
Code:

SELECT o.name AS table_name,p.index_id, i.name AS index_name , au.type_desc AS allocation_type, au.data_pages, partition_number
FROM sys.allocation_units AS au
    JOIN sys.partitions AS p ON au.container_id = p.partition_id
    JOIN sys.objects AS o ON p.object_id = o.object_id
    JOIN sys.indexes AS i ON p.index_id = i.index_id AND i.object_id = p.object_id
WHERE o.name = N'DatabaseLog' OR o.name = N'Currency'
ORDER BY o.name, p.index_id;
Back to top
View user's profile Send private message
shrivatsa
Warnings : 1

Active User


Joined: 17 Mar 2006
Posts: 174
Location: Bangalore

PostPosted: Mon Mar 31, 2008 12:05 pm
Reply with quote

SuperK,

Do you need to compare Schemas...?
Back to top
View user's profile Send private message
superk

Global Moderator


Joined: 26 Apr 2004
Posts: 4652
Location: Raleigh, NC, USA

PostPosted: Tue Apr 01, 2008 7:21 pm
Reply with quote

I get an error:

Code:

SQL> SELECT O.NAME AS TABLE_NAME,P.INDEX_ID, I.NAME AS INDEX_NAME ,       
  2    AU.TYPE_DESC AS ALLOCATION_TYPE, AU.DATA_PAGES, PARTITION_NUMBER   
  3    FROM SYS.ALLOCATION_UNITS AS AU                                     
  4    JOIN SYS.PARTITIONS AS P ON AU.CONTAINER_ID = P.PARTITION_ID       
  5    JOIN SYS.OBJECTS AS O ON P.OBJECT_ID = O.OBJECT_ID                 
  6    JOIN SYS.INDEXES AS I ON P.INDEX_ID = I.INDEX_ID AND I.OBJECT_ID = 
  7      P.OBJECT_ID                                                       
  8    WHERE O.NAME = N'DATABASELOG' OR O.NAME = N'CURRENCY'               
  9    ORDER BY O.NAME, P.INDEX_ID;       
                               
  FROM SYS.ALLOCATION_UNITS AS AU                                         
                            *                                             
ERROR at line 3:                                                           
ORA-00933: SQL command not properly ended                                 
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> All Other Mainframe Topics

 


Similar Topics
Topic Forum Replies
No new posts Need to fetch data from so many DB2 t... DB2 9
No new posts How to: PK does not exist in several ... DB2 6
No new posts Discrepancy b/w SYSIBM tables and BMC... DB2 0
No new posts SYSIBM Tables Query DB2 8
No new posts linking an ALC program to a Relationa... PL/I & Assembler 1
Search our Forums:

Back to Top