How to generate a sql script that drops all tables starting with a certain prefix

In some circumstances, you may want to drop all tables in a schema that start with certain prefix. Say for example you like drop all tables starting with the prefix 'TMP_'.

You can execute:

BEGIN
  FOR t IN ( SELECT table_name FROM user_tables WHERE table_name LIKE 'TMP_%' )
  LOOP
    EXECUTE IMMEDIATE 'DROP TABLE ' || t.table_name || ' CASCADE CONSTRAINTS';
  END LOOP;
END;

This block finds and drops all tables whose name start with 'TMP_'.

Another way is to first generate a list of drop statements, save the list as a sql script and finally execute it. Personally I like this way better since it lets me to see and be sure exactly what is going to be removed.

This block generates a list of drop statements for all tables in your schema starting with 'TMP_' :

select 'drop table ' || table_name || ' cascade constraints' || ';'
from   user_tables
where  table_name like 'TMP_%';

You can then take this generated list, save it as a sql script and execute it as you wish.

Finding out tablespace size, used space and free space in Oracle database

This query which should be run as SYS or SYSTEM user, will give you the following information:

• Tablespace Name
• % used
• Space allocated to tablespace
• Space used in tablespace
• Space free in tablespace
• Number of datafiles used by tablespace

SELECT  a.tablespace_name,

    ROUND (((c.BYTES - NVL (b.BYTES, 0)) / c.BYTES) * 100,2) percentage_used,

    c.BYTES / 1024 / 1024 space_allocated,

    ROUND (c.BYTES / 1024 / 1024 - NVL (b.BYTES, 0) / 1024 / 1024,2) space_used,

    ROUND (NVL (b.BYTES, 0) / 1024 / 1024, 2) space_free, 

    c.DATAFILES

  FROM dba_tablespaces a,

       (    SELECT   tablespace_name, 

                  SUM (BYTES) BYTES

           FROM   dba_free_space

       GROUP BY   tablespace_name

       ) b,

      (    SELECT   COUNT (1) DATAFILES, 

                  SUM (BYTES) BYTES, 

                  tablespace_name

           FROM   dba_data_files

       GROUP BY   tablespace_name

    ) c

  WHERE b.tablespace_name(+) = a.tablespace_name 

    AND c.tablespace_name(+) = a.tablespace_name

ORDER BY NVL (((c.BYTES - NVL (b.BYTES, 0)) / c.BYTES), 0) DESC;

How to find out an Oracle DB Service Name.

Simply run the following query :

show parameter service_name;