INSERT INTO USER1.TABLE1 SELECT * FROM USER2.TABLE2;COMMIT;这样就可以了.
假设你想把a用户的table2数据导入到b用户的table1首先授权 在a用户执行grant select on TABLE2 to b;导数据 INSERT INTO b.TABLE1 SELECT * FROM a.TABLE2; COMMIT;
CREATE OR REPLACE PRO_BACKUP IS CURSOR CUR_TABLES IS SELECT TABLE_NAME FROM DBA_TABLES WHERE OWNER='USER1'; BEGIN FOR X IN CUR_TABLES LOOP EXECUTE IMMEDIATE 'INSERT INTO USER1.' ||X.TABLE_NAME || ' SELECT * FROM USER2.' || X.TABLE_NAME ; COMMIT;END LOOP;
INSERT INTO USER1.TABLE1
SELECT * FROM USER2.TABLE2;COMMIT;这样就可以了.
在a用户执行grant select on TABLE2 to b;导数据
INSERT INTO b.TABLE1
SELECT * FROM a.TABLE2;
COMMIT;
CREATE OR REPLACE PRO_BACKUP
IS
CURSOR CUR_TABLES IS
SELECT TABLE_NAME FROM DBA_TABLES
WHERE OWNER='USER1';
BEGIN
FOR X IN CUR_TABLES LOOP
EXECUTE IMMEDIATE 'INSERT INTO USER1.' ||X.TABLE_NAME ||
' SELECT * FROM USER2.' || X.TABLE_NAME ;
COMMIT;END LOOP;
END;
/