How to limit list of tables when doing a metadata import

clarcombe · Post by **clarcombe** » Fri Jul 20, 2007 3:50 am

I am trying to import a small list of tables (20) from an Oracle Database. However the Oracle schema I am trying to import from has 70,000 tables (that's Peoplesoft for you !!)

QuestionIs there any way of limiting the list ? I have tried using schema.table% but this doesn't work.

Does anyone have suggestions of how to do this more efficiently ? I have thought about exporting the structures from Oracle in DDL format and trying to import them that way (but I don't know how)

TIA

clarcombe · Post by **clarcombe** » Fri Jul 20, 2007 4:16 am

Note that I cannot use ODBC currently as I don't have ODBC installed on the Unix server.

sachin1 · Post by **sachin1** » Fri Jul 20, 2007 5:16 am

Hello, i interpret your issue correctly, i think you need few table definitions(Meta data) to be used in jobs.

you go on to import plug-in meta data, then give Instance name, username,password.

you will get a list of all tables, but if you want to select particular list of tables starting with name for example HR_* suppose. then put HR in Name Contains box and click refersh, you will get list of table starting with HR.

chulett · Post by **chulett** » Fri Jul 20, 2007 6:38 am

Unfortunately, the issue will be the 'you will get a list of all the tables' part that has to happen before you can filter it down, I'd wager. Colin, are you having a problem fetching back that larger list? I don't think that widget can hold 70K entries and you can't filter out what isn't there in the first place. I seem to remember the same issue back when I had to deal with PS but don't recall what I did about it.

If you truly have issues with the table list then your last thought is what I would suggest. Get your hands on the bare-bones DDL for the tables you care about and then use them to create those tables empty elsewhere. Doesn't really matter where, a local schema for instance and then import the metadata from there.

clarcombe · Post by **clarcombe** » Fri Jul 20, 2007 6:45 am

Craig,

Luckily for me,I have discovered that a DS developer here has created a tool to filter and extract metadata from Oracle so I don't have to look any further.

I left the question open as I am sure I won't be so lucky at the next site I work on !!

Thanks

chulett · Post by **chulett** » Fri Jul 20, 2007 6:48 am

Good to know and perhaps one of these days there will be a place where tools like that can be uploaded and shared here.

In the meantime, for completeness, can you do me a favor and expand a little bit on what exactly your issue was? You just mention the fact that you had one, but not what actually was going on.

Thanks.

clarcombe · Post by **clarcombe** » Fri Jul 20, 2007 7:24 am

Craig,

If I try to import from the Oracle Plugin where there is a huge list of tables to import, the import understandably hangs when it trys to display the tables.

I have no idea if the plug-in is capable of loading 70,000 tables into memory to display (I never waited long enough to find out !)

It would make sense if IBM/Ascential had put a filter on this screen to limit the number of tables it returns as they have done with the ODBC table import.

The point you make about loading the table into an empty schema is a valid one and I think this would resolve the problem (had I not found another one)

Is this enough info ?

chulett · Post by **chulett** » Fri Jul 20, 2007 7:42 am

Yes, thank you... unless you are willing to be patient and see if indeed the full table list can be imported if one just waits... and waits...

DSXchange

How to limit list of tables when doing a metadata import

How to limit list of tables when doing a metadata import

Re: How to limit list of tables when doing a metadata import