Oracle Database Discussions

Hello, im an cs student and have internship at a company named DiMS that sells a system and always uses Oracle 9i or 10i.

The schema of the database is written in a company specific syntax. There is a tool that can interprete it and create the correct tables to support that schema.

My task is to write a tool that checks wether the[i] database schema is correct with respect to the reference schema (the ref schema is the schema as defined in the company specific syntax).

My tool analyses the database and creates Delta object that are handled by a DataBase updater which creates the correct SQL statemetns and executes them on the database to update the database to a new schema.

Here is some example output:


ALTER TABLE mddcmdtest.contractcomponent DROP (testcol, testcol2);
#GenericSolution contractheaderline {
DROP TABLE mddcmdtest.contractheaderline;
CREATE TABLE mddcmdtest.contractheaderline ...;
#}
#GenericSolution contractprice {
DROP TABLE mddcmdtest.contractprice;
CREATE TABLE mddcmdtest.contractprice ...;
#}
ALTER TABLE mddcmdtest.dagplan2 ADD CONSTRAINT ixdagplan2 PRIMARY KEY (nummer, regel) STORAGE ( MAXEXTENTS UNLIMITED );
ALTER TABLE mddcmdtest.entityselection ADD CONSTRAINT xentityselection PRIMARY KEY (userid, id, reftype) STORAGE ( MAXEXTENTS UNLIMITED );
ALTER TABLE mddcmdtest.menuuser ADD CONSTRAINT ixmenuuser PRIMARY KEY (nummer, logname) STORAGE ( MAXEXTENTS UNLIMITED );
ALTER TABLE mddcmdtest.fms4apr ADD CONSTRAINT ixfms4apr PRIMARY KEY (bedrijf, id, periode) STORAGE ( MAXEXTENTS UNLIMITED );
ALTER TABLE mddcmdtest.fms7d52 ADD CONSTRAINT xfms7d52 PRIMARY KEY (bedrijf, boekstukno) STORAGE ( MAXEXTENTS UNLIMITED );
ALTER TABLE mddcmdtest.fms7r01 ADD CONSTRAINT xfms7r01 PRIMARY KEY (bedrijf, boekstukno, regel) STORAGE ( MAXEXTENTS UNLIMITED );
CREATE INDEX mddcmdtest.yfms7r05 ON mddcmdtest.fms7r05(grboekregel) STORAGE ( MAXEXTENTS UNLIMITED );
CREATE INDEX mddcmdtest.zfms7r05 ON mddcmdtest.fms7r05(tabelid, boeksoort) STORAGE ( MAXEXTENTS UNLIMITED );
CREATE TABLE mddcmdtest.cmsvariabledata ...;
CREATE TABLE mddcmdtest.cmswcunit ...;
DROP TABLE mddcmdtest.aaaa;
DROP TABLE mddcmdtest.abc;



As you can see it updates the database.
The update process should only start if it is sure it can perform the updat without problems ( thats a requirement of the updater ).
But what if a table has 30GB > or more data in it,
and that table must have a new column on position 2. For example:

oldcols( A NUMBER, B NUMBER) ==> newcols(A NUMBER, C NUMBER, B NUMBER).

The only way to ad such a column is the following approach (which i call the generic approach) (see below). But the generic approach will probably fail for such big tables.
How can is solve this? How should i update the schema of such large tables?
Or does my aproach work?
Are there any tips for me?

Here an explanation copied from my report:
---
7.2.2.1 Generic solution
When a table x does not conform to the reference schema we can simple execute the following actions sequentially. Note that the SQL conforms to the oracle syntax as explained in the Oracle SQL Reference [ORASQL]. The syntax may be different for different database systems but the purpose is identical.

Create a temporary copy of the current table
CREATE TEMPORARY TABLE x_temp AS SELECT * FROM x

Drop the current table
DROP TABLE x ...

Create the new table using an existing method, now we can be sure that the table conforms to the reference schema.
CREATE TABLE x ...

Copy the old data into the new table (data conversions may be needed, see 1.2.2.8).
INSERT INTO x SELECT .... FROM x_temp

Drop the temporary table.
DROP TABLE x_temp

---