How to check if a port is open on Linux

Sometimes when writing a script/program you need to know if a port your are interested in has an established connection to it or it's free.

My favorite way to do this is to run the following command ( replace PORT with the port number you want to check for ):

netstat -ln | grep :PORT

-l = Show only listening ports

-n = Show port number

 If this command doesn't return anything, it shows that your PORT is available to use.
In your script you can check $? to see the return code of the above command. If it's non-zero, the PORT is available.

One thing to note though is that, this doesn't tell you the status of your network e.g. if PORT is accessible from outside of your notwork.

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.

How to zip files with a pattern in name into a single zip file

To zip files with a certain pattern in their name in a directory, we can combine find and zip command like below :

find <DIRECTORY_PATH> -name \*<PATTERN>\* | zip <ZIP_FILE_NAME>.zip -@ 

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 fix Cannot open your terminal '/dev/pts/#' - please check.

simply run :

script /dev/null

and try again.