Oracle

ALTER SESSION SET CURRENT_SCHEMA=CBX;
--To check all schema in DB
select username from sys.dba_users;

--check DB links
select * from all_db_links

--disable password verification
alter profile default limit password_verify_function null;

--enable password verification
alter profile default limit password_verify_function verify_function_11g;

--update user password
alter user CBX_CONNECT identified by p;

--check constraints
SELECT * user_constraints where table_name='CNT_PROJECT' and CONSTRAINT_NAME='PROJECT_UNIQUE';

--check column
SELECT * from cols where table_name = 'CNT_ITEM'  and column_name = 'LDL_LICENSE_FEE';

--To check table size

SELECT segment_name AS TABLENAME,BYTES/1024/1024||'MB' FROM user_segments WHERE segment_name='CNT_RFQ';

--after delete statement, need run below statement firstly, and then check size again
alter table my_objects enable row movement;
alter table my_objects shrink space;

--To check DB lock

SELECT A.OWNER, A.OBJECT_NAME, B.XIDUSN, B.XIDSLOT, B.XIDSQN,
         B.SESSION_ID, B.ORACLE_USERNAME, B.OS_USER_NAME, B.PROCESS,
         B.LOCKED_MODE, C.MACHINE, C.STATUS, C.SERVER, C.SID,
         C.SERIAL#, C.PROGRAM
    FROM ALL_OBJECTS A, V$LOCKED_OBJECT B, SYS.GV_$SESSION C
   WHERE (A.OBJECT_ID = B.OBJECT_ID) AND (B.PROCESS = C.PROCESS)
ORDER BY 1, 2;

--To kill session
alter system kill session '426, 4887';

--To check current DB SQL running status

SELECT c.STATUS,
         time_remaining / 60,
         sql_text SQL,
         machine,
         MESSAGE
    FROM V$SESSION_LONGOPS a
         INNER JOIN v$sql b ON a.sql_hash_value = b.hash_value
         INNER JOIN v$session c ON a.sid = c.sid
   WHERE sofar < totalwork
ORDER BY time_remaining DESC;

--To delete huge data table

set SERVEROUTPUT ON;
DECLARE
    I_COMMIT PLS_INTEGER := 100000;
    I_DELETED_ROW_COUNT PLS_INTEGER := 0;
    I_ITERATION PLS_INTEGER := 0;
BEGIN
    DBMS_OUTPUT.PUT_LINE('Start delete of LDL_ITEM_REQUIREMENT_AND_STYLE');
LOOP
    DELETE FROM LDL_ITEM_REQUIREMENT_AND_STYLE
    WHERE ROWNUM <= I_COMMIT;
    I_DELETED_ROW_COUNT := I_DELETED_ROW_COUNT + SQL%ROWCOUNT;
    I_ITERATION := I_ITERATION + 1;
    DBMS_OUTPUT.PUT_LINE(to_char(sysdate,'yyyy-MM-dd HH24:mi:ss') || ' Iteration ' || I_ITERATION || ': ' || TRIM(TO_CHAR(I_DELETED_ROW_COUNT, '999999999999')) || ' rows deleted');
    IF SQL%ROWCOUNT = 0 THEN
        COMMIT;
        EXIT;
    END IF;
    COMMIT;
END LOOP;
DBMS_OUTPUT.PUT_LINE('Total: ' || TRIM(TO_CHAR(I_DELETED_ROW_COUNT, '999999999999')) || ' rows deleted');
DBMS_OUTPUT.PUT_LINE('End delete of LDL_ITEM_REQUIREMENT_AND_STYLE');
END;
/

About date

--gap of minuite ceil((date1 - date2) * 24 * 60 )

select ceil((To_date('2015-05-11 00:00:00' , 'yyyy-mm-dd hh24-mi-ss') - To_date('2015-04-11 23:59:59' , 'yyyy-mm-dd hh24-mi-ss')) * 24 * 60  )  FROM DUAL;

--gap of hour ceil((date1 - date2) * 24 )

select ceil((To_date('2015-05-11 00:00:00' , 'yyyy-mm-dd hh24-mi-ss') - To_date('2015-04-11 23:59:59' , 'yyyy-mm-dd hh24-mi-ss')) * 24 )  FROM DUAL;

select sysdate,add_months(sysdate,12) from dual;  --add one year

select sysdate,TO_CHAR(sysdate+1,'yyyy-mm-dd HH24:MI:SS') from dual;  --add 1 day

select sysdate,TO_CHAR(sysdate+1/24/60,'yyyy-mm-dd HH23:MI:SS') from dual;  --add 1 min

select * from cnt_item where item.updated_on > TO_DATE('2018-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')

--for loop usage--
set serveroutput on
DECLARE
    type id_list is varray(4) of char(32);
    ids id_list := id_list('522a4150aa6f4405ae84a8b3634cda2b','eb40271eb7654462a6b55a085186c483','97dfdc4693794cb3b48d79b87ae55f26','28738287907c435ca8f8156532bbae7d');
BEGIN
    FOR i IN 1..ids.count 
    LOOP
         dbms_output.put_line(ids(i));
    END LOOP;
    
    --CASE 2
    FOR T IN (SELECT ID,BASE_VIEW_ID FROM CNT_VIEW WHERE BASE_VIEW_ID IS NOT NULL)
    LOOP
       SELECT * FROM CNT_ITEM WHERE ID = T.ID;
    END LOOP;
END;
/