Database related

1. To connect postgres database using terminal ,login as a postgres user ,set environment
   
   
   psql -h ipaddress -p 5432 -U username databasename
2. To take dump
   
   
   pg_dump databasename> pathtostorefile/filename.backup
3. To start database
   
   
   pg_ctl start
4. To stop database
   
   
   pg_ctl stop
5. To import database
   
   
   psql -U username -d databasename-f path/filename.backup
6. To drop database,connect to postgres database(databasename is postgres)
   
   
   SELECT * FROM pg_stat_activity WHERE datname = 'droppingdatabasename'; SELECT pg_terminate_backend (pg_stat_activity.pid) FROM pg_stat_activity WHERE pg_stat_activity.datname = 'droppingdatabasename';DROP DATABASE droppingdatabasename;
7. Invalid Objects in Database
   
   
   select decode(object_type, 'PROCEDURE', 'alter procedure ' || object_name || ' compile;', 'FUNCTION', 'alter function ' || object_name || ' compile;', 'PACKAGE BODY', 'alter package ' || object_name || ' compile;', 'SYNONYM', 'create or replace public synonym ' || object_name || ' for ' || object_name || ';', 'TRIGGER', 'alter trigger ' || object_name || ' compile;','*** ' || object_name || ' ' || object_type) from all_objects where status = 'INVALID';
8. Query to get text from terminal for procedure
   
   
   select text from all_source where name='procedure_name' order by line
9. Call procedure with out parameter in terminal
   
   
   DECLARE amount NUMBER;
   BEGIN
   PROC1(1000001, amount);
   dbms_output.put_line(amount);
   END;
   
10. Take spool (back up) of procedure
    
    
    spool on
    spool path of file.sql
    select text from all_source where NAME like upper(‘procedure name’);
    spool off
    
11. Example of anonymous block with for loop(It is used when there is dml stament in inner queries)
    
    
    declare
    BEGIN
    DBMS_OUTPUT.PUT_LINE('init..');
    FOR x IN (select query)
    LOOP
    DBMS_OUTPUT.PUT_LINE();
    dml query
    END LOOP;
    END;
    /
12. Example of anonymous block with cursor
    
    
    DECLARE CURSOR c1 IS select query1
    cursor c2 is select query1
    BEGIN
    OPEN c1;
    LOOP
    FETCH c1
    INTO column1,column2;
    EXIT WHEN c1%notfound;
    open c2;
    loop
    fetch c2 into column3,column4;
    EXIT WHEN c2%notfound;
    END LOOP;
    CLOSE c2;
    END LOOP;
    CLOSE c1;
    END;
    /
13. Command to get db existing in filesystem
    tnsping filesystemname
    
14. To get pga memory
    select * from v$SESSTAT where sid=464;
    V$Process
    
15. To get concurrent session user
    select a.sid, a.serial#, b.spid, a.username,to_char(a.logon_time,'DD-MM HH24:MI:SS') LogonDate, a.osuser, a.machine, a.status, a.module from v$session a, v$process b where a.paddr = b.addr and a.status = 'ACTIVE' and a.username is not null order by 9;
    
16. To get primary key from table
    SELECT cols.table_name, cols.column_name, cols.position, cons.status, cons.owner FROM all_constraints cons, all_cons_columns cols WHERE cols.table_name = 'table_name' AND cons.constraint_type = 'P' AND cons.constraint_name = cols.constraint_name AND cons.owner = cols.owner ORDER BY cols.table_name, cols.position;
    
17. To kill session
    ALTER SYSTEM KILL SESSION 'sid,serial#';
    
18. Command to load java or jar
    loadjava -u username/password@dburl:port:filesystem -f -t -v *.jar
    loadjava -u username/password@dburl:port:filesystem -f -t -v *.java
19. Query to check file permissions which are all executed from db
    select * from user_java_policy
    
20. To use & in pl sql
    if txt = 'test'|| chr(38) ||'123' --equal to test&123
    
21. Anonymous block to kill all existing inactive status session, where chr(39) is used for single quote
    
    declare
    t_command varchar2(200);
    t_sid_se varchar2(100);
    BEGIN
    DBMS_OUTPUT.PUT_LINE('init..');
    FOR x IN (select sid,serial# from v$session where status='INACTIVE')
    LOOP
    DBMS_OUTPUT.PUT_LINE('sid is '||x.sid||'serial'||x.serial#);
    t_sid_se := x.sid||','||x.serial#;
    DBMS_OUTPUT.PUT_LINE('t_sid_se is '||t_sid_se);
    t_command := 'alter system kill session '|| chr(39)||t_sid_se||chr(39);
    DBMS_OUTPUT.PUT_LINE('cmd is '||t_command);
    execute immediate t_command;
    END LOOP;
    END;
    /
    
    
22. Query to get only value which is in list and same value is not in table
    select column_value from table(sys.dbms_debug_vc2coll('Hello', 'Goodbye', 'Greetings', 'Dog')) minus select word from words;
    
23. Query to pass comma seperated values in where clause
    select regexp_substr('SMITH,ALLEN,WARD,JONES','[^,]+', 1, level) from dual connect by regexp_substr('SMITH,ALLEN,WARD,JONES', '[^,]+', 1, level) is not null
    
24. Function returns cursor example
    CREATE OR REPLACE PACKAGE package_name
    IS
    TYPE pk_cursor_name IS REF CURSOR;
    
    FUNCTION function_name
    (
    input parameter,
    cursname OUT pk_cursor_name
    ) RETURN NUMBER;
    END;
    
    
    
    CREATE OR REPLACE PACKAGE BODY package_name
    IS
    
    FUNCTION function_name(
    input parameter,
    cursname OUT pk_cursor_name)
    RETURN NUMBER
    is
    BEGIN
    
    BEGIN
    OPEN cursname FOR
    SELECT
    columns
    FROM
    table_name;
    
    EXCEPTION
    WHEN others THEN ora_raiserror(SQLCODE, 'Execution failed', 1843);
    END;
    
    
    RETURN 0;
    EXCEPTION
    WHEN others THEN ora_raiserror(SQLCODE, 'failed', 1843);
    END function_name;
    END package_name;