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;

Killing all processes containing a specific pattern in their process name

Say you want to kill all processes spawned from your Oracle Home. In Linux/Unix there is a nice command that lets you achieve this very easily.

If your Oracle Home is /u01/app/oracleHome then any oracle related process will have oracleHome in it's name. To kill all such processes at once issue the command :

pkill -9 -f oracleHome

Fixing "authentication is required to set the network proxy ..." on Redhat/REL/OEL

Whenever i started  a vncsession i would get an popup window stating :

authentication is required to set the network proxy used for downloading packages.  An
application is attempting to perform an action that requires privileges.
Authentication as the super user is required to perform this action" and asking
for the root password.

I didn't have the root password for this machine and hitting cancel would just bring back the pop-up in a few minutes. After googling here is the best way i found to fix this problem:

From a terminal window run "gnome-session-properties" and un-check "PackageKit
Update Applet"

Finally restart your vncserver and the issue should be gone.

How to find out an Oracle DB Service Name.

Simply run the following query :

show parameter service_name;

How to export query results from Oracle SQL Developer tool

So you ran a query in Oracle SQL Developer and it returned many rows. You would like to take this data and export it to a excel , CSV,  PDF or HTML file for further processing and easier navigation. You can achieve this by following these steps:

1. SQL Developer usually return a limited number of rows, to get all rows go to the last returned row and press Ctrl + End. This will fetch all rows.
2. Now Select all your data by highlighting it.
3. Right click on your selection and select Export Data and finally choose a file format.
4. Give your file a name and location and confirm the export operation.

Kill connections to an Oracle Schema

You can kill all connections by running the code below. Just Replace SCHEMA with your schema name.

declare str varchar2(100);
begin
for SES in (select sid,serial# from v$session where username='SCHEMA'  )
loop
str := 'alter system disconnect session '|| '''' || to_char(SES.sid) ||','||to_char(SES.serial#)||'''' || ' ' || 'immediate';
execute immediate str;
end loop;
end;


To kill a single connection, first find the connection by running:

select * from v$session;

Then run:

alter system kill session 'sid,serial#';

where sid and serial# are extracted from your first query of v$session above.

How to run sqlplus

To run sqlplus you need 3 env var set. Set them as follows if they are not set already. ( you can check if they are set by doing "echo $ORACLE_SID" for example to see if your SID is set. )

export ORACLE_HOME=<your Oracle Home>
export PATH=%ORACLE_HOME%\bin;%PATH%
export ORACLE_SID=<your SID>

Then run the following at the command line to start sqlplus.
sqlplus /nolog

This starts sqlplus with no user logged in.

How to export a schema in Oracle 11g

First make sure your ORACLE_SID,  PATH and ORACLE_HOME Env. variables are set. If not set them like below :
export ORACLE_HOME=<your Oracle Home>
export PATH=%ORACLE_HOME%\bin;%PATH%
export ORACLE_SID=<your SID>

Then run the following command from the command line:

expdp system/system_password@sid schemas=Schema_Name dumpfile=Schema_Name.dmp

How to unlock an Oracle user

ALTER USER myuser ACCOUNT UNLOCK;

Get Current Time or Date of an Oracle DB

Note: This is on Oracle DB

To get the server Date:
SELECT sysdate FROM dual;
This will give you the system date in this format, Ex: 18-AUG-09

To get the server TimeZone:
SELECT sessiontimezone FROM dual;
This will give you the system timezone in this format, Ex: Asia/Calcutta

To get the server time:
SELECT systimestamp FROM dual;
This will give you the system time in this format,
Ex: 18-AUG-09 04.49.43.648480000 AM -07:00

How to findout who locks your Oracle DB user

select USERID,
         userhost,
         decode(returncode,01017,'Login Error','Acount Locked') "ISSUE",
         spare1,
         TO_CHAR ( CAST(
                                   ( FROM_TZ(
                                                     CAST(
                                                               TO_DATE(
                                                                              TO_CHAR( ntimestamp# , 'DD/MM/YYYY HH:MI PM'),
                                                                              'DD/MM/YYYY HH:MI PM'
                                                                            )
                                                                AS    TIMESTAMP
                                                             ) ,
                                                      'GMT'
                                                   ) AT LOCAL
                                    )
                                    AS TIMESTAMP)
                                  , 'DD/MM/YYYY HH:MI PM')  "Time",
           sqltext,
           comment$text from SYS.aud$
           where ( returncode=1017 OR returncode=28000 )
           order by ntimestamp# desc ;

How to delete records older than 7 days from sys.aud$

delete from sys.aud$ where trunc(NTIMESTAMP# ) < trunc(sysdate - 7);

you can change the number 7 above to any number of days you like.