Oracle development topics: Common Operations, copying table structures, etc.

1. Copy the table structure and data:

Create Table table_name_new as select * From table_name_old

 

2. copy only the table structure:

Create Table table_name_new as select * From table_name_old where 1 = 2;

 

3. Copy table data only:

If the two tables have the same structure:

Insert into table_name_new select * From table_name_old

If the two tables have different structures:

Insert into table_name_new (column1, column2. ..) Select column1, column2.... from table_name_old

 

How to query the first sorted record in Oracle

Select * from (
Select TB. *, rownum from table_name TB where column_name like '000000' order by ID)
Where rownum = 1

 

How to View tables in Oracle

You can use the query statement to query the system table user_tables.

For example, to list all tables in a user table, which table space is used, you can use the following statement to query

Select table_name, tablespace_name from user_tables;

 

How to check Oracle tablespace usage

Select * From dba_free_space

Select * From dba_data_files

Select B. file_id File ID, B. tablespace_name tablespace name, B. bytes,
(B. bytes-sum (nvl (A. bytes, 0) used, sum (nvl (A. bytes, 0) remaining space,
Sum (nvl (A. bytes, 0)/(B. bytes) * 100 percentage remaining
From dba_free_space A, dba_data_files B
Where a. file_id = B. file_id
Group by B. tablespace_name, B. file_id, B. bytes
Order by B. file_id