English 
Italiano Some PL/SQL snippets of code
- backup and recovery of a table
- interactive input with &
- find keys referencing your table
- group and count
- list column names
- list available tables in a schema
Backup and recovery of a table
First create a backup copy of your table
backup create table MY_TABLE_BACK AS select * from MY_TABLE
After your stuff, recovery the original table
insert into MY_TABLE select * from MY_TABLE_BACK delete all rows delete from MY_TABLE_BACK
Interactive input with &
select * from orders where id = &ID
before executing the query will appear a Dialog asking for variable
Find keys referencing your table
-- find foreign keys
select table_name from user_constraints
where r_constraint_name in
(select constraint_name
from user_constraints
where constraint_type in ('P','U')
and table_name = upper('&tableOfInterest')
)
Group and count
Group by and count limit to ten rows
select *
from (select count(*), t.id_order
from order_cart t
group by m.id_order
order by count(*) desc)
where rownum < 10
Rename Columns
alter table MY_TABLE_NAME rename column OLD_NAME to NEW_NAME
List column names
SELECT column_name FROM user_tab_cols WHERE table_name = 'MY_TABLE_NAME'
List available tables in a schema
The following script list data for available tables in specified schema, and order rows by table name.
SELECT *
FROM all_tables
WHERE owner = upper('&SCHEMA_NAME')
ORDER BY table_name
Since there is the parameter &SCHEMA_NAME, before executing the query will appear a dialog in which you can insert the name of schema.
References:
http://viralpatel.net/blogs/2009/08/fast-data-copy-with-create-table-select-from-in-plsql.html