CODEWITHSUNDEEP
sqloracle-databasedeclaration
Vloxxity
Vloxxity

Reputation: 980

SQL create table use %type at column

I'm trying to create a backuptable of one of my tables (in a function),

CREATE TABLE TBTestBackup
           (
             colum1           user.TBTest.colum1%type,
             colum2           user.TBTest.colum2%type,
             colum3           user.TBTest.colum3%type
             colum31           user.TBTest.colum3%type, --new column with same type as colum3
             colum4           user.TBTest2.column15type, --column from other table
             colum4           CHAR    (12 BYTE), --new column with fixed type
           ) TABLESPACE user_DATA

But I red this won't work, now my question is how could I make this as much dynamic as possible so I don't have to update the datatypes at the backup script every time I change a datatype e.g. from:

VARCHAR2(24 CHAR) to VARCHAR2(50 CHAR)

(the table-columns are fixed they wont change) this doesn't happen often but we had to do it some times because the field wasn't great enough for a specific value and then nobody updated the backup-table and id gave some errors.

EDIT: I forgot something necessary:

SUM:

Upvotes: 6

Views: 24681

Answers (5)

Alex Poole
Alex Poole

Reputation: 191295

Based on your updated requirements, to create a table based on the types in these two tables:

create table t1 (col1 number, col2 varchar2(2), col3 date);
create table t2 (col1 varchar2(10 char));

You can just join them together, with a filter that always evaluates to false as Orangecrush suggested:

create table tb tablespace users as
select t1.col1 as col1, t1.col2 as col2, t1.col3 as col3,
    t1.col3 as col4, t2.col1 as col5, cast(null as varchar2(12 byte)) as col6
from t1
cross join t2
where null is not null;

Normally a cross join would be unwelcome, but the optimizer is clever enough to realise that the filter means it doesn't need to actually hit the tables at all. You can use a normal inner join if there are fields you can join on though, of course.

desc tb

Name Null Type              
---- ---- ----------------- 
COL1      NUMBER            
COL2      VARCHAR2(2)       
COL3      DATE              
COL4      DATE               -- new column with same type as t1.col3
COL5      VARCHAR2(10 CHAR)  -- column from other table
COL6      VARCHAR2(12)       -- new column with fixed type

Upvotes: 5

mavroprovato
mavroprovato

Reputation: 8362

You could create the table with:

CREATE TABLE TBTestBackup AS 
SELECT colum1, column2, column2
FROM user.TBTest

Based on your edit: You can change the select statement to anything you like. Join the two tables and select 3 columns from one table and 2 from the other one

Upvotes: 4

tbone
tbone

Reputation: 15473

Sounds like a materialized view would work for what you want.

create materialized view my_backup
tablespace whatever
nologging
build immediate
refresh complete on demand
as
select t.* 
from some_table t;

When you want to refresh your backup, do a full refresh on the mat view:

exec dbms_mview.refresh('MY_BACKUP', 'C', atomic_refresh=>false);

Also note that this is no substitute for RMAN or other tools used by DBAs to do backups, but fits what you're asking to do.

Also note that you can easily add extra columns to the query as needed.

Upvotes: 2

Lupuss
Lupuss

Reputation: 649

The syntax is

CREATE TABLE TBTestBackup as (SELECT * FROM TBTest)

Not sure how you could automate changes to the datatypes, but anyway it's not something that should change often (if it does you need to work on that).

Upvotes: 3

Orangecrush
Orangecrush

Reputation: 1990

You can use the statement to create the backup.

CREATE TABLE TBTestBackup AS SELECT * FROM ORIGINAL_TABLE_NAME WHERE 1=2;

This is assuming that you do not want the data in the backup table. If you do want the data as well, just remove the WHERE condition from the above statement.

Upvotes: 4

Related Questions