Can one add new columns to CREATE TABLE AS ..

[dbalearner]

Oracle does not seem to like:


CREATE TABLE T AS (SELECT *,RPAD('*',4000,'*) FROM ALL_OBJECTS) ORDR BY dbms_raddom.random
*
ERROR at line 1:
ORA-00923: FROM keyword not found where expected


However I think the only way is to do:

CREATE TABLE T AS (SELECT * FROM ALL_OBJECTS);

Table created.

ALTER TABLE T ADD IND_PAD VARCHAR2(4000);

Table altered.

UPDATE T SET IND_PAD = RPAD('*',4000,'*');

72049 rows updated.

Anyway this can be done in CTAS itself?

Thanks

 

[carp]

Two things appear wrong with your first attempt. First, you have unbalanced single quotes. Secondly, you will need to provide a column name for the expression you are trying to add.

 

[Dagon]

You also have various mispellings (e.g. ordr and dbms_raddon). To use *, you'll need to give an alias to the user_objects e.g.

CREATE TABLE T AS (SELECT a.*,RPAD('*',4000,'*') lots_of_stars FROM ALL_OBJECTS a) ORDeR BY dbms_random.random

There is no point in applying an order column when creating the table. You should apply this in queries against the table instead.

What on Earth do you want a column that consists of 4000 stars for?

 

[fredericofonseca]

Just adding something that most people are unaware of.

creating a table with CTAS is easy... but be aware that if you are trying to clone a table and keep it with exactly the same definition CTAS will NOT copy defaults for that table.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[dbalearner]

Thanks guys.

CREATE TABLE T
AS
(
SELECT
a.*
,RPAD('*',4000,'*') AS IND_PAD
,RPAD('*',4000,'*') AS IND_PAD2
FROM ALL_OBJECTS a
)
ORDER BY dbms_random.random;
ALTER TABLE T ADD CONSTRAINT T_PK PRIMARY KEY (object_id);

The above works fine. We are using this to test wait times for mag disks vs Solid State Disks. The idea being that the seek time to read from disk to the cache for SSD disappears for randon I/Os and that is where SSD scores.

we used dbms_random because otherwise the data would tend to come out ordered by object_id, we want it scrambled up.

Learner