Informix vs Oracle. SOme questions.

Our courrent environment consist of Informix ODS. We have been asked to learn about Oracle. As an Informix user, I know very little about Oracle and have created a list of questions relative to how we work around here. Could some of you Oracle gurus please send me some feedback on these question ? I gave them to the Oracle reps a few weeks ago and have not heard from them yet.

Thanks,

Jorge :-)

A rough draft of things our shop floor control system uses and must have. Not only because they currently exists. But, because they are Beneficial to our operation.

• On occasions we need to recreate a table on the fly Quickly. These are situations where seconds and minutes are critical and can mean large dollar losses. Does oracle offer a tool to create a SQL statement of the table or entire entire database schema from the command line as easily as just typing "dbschema -d databasename" ?
• Can database and tables be renamed using simple SQL ? "rename database abc to efg;"
• Sometimes we need to unload filtered data from a table to a flat file. we do this in simple SQL by simply executing commands such as "unload to filename select * from customer" Can I do this as easily in Oracle as I can now ?
• Also, we can unload databases or tables to ASCII or binary using tools such as dbexport, unload, onunload and a high performance loader which. We have been able to unload 8 million rows, drop the table, recreate it, apply 5 indices and reload the data in less than 30 minutes. Can Oracle do the same
• We currently have several options for achieving data replication. from one server to another or from one to many. We currently use high availability replication which sends logical logs from one server to the other thus maintaining a real-time redundant server without having to setup any special database actions such as triggers or procedure to replicate for us. ( low maintenance ). Does oracle offer similar replication.
• Is oracle 8 currently compatible with sql-92 ?
• Does Oracle offer the ability to fragment a table by expression or round robin over multiple database spaces ? We have bench marked parallel processing in our labs with our current product which has been using the feature for several years now and have noticed performance gains of queries which took 3 minutes plus to 1 (one) second.
• I understand that when a database instance in oracle is set up, a fixed amount of memory is preallocated to the instance. What would happen when the system becomes saturated and more memory is needed ? Does it allocate memory dynamically ? Do I have to Bring down production, to allocate more memory and then restart ? Can I free up memory as easily as adding more ?
• On the subject of bringing down the server, is it true that when a change to a parameter of the database is needed one must bring down the server and restart it ?
• We put out software release on a regular basis. These release involve changes to tables. Currently if I alter a table and add a new column I can insert it before or after a specifics existing column. Can I do the same with Oracle using basic SQL. "alter table cust add ( state char (2) before zip );"
• We use RAW disk partitioning for our database. This increases the performance of the IO. Does Oracle offer and SUPPORT RAW disk partioninig with oracle 7 or 8 ?
• Does oracle offer automatic seamless char to numeric conversion and vice versa without the use of additional functions ?
• On our shop floor system everything is real time. Being so, we must capture dates and times down to a fraction of a second. these are regular data types which we use now. does oracle offer this data type ? or the interval type for differences between two time stamps ?
• Cursors allow a user to compile a Data Management Statement thus improving performance. Does Oracle support Dynamic cursors, meaning a user may fetch the last, previous, or an absolute element within a cursor ? *