Quick Reference: Oracle SQL

Useful Queries

declare 
type id_table is table of beamprocesses.BEAMPROCESS_ID%type index by binary_integer; 
found_bp_id id_table;

begin 
dbms_output.ENABLE(10000);

select beamprocess_id 
bulk collect into found_bp_id 
from beamprocesses bp 
where bp.CONTEXT_TYPE = 'SC' 
and not exists ( 
  select null from supercycle_beamprocesses sbp where sbp.beamprocess_id = bp.beamprocess_id 
);

for i in found_bp_id.first .. found_bp_id.last loop 
  dbms_output.PUT_LINE(found_bp_id(i)); 
  settings_management.delete_beamprocess(found_bp_id(i)); 
end loop;

end; /

---------------------------------------------------------

declare 
type id_table is table of beamprocesses.BEAMPROCESS_ID%type index by binary_integer; 
l1 beamprocesses.BEAMPROCESS_ID%type;
i beamprocesses.BEAMPROCESS_ID%type;
found_bp_id id_table;

begin 
dbms_output.ENABLE(10000);

select beamprocess_id 
bulk collect into found_bp_id 
from beamprocesses bp 
where bp.CONTEXT_TYPE = 'SC' 
and not exists ( 
  select null from supercycle_beamprocesses sbp where sbp.beamprocess_id = bp.beamprocess_id 
);


l1 := found_bp_id.first;
while (l1 is not null)
loop
dbms_output.PUT_LINE(l1); 
l1 := found_bp_id.next(l1);
--settings_management.delete_beamprocess(l1); 
end loop;

end;

Synonym creation

-- drop an (re)create synonyms for the lsa_data tablespace
SET serveroutput ON;
DECLARE 
  l_access_rights VARCHAR2(1) := 'W'; -- can be 'W' for write or 'R' for read access
  l_from_user VARCHAR2(30) := 'LSA'; -- table owner of the real tables
  l_to_user VARCHAR2(30) := 'LSA_APPS'; -- user that should receive the synonyms
BEGIN
  DBMS_OUTPUT.enable(1000000);
  -- drop all the existing synonyms for l_to_user that point to tables from l_from_user
  FOR x IN
    (SELECT 'DROP SYNONYM "' || l_to_user || '"."' || synonym_name || '";' stmt
     FROM
      (SELECT synonym_name
       FROM all_synonyms
       WHERE owner = l_to_user
       AND table_owner = l_from_user)
    )
  LOOP
    DBMS_OUTPUT.PUT_LINE(x.stmt);
  END LOOP;

  -- (re)create synonyms for l_to_user that point to tables and views from l_from_user
  FOR x IN

      (SELECT DISTINCT OBJECT_NAME, OBJECT_TYPE
       FROM all_objects
       WHERE object_type in ('TABLE', 'VIEW', 'SEQUENCE', 'PROCEDURE', 'FUNCTION', 'PACKAGE', 'TYPE')
       AND owner = l_from_user
       ORDER BY object_name)
    
  LOOP
  DBMS_OUTPUT.PUT_LINE('CREATE OR REPLACE SYNONYM "' || l_to_user || '"."' || x.OBJECT_NAME || '" FOR "' || l_from_user || '"."' || x.OBJECT_NAME || '";');
    IF x.object_type IN ('TABLE','VIEW') THEN
      IF l_access_rights = 'W' THEN
        DBMS_OUTPUT.PUT_LINE('GRANT SELECT, UPDATE, INSERT, DELETE ON "' || l_from_user || '"."' || x.OBJECT_NAME || '" TO "' || l_to_user || '";');
      ELSE
        DBMS_OUTPUT.PUT_LINE('GRANT SELECT ON "' || l_from_user || '"."' || x.OBJECT_NAME || '" TO "' || l_to_user || '";');
      END IF;
    ELSIF x.object_type IN ('SEQUENCE') THEN
     IF l_access_rights = 'W' THEN
        DBMS_OUTPUT.PUT_LINE('GRANT SELECT, ALTER ON "' || l_from_user || '"."' || x.OBJECT_NAME || '" TO "' || l_to_user || '";');
      ELSE
        DBMS_OUTPUT.PUT_LINE('GRANT SELECT ON "' || l_from_user || '"."' || x.OBJECT_NAME || '" TO "' || l_to_user || '";');
      END IF;
    ELSIF x.object_type IN ('PROCEDURE','FUNCTION','PACKAGE') THEN
      IF l_access_rights = 'W' THEN
        DBMS_OUTPUT.PUT_LINE('GRANT EXECUTE ON "' || l_from_user || '"."' || x.OBJECT_NAME || '" TO "' || l_to_user || '";');
      ELSE
        DBMS_OUTPUT.PUT_LINE('REVOKE EXECUTE ON "' || l_from_user || '"."' || x.OBJECT_NAME || '" FROM "' || l_to_user || '";');
      END IF;
    END IF;
  END LOOP;

  DBMS_OUTPUT.new_line();
END;
/

Create a read only database user

grant select any table to lsaread;
alter user lsaread quota unlimited on users;