You have two options:
- Run multiple Oracle instances on the same machine
- Consolidate all of your Oracle instances into a single instance,
placing the data in separate schemas
Since you're familiar with SQL Server/Sybase, I'll explain the difference between them & Oracle as far as databases and users are concerned.
- A SQL Server database is equivalent to an Oracle Schema. An Oracle schema is owned by a single user
- A SQL Server dataserver is equivalent to an Oracle Instance
Running 4 instances on one machine is trivial, so I won't explain further.
Consolidating to a single database is also easy if the separate databases don't have conflicting schema names. If they do, it may not be an issue as long as the applications/interfaces/packages don't have hard-coded schema names - it's easy to export data from one schema in a database & import it into a different schema on another database.
The Oracle Administrators Guide says the following:
Use the ALTER TABLE...MODIFY statement to modify an existing column
definition. You can modify column data type, default value, column
constraint, column expression (for virtual columns) and column
encryption.
You can increase the length of an existing column, or decrease it, if
all existing data satisfies the new length. You can change a column
from byte semantics to CHAR semantics or vice versa. You must set the
initialization parameter BLANK_TRIMMING=TRUE to decrease the length of
a non-empty CHAR column.
If you are modifying a table to increase the length of a column of
data type CHAR, realize that this can be a time consuming operation
and can require substantial additional storage, especially if the
table contains many rows. This is because the CHAR value in each row
must be blank-padded to satisfy the new column length.
The Oracle SQL Language Reference has much more detail including the following:
You can change the data type of any column if all rows of the column contain nulls. However, if you change the data type of a column in a materialized view container table, then Oracle Database invalidates the corresponding materialized view.
You can always increase the size of a character or raw column or the precision of a numeric column, whether or not all the rows contain nulls. You can reduce the size of a data type of a column as long as the change does not require data to be modified.The database scans existing data and returns an error if data exists that exceeds the new length limit.
You can modify a DATE column to TIMESTAMP or TIMESTAMP WITH LOCAL TIME ZONE. You can modify any TIMESTAMP WITH LOCAL TIME ZONE to a DATE column.
If the table is empty, then you can increase or decrease the leading field or the fractional second value of a datetime or interval column. If the table is not empty, then you can only increase the leading field or fractional second of a datetime or interval column.
For CHAR and VARCHAR2 columns, you can change the length semantics by specifying CHAR (to indicate character semantics for a column that was originally specified in bytes) or BYTE (to indicate byte semantics for a column that was originally specified in characters). To learn the length semantics of existing columns, query the CHAR_USED column of the ALL_, USER_, or DBA_TAB_COLUMNS data dictionary view.
There is additional information and restrictions in the above documentation. Here is a demonstration of attempting to reduce the precision of a Number column and reduce the length of a Varchar2. You can try other changes so you will know what will happen.
--Setup.
DROP TABLE FOO;
CREATE TABLE FOO (BAR Number, BAR2 VARCHAR2(300));
INSERT INTO FOO (SELECT Level, RPAD(to_char(Level),10*Level,to_char(Level))
FROM DUAL CONNECT BY Level <=20);
COMMIT;
SELECT * FROM FOO;
--Reduce Number to Number(10).
ALTER TABLE FOO MODIFY (BAR NUMBER (10));
--Reduce Varchar2(300) to Varchar2(100) (data would be truncated).
ALTER TABLE FOO MODIFY (BAR2 VARCHAR2(100));
--Reduce Varchar2(300) to Varchar2(200) (no data would be truncated).
ALTER TABLE FOO MODIFY (BAR2 VARCHAR2(200));
The alter statements have the following output:
ALTER TABLE FOO MODIFY (BAR NUMBER (10))
Error report:
SQL Error: ORA-01440: column to be modified must be empty to decrease precision or scale
01440. 00000 - "column to be modified must be empty to decrease precision or scale"
ALTER TABLE FOO MODIFY (BAR2 VARCHAR2(100))
Error report:
SQL Error: ORA-01441: cannot decrease column length because some value is too big
01441. 00000 - "cannot decrease column length because some value is too big"
table FOO altered.
Reduce precision by creating a new column.
ALTER TABLE FOO ADD (BAR3 NUMBER(10));
UPDATE FOO SET Bar3 = Bar;
ALTER TABLE FOO DROP COLUMN BAR;
ALTER TABLE FOO RENAME COLUMN BAR3 TO BAR;
Best Answer
Key preserved means that 1 key value goes to 1 table. Giving counter examples may help you understand this concept better.
Example1:
Your view contains aggregation. Suppose you have following view structure.
In this example: your values comes from more than one rows. If you try to update AverageSalary in this view, database has no way to find WHICH rows to update.
Example2: Your view shows values from more than one table. Your view shows values from PERSON and PERSON_CONTACT_DETAILS(ID,PersonID,ContactType,ContactValue) table.
Example rows :
You join this 2 table and show more business friendly information in view.
PersonId,Name,LastName, Phone1,Email1
Here you would like to update Phone1 and Email1. But your personID maps to two different rows, may be more rows, in this example. In this view, again, database has no way to find WHICH rows to update.
Note: If you restrict your view sql and makes it clear to find which rows to update it may work.
This two example is first examples which comes to my mind. They can be increased. But concept is clear. Database needs to map 1 key value to 1 table. For example you have one to one PERSON, PERSON_DETAILS tables. Here view and update will work since it is one to one.