Oracle-复制表

-1. 复制表结构及其数据：

create table table_name_new as select * from table_name_old

-2. 只复制表结构：

create table table_name_new as select * from table_name_old where 1=2;

或者：

create table table_name_new like table_name_old

-3. 只复制表数据：

-3.1 如果两个表结构一样：

insert into table_name_new select * from table_name_old

-3.2 如果两个表结构不一样：

insert into table_name_new(column1,column2...) select column1,column2... from table_name_old

Oracle-复制表+序列自增

-1. create sequence

create sequence tmp_seq
increment by 1 
start with 1
nomaxvalue
nominvalue
nocache

-2. create trigger

create or  replace trigger tmp_tri
before insert on table_name_new
for each row
begin
select tmp_seq.nextval into :new.id from dual;
end;

Oracle-存储过程举例

-1. insert

begin
for i in 1..10 loop
dbms_output.put_line(i);
insert /*+ append */ into table_name_new select * from table_name_old;
commit;
end loop;
end;
/

-2. update

begin
for i in 1..10 loop
dbms_output.put_line(i);
update table_name_new set colName=colName||'xyz'||i where id_seq<=2000000*i and id_seq>2000000*(i-1);
commit;
end loop;
end;
/

Oracle-遇到的问题

-1. tablespace 不足

ORA-01653: unable to extend tableby 16 in tablespace

solution:add a new datafile：

alter tablespace FUSION_TS_TX_DATA add datafile '/fusion/oradata/fusion/fusion_ts_tx_data1.dbf' size 20000m;
alter tablespace FUSION_TS_TX_DATA add datafile '/fusion/oradata/fusion/fusion_ts_tx_data1.dbf' size 20000m;

-2. To find out the location and size of your data files:

SELECT FILE_NAME, BYTES FROM DBA_DATA_FILES WHERE TABLESPACE_NAME = 'FUSION_TS_TX_DATA';

Tips

-1. 查看sql执行时间

set timing on

-2. 多线程

insert /*+ parallel (table_name_new,4) APPEND */ into table_name_new select * from table_name_old;