View previous topic :: View next topic
|
Author |
Message |
superk
Global Moderator
Joined: 26 Apr 2004 Posts: 4652 Location: Raleigh, NC, USA
|
|
|
|
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 |
|
|
shrivatsa Warnings : 1 Active User
Joined: 17 Mar 2006 Posts: 174 Location: Bangalore
|
|
|
|
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 |
|
|
shrivatsa Warnings : 1 Active User
Joined: 17 Mar 2006 Posts: 174 Location: Bangalore
|
|
|
|
SuperK,
Do you need to compare Schemas...? |
|
Back to top |
|
|
superk
Global Moderator
Joined: 26 Apr 2004 Posts: 4652 Location: Raleigh, NC, USA
|
|
|
|
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 |
|
|
|