Copy all tables from other owner

View All Scripts Login to Run Script

Script Name Copy all tables from other owner
Description Create a stored procedure in order to copy all tables from other owner The stored procedure will take a string as input for the schema name
Area PL/SQL General / PL/SQL Procedures, Functions, Packages
Contributor pchen0821@gmail.com
Created Saturday March 30, 2019

Statement 1

CREATE OR REPLACE PROCEDURE copy_tables (schema_name IN string) AS 
    v_sql string(32767); 
    v_exist number; 
BEGIN 
    for l_tables in ( 
        select table_name 
        from all_tables 
        where owner = schema_name 
    ) loop 
        dbms_output.put_line('Creating Table - ' || l_tables.table_name || ' from ' || schema_name); 
        select count(*) into v_exist from user_tables where table_name = l_tables.table_name; 
        if v_exist = 0 then 
            v_sql := 'create table ' || l_tables.table_name || ' as select * from '|| schema_name || '.' || l_tables.table_name; 
            execute immediate v_sql; 
        end if; 
    end loop; 
EXCEPTION when others then 
     dbms_output.put_line(sqlerrm); 
END;

Procedure created.

Statement 2

exec copy_tables ('HR')

Statement processed.
Creating Table - COUNTRIES from HR
Creating Table - DEPARTMENTS from HR
Creating Table - EMPLOYEES from HR
Creating Table - JOBS from HR
Creating Table - JOB_HISTORY from HR
Creating Table - LOCATIONS from HR
Creating Table - REGIONS from HR

Statement 3

select * from user_tables


TABLE_NAME TABLESPACE_NAME CLUSTER_NAME IOT_NAME STATUS PCT_FREE PCT_USED INI_TRANS MAX_TRANS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE FREELISTS FREELIST_GROUPS LOGGING BACKED_UP NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN AVG_SPACE_FREELIST_BLOCKS NUM_FREELIST_BLOCKS DEGREE INSTANCES CACHE TABLE_LOCK SAMPLE_SIZE LAST_ANALYZED PARTITIONED IOT_TYPE TEMPORARY SECONDARY NESTED BUFFER_POOL FLASH_CACHE CELL_FLASH_CACHE ROW_MOVEMENT GLOBAL_STATS USER_STATS DURATION SKIP_CORRUPT MONITORING CLUSTER_OWNER DEPENDENCIES COMPRESSION COMPRESS_FOR DROPPED READ_ONLY SEGMENT_CREATED RESULT_CACHE CLUSTERING ACTIVITY_TRACKING DML_TIMESTAMP HAS_IDENTITY CONTAINER_DATA INMEMORY INMEMORY_PRIORITY INMEMORY_DISTRIBUTE INMEMORY_COMPRESSION INMEMORY_DUPLICATE DEFAULT_COLLATION DUPLICATED SHARDED EXTERNAL HYBRID CELLMEMORY CONTAINERS_DEFAULT CONTAINER_MAP EXTENDED_DATA_LINK EXTENDED_DATA_LINK_MAP INMEMORY_SERVICE INMEMORY_SERVICE_NAME CONTAINER_MAP_OBJECT MEMOPTIMIZE_READ MEMOPTIMIZE_WRITE HAS_SENSITIVE_COLUMN ADMIT_NULL DATA_LINK_DML_ENABLED LOGICAL_REPLICATION
COUNTRIES LIVESQL_USERS  -  - VALID 10  - 1 255 65536 1048576 1 2147483645  -  -  - YES N 25 4 0 0 0 15 0 0          1          1     N ENABLED 25 30-MAR-19 NO  - N N NO DEFAULT DEFAULT DEFAULT DISABLED YES NO  - DISABLED YES  - DISABLED DISABLED  - NO NO YES DEFAULT NO  -  - NO NO DISABLED  -  -  -  - USING_NLS_COMP N N NO NO  - NO NO NO NO  -  - NO DISABLED DISABLED NO NO NO ENABLED
DEPARTMENTS LIVESQL_USERS  -  - VALID 10  - 1 255 65536 1048576 1 2147483645  -  -  - YES N 27 4 0 0 0 21 0 0          1          1     N ENABLED 27 30-MAR-19 NO  - N N NO DEFAULT DEFAULT DEFAULT DISABLED YES NO  - DISABLED YES  - DISABLED DISABLED  - NO NO YES DEFAULT NO  -  - NO NO DISABLED  -  -  -  - USING_NLS_COMP N N NO NO  - NO NO NO NO  -  - NO DISABLED DISABLED NO NO NO ENABLED
EMPLOYEES LIVESQL_USERS  -  - VALID 10  - 1 255 65536 1048576 1 2147483645  -  -  - YES N 107 5 0 0 0 69 0 0          1          1     N ENABLED 107 30-MAR-19 NO  - N N NO DEFAULT DEFAULT DEFAULT DISABLED YES NO  - DISABLED YES  - DISABLED DISABLED  - NO NO YES DEFAULT NO  -  - NO NO DISABLED  -  -  -  - USING_NLS_COMP N N NO NO  - NO NO NO NO  -  - NO DISABLED DISABLED NO NO NO ENABLED
JOBS LIVESQL_USERS  -  - VALID 10  - 1 255 65536 1048576 1 2147483645  -  -  - YES N 19 4 0 0 0 33 0 0          1          1     N ENABLED 19 30-MAR-19 NO  - N N NO DEFAULT DEFAULT DEFAULT DISABLED YES NO  - DISABLED YES  - DISABLED DISABLED  - NO NO YES DEFAULT NO  -  - NO NO DISABLED  -  -  -  - USING_NLS_COMP N N NO NO  - NO NO NO NO  -  - NO DISABLED DISABLED NO NO NO ENABLED
JOB_HISTORY LIVESQL_USERS  -  - VALID 10  - 1 255 65536 1048576 1 2147483645  -  -  - YES N 11 4 0 0 0 31 0 0          1          1     N ENABLED 11 30-MAR-19 NO  - N N NO DEFAULT DEFAULT DEFAULT DISABLED YES NO  - DISABLED YES  - DISABLED DISABLED  - NO NO YES DEFAULT NO  -  - NO NO DISABLED  -  -  -  - USING_NLS_COMP N N NO NO  - NO NO NO NO  -  - NO DISABLED DISABLED NO NO NO ENABLED
LOCATIONS LIVESQL_USERS  -  - VALID 10  - 1 255 65536 1048576 1 2147483645  -  -  - YES N 23 4 0 0 0 49 0 0          1          1     N ENABLED 23 30-MAR-19 NO  - N N NO DEFAULT DEFAULT DEFAULT DISABLED YES NO  - DISABLED YES  - DISABLED DISABLED  - NO NO YES DEFAULT NO  -  - NO NO DISABLED  -  -  -  - USING_NLS_COMP N N NO NO  - NO NO NO NO  -  - NO DISABLED DISABLED NO NO NO ENABLED
REGIONS LIVESQL_USERS  -  - VALID 10  - 1 255 65536 1048576 1 2147483645  -  -  - YES N 4 4 0 0 0 14 0 0          1          1     N ENABLED 4 30-MAR-19 NO  - N N NO DEFAULT DEFAULT DEFAULT DISABLED YES NO  - DISABLED YES  - DISABLED DISABLED  - NO NO YES DEFAULT NO  -  - NO NO DISABLED  -  -  -  - USING_NLS_COMP N N NO NO  - NO NO NO NO  -  - NO DISABLED DISABLED NO NO NO ENABLED

7 rows selected.

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database

TABLE_NAME	TABLESPACE_NAME	CLUSTER_NAME	IOT_NAME	STATUS	PCT_FREE	PCT_USED	INI_TRANS	MAX_TRANS	INITIAL_EXTENT	NEXT_EXTENT	MIN_EXTENTS	MAX_EXTENTS	PCT_INCREASE	FREELISTS	FREELIST_GROUPS	LOGGING	BACKED_UP	NUM_ROWS	BLOCKS	AVG_ROW_LEN	DEGREE	INSTANCES	CACHE	TABLE_LOCK	SAMPLE_SIZE	LAST_ANALYZED	PARTITIONED	IOT_TYPE	TEMPORARY	SECONDARY	NESTED	BUFFER_POOL	FLASH_CACHE	CELL_FLASH_CACHE	ROW_MOVEMENT	GLOBAL_STATS	USER_STATS	DURATION	SKIP_CORRUPT	MONITORING	CLUSTER_OWNER	DEPENDENCIES	COMPRESSION	COMPRESS_FOR	DROPPED	READ_ONLY	SEGMENT_CREATED	RESULT_CACHE	CLUSTERING	ACTIVITY_TRACKING	DML_TIMESTAMP	HAS_IDENTITY	CONTAINER_DATA	INMEMORY	INMEMORY_PRIORITY	INMEMORY_DISTRIBUTE	INMEMORY_COMPRESSION	INMEMORY_DUPLICATE	DEFAULT_COLLATION	DUPLICATED	SHARDED	EXTERNAL	HYBRID	CELLMEMORY	CONTAINERS_DEFAULT	CONTAINER_MAP	EXTENDED_DATA_LINK	EXTENDED_DATA_LINK_MAP	INMEMORY_SERVICE	INMEMORY_SERVICE_NAME	CONTAINER_MAP_OBJECT	MEMOPTIMIZE_READ	MEMOPTIMIZE_WRITE	HAS_SENSITIVE_COLUMN	ADMIT_NULL	DATA_LINK_DML_ENABLED	LOGICAL_REPLICATION
COUNTRIES	LIVESQL_USERS	-	-	VALID	10	-	1	255	65536	1048576	1	2147483645	-	-	-	YES	N	25	4	15	1	1	N	ENABLED	25	30-MAR-19	NO	-	N	N	NO	DEFAULT	DEFAULT	DEFAULT	DISABLED	YES	NO	-	DISABLED	YES	-	DISABLED	DISABLED	-	NO	NO	YES	DEFAULT	NO	-	-	NO	NO	DISABLED	-	-	-	-	USING_NLS_COMP	N	N	NO	NO	-	NO	NO	NO	NO	-	-	NO	DISABLED	DISABLED	NO	NO	NO	ENABLED
DEPARTMENTS	LIVESQL_USERS	-	-	VALID	10	-	1	255	65536	1048576	1	2147483645	-	-	-	YES	N	27	4	21	1	1	N	ENABLED	27	30-MAR-19	NO	-	N	N	NO	DEFAULT	DEFAULT	DEFAULT	DISABLED	YES	NO	-	DISABLED	YES	-	DISABLED	DISABLED	-	NO	NO	YES	DEFAULT	NO	-	-	NO	NO	DISABLED	-	-	-	-	USING_NLS_COMP	N	N	NO	NO	-	NO	NO	NO	NO	-	-	NO	DISABLED	DISABLED	NO	NO	NO	ENABLED
EMPLOYEES	LIVESQL_USERS	-	-	VALID	10	-	1	255	65536	1048576	1	2147483645	-	-	-	YES	N	107	5	69	1	1	N	ENABLED	107	30-MAR-19	NO	-	N	N	NO	DEFAULT	DEFAULT	DEFAULT	DISABLED	YES	NO	-	DISABLED	YES	-	DISABLED	DISABLED	-	NO	NO	YES	DEFAULT	NO	-	-	NO	NO	DISABLED	-	-	-	-	USING_NLS_COMP	N	N	NO	NO	-	NO	NO	NO	NO	-	-	NO	DISABLED	DISABLED	NO	NO	NO	ENABLED
JOBS	LIVESQL_USERS	-	-	VALID	10	-	1	255	65536	1048576	1	2147483645	-	-	-	YES	N	19	4	33	1	1	N	ENABLED	19	30-MAR-19	NO	-	N	N	NO	DEFAULT	DEFAULT	DEFAULT	DISABLED	YES	NO	-	DISABLED	YES	-	DISABLED	DISABLED	-	NO	NO	YES	DEFAULT	NO	-	-	NO	NO	DISABLED	-	-	-	-	USING_NLS_COMP	N	N	NO	NO	-	NO	NO	NO	NO	-	-	NO	DISABLED	DISABLED	NO	NO	NO	ENABLED
JOB_HISTORY	LIVESQL_USERS	-	-	VALID	10	-	1	255	65536	1048576	1	2147483645	-	-	-	YES	N	11	4	31	1	1	N	ENABLED	11	30-MAR-19	NO	-	N	N	NO	DEFAULT	DEFAULT	DEFAULT	DISABLED	YES	NO	-	DISABLED	YES	-	DISABLED	DISABLED	-	NO	NO	YES	DEFAULT	NO	-	-	NO	NO	DISABLED	-	-	-	-	USING_NLS_COMP	N	N	NO	NO	-	NO	NO	NO	NO	-	-	NO	DISABLED	DISABLED	NO	NO	NO	ENABLED
LOCATIONS	LIVESQL_USERS	-	-	VALID	10	-	1	255	65536	1048576	1	2147483645	-	-	-	YES	N	23	4	49	1	1	N	ENABLED	23	30-MAR-19	NO	-	N	N	NO	DEFAULT	DEFAULT	DEFAULT	DISABLED	YES	NO	-	DISABLED	YES	-	DISABLED	DISABLED	-	NO	NO	YES	DEFAULT	NO	-	-	NO	NO	DISABLED	-	-	-	-	USING_NLS_COMP	N	N	NO	NO	-	NO	NO	NO	NO	-	-	NO	DISABLED	DISABLED	NO	NO	NO	ENABLED
REGIONS	LIVESQL_USERS	-	-	VALID	10	-	1	255	65536	1048576	1	2147483645	-	-	-	YES	N	4	4	14	1	1	N	ENABLED	4	30-MAR-19	NO	-	N	N	NO	DEFAULT	DEFAULT	DEFAULT	DISABLED	YES	NO	-	DISABLED	YES	-	DISABLED	DISABLED	-	NO	NO	YES	DEFAULT	NO	-	-	NO	NO	DISABLED	-	-	-	-	USING_NLS_COMP	N	N	NO	NO	-	NO	NO	NO	NO	-	-	NO	DISABLED	DISABLED	NO	NO	NO	ENABLED