mujahidk

-- Oracle: All user constraints

select * from user_constraints

mujahidk

SET SERVEROUTPUT ON               
BEGIN
    FOR const IN ( SELECT TABLE_NAME, CONSTRAINT_NAME
               FROM USER_CONSTRAINTS
               WHERE CONSTRAINT_TYPE IN ('R', 'C') /*Foreign Key and Check constraints.*/ )
    LOOP
        EXECUTE IMMEDIATE 'ALTER TABLE '||const.TABLE_NAME ||' DROP CONSTRAINT '||const.CONSTRAINT_NAME;
        DBMS_OUTPUT.PUT_LINE('Dropped constraint '||const.CONSTRAINT_NAME||' of table '||const.TABLE_NAME); 
    END LOOP;
END;

mujahidk

-- Oracle Sys GUID 
SELECT SYS_GUID() FROM DUAL;

mujahidk

SET SERVEROUTPUT ON
DECLARE ROW_COUNT NUMBER(10);
BEGIN
  -- All user tables with column ID.
  FOR TAB IN (SELECT TABLE_NAME FROM USER_TAB_COLUMNS WHERE COLUMN_NAME = 'ID' ORDER BY TABLE_NAME)
  LOOP
    EXECUTE IMMEDIATE 'SELECT COUNT(1) FROM ' || TAB.TABLE_NAME INTO ROW_COUNT;
    DBMS_OUTPUT.PUT_LINE( TAB.TABLE_NAME || ',' || ROW_COUNT );
  END LOOP;
END;

mujahidk

-- Query to find the invalid user objects in Oracle DB
SELECT OBJECT_NAME,
  OBJECT_TYPE,
  STATUS
FROM USER_OBJECTS
WHERE object_type IN ('PROCEDURE', 'TRIGGER', 'TABLE', 'VIEW', 'FUNCTION', 'INDEX')
AND STATUS         ='INVALID';

--Query to find database view errors in the Oracle DB.
SELECT * FROM ALL_ERRORS WHERE TYPE = 'VIEW' ORDER BY SEQUENCE ASC;

mujahidk

-- List the invalid objects from the Oracle DB

DECLARE
    iCount INTEGER := 0;
    objects VARCHAR2(4000 CHAR);
    new_line VARCHAR2(2):= CHR(13) || CHR(10);
BEGIN
    FOR usr_objects IN
    (
        SELECT object_name, object_type, status

mujahidk

-- db version and other information

SELECT * FROM V$VERSION;

SELECT * FROM PRODUCT_COMPONENT_VERSION;

SELECT SYS_CONTEXT('USERENV','SERVICE_NAME') FROM DUAL;

SELECT * FROM GLOBAL_NAME;

mujahidk

-- Tunring off and on oracle recycle bin

ALTER SYSTEM SET RECYCLEBIN = OFF;

ALTER SYSTEM SET RECYCLEBIN = ON;

-- Purge oracle recycle bin
PURGE RECYCLEBIN;

mujahidk

//Select empty space between a number and letter
(?<=\d)\s+(?=\w)

//Or, the above will match the line endings too
(?<=\d)[ ]+(?=\w)

mujahidk

--connection as dba
connect <system>/<password> as sysdba;

--drop oracle user/schema
drop user <user_name> cascade;

--create a new user and schema
create user <user_name> identified by <password>;

--grant dba, other roles and privileges