If you're not accepting new questions, how could this question have been asked 8 hours ago? I'm missing something here, where can I go to ask a new question?

Thank you!

It is a rare treat to get to ask Tom a question. You have to have some "lucky" timing. Typically the window for asking questions is on the order of minutes, not hours. If you query around you might find out when "good" times are to check if Tom is accepting new questions.

Hi, anonymous

Just a suggestion that you possibly could avoid the transient tables completely.

You do:

1) New flat files arrive
2) Transient tables truncated
3) SQL Loader: flat files -> transient tables
4) Data cleansed: transient tables -> "real" tables

We have had much success with a different approach using external tables and materialized views. (I don't know your case sufficiently to know if it is possibly to use this approach in your case, but it might serve as inspiration :-)

We have created external tables on the flat files - that way we can select from the flat files.
Our data cleansing is possible to define in a single (albeit slightly complex) sql statement.
We create a materialized view refresh complete on demand defined by that cleansing sql statement.
The materialized view then becomes our production application table, on which we create suitable indexes etc.

Our scheduled work then becomes this:


1) New flat files arrive
2) Refresh materialized view

All done in two steps :-) Data is only moved once and it is done transactionally.

It is an approach that is good if the new flat files is a complete refresh of the data. If the flat files arriving are to be incrementally added to the data in your application tables, then it is not quite as neat :-)

Perhaps you can use some of the ideas.

At one time, we did explore the possibility of using external tables but there are some limitations which exclude their use. Today it is possible to workaround those limitations but no one wants to change a process which has worked for years.

I just got lucky. In fact, the slot for new questions was open for a while yesterday. I read this site regularly to refresh my Oracle knowledge and learn new features. Yesterday, when I opened the home page, I saw that Tom was taking new questions so I posted this question. Trust me, there is no short cut or backdoor to get questions in.

In the oracle documentation at http://download.oracle.com/docs/cd/B28359_01/server.111/b28294/scenarios.htm#i1015738 it says

"Even though the user specifies the clause, a redo record is still written to the online redo log file. However, there is no data associated with this record. This can result in log application or data access errors at the standby site and manual recovery might be required to resume applying log files."

In what scenarios would it result in an error? Lets say I have a transient table in a nologging tablespace. After I finish loading the data to the transient table, but before I've inserted into the permanent table, I need to switch to the physical standby database. I realize that the data won't be in the transient table, but will it error when I query it? How can I keep these transient tables in nologging and avoid errors if I fail over to the physical standby database? I know I'll need to reload that data, but is there anything that needs to be done before hand?