How to manage logon mechanism with trigger ?

CREATE OR REPLACE TRIGGER SYS.DENY_LOGIN

   AFTER LOGON

   ON DATABASE

BEGIN

   IF     TO_CHAR (SYSDATE, 'hh24') BETWEEN 08 AND 22

      AND SYS_CONTEXT ('USERENV', 'SESSION_USER') = 'HR'

   THEN

      raise_application_error (

         -20001,

         'You cannot login between 08 AM and 06 PM. Please contact with Alper!');

   END IF;

END deny_login;

/

Error message:

ORA-00604: error occurred at recursive SQL level 1

ORA-20001: You cannot login between 08 AM and 06 PM. Please contact with Alper!

ORA-06512: at line 5

LISTAGG examples

SQL>

select LISTAGG (last_name, ', ')  WITHIN GROUP (order by last_name)

from employees

SQL>

select LISTAGG (first_name || ' ' || last_name, ', ') WITHIN GROUP (order by first_name)

from employees

SQL>

select LISTAGG (first_name || ' ' || salary, ', ') WITHIN GROUP (order by salary desc)

from employees

SQL>

select LISTAGG (first_name || ' ' || salary || ' ' || NVL2(commission_pct, commission_pct, '0'), ', ') WITHIN GROUP (order by salary desc)

from employees

How to define "idle_time" ?

Check parameter of resource_limit
SQL> show parameter resource;

If it is 'False'
SQL> alter system set resource_limit = true;

Add it into init.ora file permanently
*.resource_limit=true;

Modify default profile
SQL> alter profile default limit idle_time 1440;
-- 1440 -> 24 hour

Check default profile setting
SQL> select profile, resource_name, limit from dba_profiles 
where lower(profile) = 'default' and resource_name = 'IDLE_TIME';

Monitor idle sessions
SQL> select * from v$session where status = 'SNIPED' order by logon_time desc;

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
Teletypewriter - TTY
pseudo terminal slave - PTS

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
-> REMOVE *.DB_RECOVERY_FILE_DEST from pfile if you desire different path

SQL> select * from V$ARCHIVE_DEST;

Define default log archive destination:
*.log_archive_dest='C:\oraclexe\app\oracle\oradata\XE\LOG_ARCH\'

Define new paths: 
*.log_archive_dest_1='LOCATION=C:\oraclexe\app\oracle\oradata\XE\LOG_ARCH_1\'
*.log_archive_dest_2='LOCATION=C:\oraclexe\app\oracle\oradata\XE\LOG_ARCH_2\'

Define duplex destination if needs
*.log_archive_duplex_dest='LOCATION=C:\oraclexe\app\oracle\oradata\XE\LOG_ARCH_DUPLEX\'

Differences between regular dest and duplex dest:
LOG_ARCHIVE_DEST calls as primary archive destination,
LOG_ARCHIVE_DUPLEX_DEST calls as secondary archive destination
The important point is that these are using only on local machine

-> On the other hand, LOG_ARCHIVE_DEST_n may use in remote and local as well.

Test the path:
SQL> alter system switch logfile;
Check archive log file in local path like "C:\oraclexe\app\oracle\oradata\XE\LOG_ARCH\"

How to use logminer?

Determine the archive log path:
SQL> archive log list;

If "Archive destination" is "DB_RECOVERY_FILE_DEST":
SQL> show parameter DB_RECOVERY_FILE_DEST;

Select the archive log file to use for logminer 
such as "C:\oraclexe\app\oracle\oradata\XE\LOG_ARCH_1\LOG1_28_934210760.ARC"

e.g.
delete from employees where employee_id = 206

Run following codes as SYS user:
BEGIN
   DBMS_LOGMNR.add_logfile (
      logfilename   => 'C:\oraclexe\app\oracle\oradata\XE\LOG_ARCH_1\LOG1_28_934210760.ARC', options => DBMS_LOGMNR.new);
END;
/
EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG)
/

Run these codes for examining the logs in example table (as SYS):
SQL>
CREATE TABLE HR.ex934210760 AS SELECT * FROM v$logmnr_contents;
SQL> 
GRANT SELECT ON HR.ex934210760 TO PUBLIC;

To find DML (delete) SQL in example table:
SQL>
select * from HR.ex934210760 where table_name='EMPLOYEES'

GRANT/REVOKE examples

check own privileges:
SQL> SELECT * FROM SESSION_PRIVS;

syntax of system privileges:
GRANT [system_privileges | roles] {ANY} <object_type>
TO [user | role | PUBLIC] {WITH GRANT OPTION}

syntax of object privileges:
GRANT [ALL {object_privileges}] ON object 
TO [user | role | PUBLIC] {WITH GRANT OPTION}  

system privileges options:
ALTER, CREATE, DROP, EXECUTE, SELECT

object privileges options:
SELECT, INSERT, UPDATE, DELETE, REFERENCES, ALTER, INDEX, ALL

object types:
CONTEXT, DATABASE LINK, DICTIONARY, INDEX, MATERIALIZED VIEW, PACKAGES, PROCEDURE, PROFILE, ROLE, SESSION, SEQUENCE, SYNONYM, USERS, VIEW, TYPE, TABLE, TABLESPACE, TRIGGER

some of system roles:
CONNECT, RESOURCE, DBA

examples of system privileges:
e.g.
SQL> grant dba to newhr

e.g.
SQL> grant create type to hrnew;

e.g. using ANY syntax
SQL> grant create any table to hrnew;
-> hrnew user is able to create table for any schema/user.

e.g. revoke ANY grant
SQL> revoke create any table from hrnew;

e.g. grant for refreshing materialized view
SQL> grant alter any materialized view to hrnew; 
-> hrnew is able to refresh all materialized views on whole database schemas

examples of object privileges:
e.g.
SQL> grant select on hr.employees to hrnew;

e.g. 
SQL> grant select, update, delete on hr.employees to hrnew;

e.g. 
SQL> grant all on hr.employees to hrnew;

e.g.
SQL> grant all on hr.employees to public;

e.g.
SQL> revoke select on hr.employees from hrnew;

e.g.
SQL> revoke select, update, delete on hr.employees from hrnew;

e.g.
SQL> revoke all on hr.employees from public;

e.g. for "references" option
SQL> conn hr/hr
Connected.
SQL> grant references on employees to hrnew;
Grant succeeded.
SQL> conn hrnew/hrnew
Connected.
SQL> alter table newemployees add foreign key (employee_id) references employees(employee_id);
Table altered.

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 DISTINCT
                    'ALTER SYSTEM KILL SESSION '''
                 || b.sid
                 || ','
                 || b.serial#
                 || ''''
                    AS KILL_THEM_ALL
            FROM sys.dba_ddl_locks a, sys.v_$session B
           WHERE     B.SID = a.session_id
                 AND owner NOT IN ('SYS',
                                   'WMSYS',
                                   'CTXSYS',
                                   'DBSNMP')
                 AND B.TYPE <> 'BACKGROUND'
                 AND B.status = 'INACTIVE')
   LOOP
      BEGIN
         str_exec := cur_session.KILL_THEM_ALL;
         EXECUTE IMMEDIATE str_exec;
      END;
   END LOOP;
END;
/

Usage:
BEGIN
P_KILL_ALL_INACTIVE_SESSIONS;
END;
/

Ref: https://ozsoyler.blogspot.com/2014/10/how-to-kill-session-with-custom.html

How to use "EXECUTE IMMEDIATE' ?

e.g.
DECLARE
   v_name      VARCHAR (100) := &i1;
   v_surname   VARCHAR (100) := &i2;
BEGIN
   EXECUTE IMMEDIATE 'insert into EMPLOYEES_A  values (:input, :input2)'
      USING v_name, v_surname;
END;

watch examples

$ watch free -m
-> Display free command output in every two seconds 

$ watch -n 5 free -m
-> Display free command output in every five seconds  (with -n parameter)

Dynamic sql script for synonyms

Define new synonyms:
SQL>
select 'create public synonym ' || synonym_name || ' for ' || table_owner || '.' || table_name || ';' from all_synonyms where owner like '%PUBLIC%' and table_owner like '%ALPER%';

SQL>
select 'create public synonym ' || synonym_name || ' for ' || table_owner || '.' || table_name || ';' from all_synonyms where owner like '%ALPER2%' and table_owner like '%ALPER%'

Drop synonyms:
SQL>
select 'drop public synonym ' || synonym_name || ';' from all_synonyms where owner like '%PUBLIC%' and table_owner like '%ALPER%';

screen commands

screen	->	start the tool
ctrl + a	->	control screen
ctrl + a + c	->	define new screen
ctrl + a + n	->	switch between screen
ctrl + a + d	->	detach screen
ctrl + a + n	->	switch between screen
screen -r	->	reattach screen
screen -r [xyz]	->	reattach specific screen
screen -ls	->	list the screens
ctrl + a + x	->	lock screen
ctrl + a + k	->	kill screen

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

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_pages=only

Then restart database;
SQL>
shutdown immediate;
startup;

How to define/use Oracle domain index?

What is domain index?:
Oracle says that domain indexes reformed beyond to the basic types of hash, bitmapped, and B-tree indexes that developers define their own specified indexes to gain more speed on applications such as video based data. 

Define domain index:
BEGIN
CTXSYS.CTX_DDL.DROP_PREFERENCE('EMP_DATASTORE');
CTXSYS.CTX_DDL.DROP_PREFERENCE('EMP_LEXER');
CTXSYS.CTX_DDL.DROP_PREFERENCE('EMP_WORLDLIST');
END;
/
DROP INDEX SYS.IX_EMP_FULL
/
ALTER TABLE HR.EMPLOYEES DROP COLUMN SEARCH_TEXT
/
BEGIN 
CTX_DDL.CREATE_PREFERENCE('EMP_DATASTORE','MULTI_COLUMN_DATASTORE');
CTX_DDL.SET_ATTRIBUTE('EMP_DATASTORE','COLUMNS','EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE');
CTX_DDL.CREATE_PREFERENCE('EMP_LEXER','BASIC_LEXER');
CTX_DDL.SET_ATTRIBUTE('EMP_LEXER','BASE_LETTER','YES');
CTX_DDL.CREATE_PREFERENCE('EMP_WORLDLIST','BASIC_WORDLIST');
CTX_DDL.SET_ATTRIBUTE('EMP_WORLDLIST','SUBSTRING_INDEX','TRUE');
END;
/
ALTER TABLE HR.EMPLOYEES ADD SEARCH_TEXT VARCHAR(1)
/
CREATE INDEX SYS.IX_EMP_FULL ON HR.EMPLOYEES (SEARCH_TEXT) INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS('datastore EMP_DATASTORE lexer EMP_LEXER wordlist EMP_WORLDLIST sync(on commit)')
/

Optional:
Remove a word from stopwords:
SQL>
BEGIN
CTXSYS.CTX_DDL.REMOVE_STOPWORD('CTXSYS.DEFAULT_STOPLIST', 'can', NULL);
END;
/

Add a word to stopwords:
SQL>
BEGIN
CTXSYS.CTX_DDL.ADD_STOPWORD('CTXSYS.DEFAULT_STOPLIST', 'alper', NULL);
END;
/

Check it;
SQL>
SELECT * FROM CTX_STOPWORDS WHERE spw_word like '%alper%';

Note that after this kind of operation, related domain index should be dropped and redefined again.

Usage:
Prepare test data:
Insert into HR.EMPLOYEES
   (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, 
    HIRE_DATE, JOB_ID, SALARY, MANAGER_ID, DEPARTMENT_ID)
 Values
   (1000, 'Alper', 'Ozsoyler', 'ozsoyler.blogspot.com', '1111', 
    TO_DATE('10/10/2010 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'IT_PROG', 10000, 100, 90);
COMMIT;

Test it:
SQL>
SELECT *
  FROM EMPLOYEES
 WHERE CONTAINS (SEARCH_TEXT, 'ALPER') > 0
       AND CONTAINS (SEARCH_TEXT, '10/10/2010') > 0

Extra:
If you deal an update on employees.first_name column, the domain index does not synchronize because when only SEARCH_TEXT column updates, EMP_DATASTORE will be updated with new data. There will not be any problem when you issue insert or delete operations.

e.g.
SQL>
UPDATE HR.EMPLOYEES
   SET last_name = 'OZ'
 WHERE employee_id = 1000;
COMMIT;

SQL>
SELECT *
  FROM EMPLOYEES
 WHERE     CONTAINS (SEARCH_TEXT, 'OZ') > 0
       AND CONTAINS (SEARCH_TEXT, '10/10/2010') > 0;
-- Result will be empty

-- To solve it;
SQL>
UPDATE HR.EMPLOYEES
   SET search_text = '1'
 WHERE employee_id = 1000;
COMMIT;

-- or
-- as SYSDBA
SQL> 
SELECT IDX_ID FROM CTXSYS.CTX_INDEXES WHERE IDX_NAME='IX_EMP_FULL'; 
--e.g. 1063

SQL> 
INSERT INTO CTXSYS.DR$PENDING (PND_CID,PND_PID,PND_ROWID,PND_TIMESTAMP)
SELECT 1063, 0, ROWID, CURRENT_DATE
FROM hr.employees
WHERE employee_id = 1000;
COMMIT;

SQL>
CALL CTX_DDL.SYNC_INDEX('IX_EMP_FULL');

-- Check index;
SQL>
SELECT *
  FROM HR.EMPLOYEES
 WHERE     CONTAINS (SEARCH_TEXT, 'OZ') > 0
       AND CONTAINS (SEARCH_TEXT, '10/10/2010') > 0;

Attention: Lastly, do not forget recompile all objects with UTLRP.
SQL> EXEC UTL_RECOMP.recomp_serial();

Ref: https://stackoverflow.com/questions/1916244/how-to-sync-and-optimize-an-oracle-text-index (check BernardMarx comment)
http://www.dba-oracle.com/t_domain_index.htm

How to list/define/delete shared folder in Windows?

Check shared folder
CMD> net share

Share a folder
CMD> net share Alperinko$=D:\Oracle /grant:everyone,FULL /users:25
-> All users can access to the folder in the domain
-> Access limit is 25 users

CMD> net share MyOracleFolder=D:\Oracle /grant:alper,READ
-> Only alper can access to the folder

Delete shared folder
CMD> net share Alperinko$ /delete
CMD> net share MyOracleFolder /delete

How to configure login options in Oracle?

Apply the option permanently
sec_case_sensitive_logon=FALSE
or
sec_case_sensitive_logon=TRUE

Check the parameter
SQL>
show parameter sec_case_sensitive_logon

Note that default value is "TRUE"

How to start/stop/configure vnc service?

install tool:
yum install tigervnc-server

configure settings:
vi /etc/sysconfig/vncservers

e.g.
VNCSERVERS="2:root 3:oracle 4:alper"
VNCSERVERARGS[2]="-geometry 1280x1024 -nolisten tcp -localhost"
VNCSERVERARGS[3]="-geometry 1280x1024"
VNCSERVERARGS[4]="-geometry 1280x1024"

change password of vnc
$ whoami
-> alper
$ vncpasswd
-> alper123

startup options:
# chkconfig vncserver on
# service vncserver start

login to system with windows vncviewer tool
e.g. 10.10.10.10.:4

remove startup option and stop vnc:
# chkconfig vncserver off
# service vncserver stop

Info: Virtual Network Computing (VNC) is a graphical desktop sharing system

output columns of top command

Columns:
PID     -> Process ID   
USER    -> Process owner   
PR      -> Priority of process 
NI      -> Nice value of process  
VIRT    -> Virtual memory using by process
RES     -> Physical memory using by process  
SHR     -> Shared memory of process
S       -> Status of process (R-> Running, S-> Sleeep, Z-> Zombie, D-> Uninterruptible Sleep, T-> Traced or Stopped)
%CPU    -> CPU usage of process (as percent)
%MEM    -> RAM usage of process (as percent)
TIME+   -> Total time activity for process 
COMMAND -> indicates of process name

Info:
PR = 20 + NI (PR-RT -> Real Time system process)
PR -> (0 is the highest priority and 39 is the lowest priority)
NI; Nice value (−20 is the highest priority and 19 is the lowest priority)
VIRT; Virtual image (kb) -> disk storage + RAM
RES;  Resident size (kb) -> RAM (non-swapped physical memory)

SHR; Shared mem size (kb)-> RAM (shared memory)

Extras:

Set priority of a process before start;
nice -n 19 tar cvzf archive.tgz largefile

Change priority of a process;
renice -n -20 -p process_id

Ref:

https://ozsoyler.blogspot.com/2017/01/how-to-see-load-averages-on-linux.html

https://ozsoyler.blogspot.com/2014/10/how-to-see-process-that-used-memory-or.html

ORA-29885 solution

Error
ORA-29885: domain index is defined on the column to be modified

Solution
Find problematic index;
SQL>
SELECT *
  FROM dba_indexes
 WHERE table_name = 'EMPLOYEES' AND index_type = 'DOMAIN'

Drop the index and try again;
SQL> 
DROP INDEX HR.IDX_EMPLOYEE_NAME;