Friday, 28 April 2017

ORA-06553: PLS-907 Solution

›
Error ORA-06553: PLS-907: cannot load library unit aaa (object)  (referenced by bbb) Solution Compile related object (bbb) SQL> AL...
Thursday, 27 April 2017

ORA-00059 solution

›
Error ORA-00059: maximum number of DB_FILES exceeded Solution Check limit that total number of db files SQL> show parameter db_file...
Friday, 21 April 2017

How to restore database object such as package body via flashback?

›
Note that below SQL gets output if it is still in UNDO tablespace. SQL>  SELECT text  FROM all_source         AS OF TIMESTAMP TO_TIME...
Wednesday, 12 April 2017

How to see TOP 10 SQL that consuming system resources highly?

›
e.g. select * from ( select * from v$sql order by  DISK_READS  desc ) where rownum < 11 options for order by: RUNTIME_MEM, EXEC...
Tuesday, 11 April 2017

How to find and investigate a process in top command output?

›
Total connection by machine: SQL> select machine, count(*) from v$session group by machine order by 2; Total number of database user ...

How to get value from xml node?

›
e.g. xml file: <A> <B value="Alper XML"> <C value="Alper XML 2"> </C> </B> </A...
Tuesday, 21 March 2017

How to manage logon mechanism with trigger ?

›
CREATE OR REPLACE TRIGGER SYS .DENY_LOGIN     AFTER LOGON     ON DATABASE BEGIN    IF     TO_CHAR (SYSDATE, 'hh24') BETW...

LISTAGG examples

›
SQL> select LISTAGG (last_name, ', ')   WITHIN GROUP (order by last_name) from employees SQL> select LISTAGG (fir...
Thursday, 9 March 2017

How to define "idle_time" ?

›
Check parameter of resource_limit SQL> show parameter resource; If it is 'False' SQL> alter system set resource_limit = t...

How to kill tty/pts terminal?

›
Check first your connection to prevent killing yourself $ tty Kill other connection $ skill -9 pts/1 or # skill -KILL pts/1 Info T...
Friday, 24 February 2017

How to manage archive logs?

›
Check status of archivelog mode: SQL> archive log list; Open if it is closed: Link SQL> SHOW PARAMETER DB_RECOVERY_FILE_DEST -...
Tuesday, 21 February 2017

How to use logminer?

›
Determine the archive log path: SQL> archive log list; If "Archive destination" is "DB_RECOVERY_FILE_DEST": SQL...

GRANT/REVOKE examples

›
check own  privileges: SQL>   SELECT * FROM SESSION_PRIVS; syntax of  system privileges: GRANT [ system_privileges | roles] {ANY} ...
Monday, 20 February 2017

How to kill all inactive sessions with custom procedure?

›
CREATE OR REPLACE PROCEDURE P_KILL_ALL_INACTIVE_SESSIONS IS    str_exec   VARCHAR2 (10000); BEGIN    FOR cur_session       IN (SELECT ...
Tuesday, 7 February 2017

How to use "EXECUTE IMMEDIATE' ?

›
e.g. DECLARE    v_name      VARCHAR (100) := &i1;    v_surname   VARCHAR (100) := &i2; BEGIN     EXECUTE IMMEDIATE 'inse...
Saturday, 4 February 2017

watch examples

›
$ watch free -m -> Display free command output in every two seconds  $ watch -n 5 free -m -> Display free command output in every...
Friday, 3 February 2017

Dynamic sql script for synonyms

›
Define new synonyms: SQL> select 'create public synonym ' || synonym_name || ' for ' || table_owner || '.' || ...
Tuesday, 31 January 2017

screen commands

›
screen -> start the tool ctrl + a -> control screen ctrl + a + c -> define new screen ctrl + a + n -> s...

How to remove datafile carefully?

›
find datafile path; SQL>  select file_name from dba_data_files; before run rm; $ cd /oradata $ lsof * $ rm -f alper_data_01.dbf
Monday, 30 January 2017

ORA-27137 Solution

›
Error ORA-27137: unable to allocate large pages to create a shared memory segment Solution Comment out this in pfile; ###*.use_large_p...