There are a couple of ways to create a copy of an Oracle table without copying the data:
1. Using the CREATE TABLE AS SELECT statement (CTAS):
CREATE TABLE new_table_name AS
SELECT * FROM original_table_name WHERE 1=0;
This query will create a new table with the same structure as the original table but without any data. The WHERE 1=0 clause ensures that no rows are selected from the original table, so no data is copied.
2. Using the CREATE TABLE statement with the LIKE clause:
CREATE TABLE new_table_name
(LIKE original_table_name);
This query will create a new table with the same structure as the original table but without any data. The LIKE clause is used to copy the system of the original table but not the data.
3. Using Data Pump export and import:
expands user/password schemas=schema_name directory=data_pump_dir dumpfile=table_name.dump table_exists_action=replace include=table:table_name
Then
impdp user/password directory=data_pump_dir dumpfile=table_name.dmp table_exists_action=replace
This way, you can create a copy of the table with the same structure but without any data. In all of the above examples, you should replace "new_table_name" with the desired name for the new table and "original_table_name" with the name of the table that you want to copy. It's also worth noting that depending on your use case, it might be more appropriate to use the CREATE TABLE AS SELECT statement with specific SELECT information that only returns certain columns or rows.
0 Komentar