Create a version controlable development DB?

[Betty]

Hi DBAs,

My boss assigned me a task -- to create a development DB for our developers, this DB should have below features:
1. version controlable like Oracle 9i workspace -- the Dev DB can capture DDL, who and when did the DDL; can roll back to any old version if needed while testing; sometimes, may need to keep the data changed by developers. So it is very like Oracle Workspace, but WS only treat "table" as the unit of version, not support DDL.
2. Frequently feed new data to Dev DB from production DB -- I considered to use Logical Standby Database, but LSD is not a very mature tech, it is often dead I have to dump and recreate it; I may use Log Miner to collect the fresh data periodly from production DB.
3. Merge Dev DB and Production DB -- I will use Oracle tool DB comparision, it is a good tool to copy Dev DDL to production DB.

For the feature one -- the version controlable -- I consider for each developer one username, then use logMnr to capture the DDL, (If we omit such a situation first -- the conflict DDL working on the same table structure,) we will get a list of DDL for each user. How to roll back simply? This is not one time action, boss requires can often rollback -- anytime, anywhere.
This make things become very complicated.

Who can tell me if such kind development DB is NOT practical? And which way is better to have a development DB??

Thanks a lot!

Betty

[slimdave]

I vote for "not practical", because there are too many operations that cannot just be rolled back. For example ...

SQL> create table t (col1 char(2));

Table created.

SQL> alter table t modify (col1 char(3));

Table altered.

SQL> alter table t modify (col1 char(2));

Table altered.

SQL> insert into t values ('A');

1 row created.

SQL> alter table t modify (col1 char(3));

Table altered.

SQL> alter table t modify (col1 char(2));
alter table t modify (col1 char(2))
                      *
ERROR at line 1:
ORA-01441: cannot decrease column length because some value is too big

[Betty]

Then, what kind Dev DB is better?