Useful SQL

Connecting to an Oracle database by service name using sqlplus

This can be surprisingly difficult to do when there is no tnsnames.ora file setup. The trick can often be placing \"'s strategically, such as:

sqlplus username@\"hostname:port/service_name\"

Track rollback progress

Oracle

As a DBA user, execute:

SELECT USED_UBLK FROM V$TRANSACTION;

You can repeatedly execute this statement to watch the number slowly go down for large rollbacks.

Diff two tables with identical columns

Oracle

with
	query_1 as (select ...),
	query_2 as (select ...)
 
	(
	select * from query_1
		minus
	select * from query_2
 	) union all (
	select * from query_2
		minus
	select * from query_1
	);

Drop everything from a schema

Oracle

Login to the schema you wish to clear out, and execute:

BEGIN
  --Drop sequences
  FOR i IN (SELECT us.sequence_name
              FROM USER_SEQUENCES us) LOOP
    EXECUTE IMMEDIATE 'drop sequence '|| i.sequence_name ||'';
  END LOOP;

  --Drop tables
  FOR i IN (SELECT ut.table_name
              FROM USER_TABLES ut) LOOP
    EXECUTE IMMEDIATE 'drop table '|| i.table_name ||' CASCADE CONSTRAINTS ';
  END LOOP;
END;

Grant SELECT privileges to an entire schema

Oracle

Login to the schema of interest before executing the following. For tables:

declare
	cursor c1 is select table_name from user_tables;
begin
	for c in c1 loop
	    execute immediate 'GRANT SELECT ON '||c.table_name||' TO <USERNAME>';
	end loop;
end;

For views:

declare
	cursor c1 is select view_name from user_views;
begin
	for c in c1 loop
	    execute immediate 'GRANT SELECT ON '||c.view_name||' TO <USERNAME>';
	end loop;
end;

Calculate the size of a schema

Oracle

SELECT USED.TABLESPACE_NAME,
  USED.USED_BYTES AS "USED SPACE(IN GB)",
  FREE.FREE_BYTES AS "FREE SPACE(IN GB)"
FROM
  (SELECT TABLESPACE_NAME,
    TO_CHAR(SUM(NVL(BYTES,0))/1024/1024/1024, '99,999,990.99') AS USED_BYTES
  FROM USER_SEGMENTS
  GROUP BY TABLESPACE_NAME
  ) USED
INNER JOIN
  (SELECT TABLESPACE_NAME,
    TO_CHAR(SUM(NVL(BYTES,0))/1024/1024/1024, '99,999,990.99') AS FREE_BYTES
  FROM USER_FREE_SPACE
  GROUP BY TABLESPACE_NAME
  ) FREE
ON (USED.TABLESPACE_NAME = FREE.TABLESPACE_NAME);