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;

How to calculate IP range?

e.g. 
Address:    10.10.10.0/24
            00001010 00001010 00001010 00000000

Netmask:    255.255.255.0
            11111111 11111111 11111111 00000000
             -8-      -8-      -8-      -0-   = 24

Wildcard:   0.0.0.255
            00000000 00000000 00000000 11111111
                                         -8-
Broadcast:  10.10.10.255
            00001010 00001010 00001010 11111111
                                         -8-

Host First IP: 10.10.10.1
Host Last IP:  10.10.10.254
Broadcast IP:  10.10.10.255
Network ID IP: 10.10.10.0

An example of PL/SQL collection for nested table

DECLARE
   TYPE employee IS TABLE OF VARCHAR (5);
   employees   employee:= employee ('A', 'B', 'C', 'D','E');
BEGIN
   FOR i IN employees.FIRST .. employees.LAST
   LOOP
      IF employees (i) = 'C'
      THEN
         DBMS_OUTPUT.PUT_LINE (employees (i));
      END IF;
   END LOOP;
END;

How to change table read-write mode?

Set read only mode:
SQL>
ALTER TABLE HR.EMPLOYEES READ ONLY;

Set read/write mode:
SQL>
ALTER TABLE HR.EMPLOYEES READ WRITE;

Check table status:
SQL>
SELECT OWNER, TABLE_NAME, READ_ONLY FROM DBA_TABLES WHERE TABLE_NAME = 'EMPLOYEES';

How to see load averages on linux?

$ top
or 
$ uptime

e.g.
load average: 0.35. 0.23, 0.25
First column; last one minute
Second column; last five minutes
Third column;  last fifteen minutes averages for each cpu

How to kill inactive session for non-background oracle processes?

At first, check the sessions
SQL>
SELECT DISTINCT spid, b.username, osuser
    FROM sys.dba_ddl_locks a, sys.v_$session B, v$process c
   WHERE     B.SID = a.session_id
         AND owner NOT IN ('SYS',
                           'WMSYS',
                           'CTXSYS',
                           'DBSNMP')
       AND B.TYPE <> 'BACKGROUND'
       AND B.status = 'INACTIVE'
         AND (c.addr = b.paddr)
ORDER BY username, osuser;

Kill the session
SQL>
SELECT DISTINCT
          'ALTER SYSTEM KILL SESSION '''
       || b.sid
       || ','
       || b.serial#
       || ''' IMMEDIATE;' 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'

How to learn linux distribution?

See all info
$ lsb_release -a
a - all

See only distro info
$ lsb_release -i -r
i - id, r - release

How to define timeout setting for auto logout users in linux system?

Configure it
# vi /etc/profile
add following parameter at the end of the profile file:
# this parameter runs as seconds and affects all users. e.g. 3600; one hour
TMOUT=3600

Check it
$ echo $TMOUT

Test result
$ timed out waiting for input: auto-logout

How to configure display format of sqlplus ?

e.g.
set linesize 200
set pagesize 100
column object_name format a30
column object_type format a106 -- optional
column owner format a30 -- optional

select object_name, object_type, owner from dba_objects where status = 'INVALID' order by owner, object_type, object_name;

Note that "30" is limit of object_name and owner size. In addition,
"106" is limit of object_type size.

linesize; line limit of each row
pagesize; total row limit of display

ORA-02020 Solution

Error
ORA-02020 – Too many database links in use 

Solution
Current setting:
SQL> show parameter open_links;
-- default is "4"

Change setting permanently:
add this into init.ora file;
*.open_links=10
*.open_links_per_instance=10

Then restart the instance to perform.

Alternative solution
Kill the hanged database link:
At first, find the database link
SQL> SELECT db_link FROM v$dblink;

Then kill it
SQL> alter session close database link ALPERDB.WORLD;

How to import/export ZOC profile, options and all settings?

e.g.
Export/Import
Copy "My Documents/ZOC7 Files" folder then import this folder to desired new path

How to define redo log files?

e.g.
check current redo log file
select member from v$logfile; --redo log files

add log group and its members
SQL>
ALTER DATABASE 
  ADD LOGFILE GROUP 1 ('/oradata/ALPERDB/redo_g1_m00.log', '/oradata/ALPERDB/redo_g1_m01.log', '/oradata/ALPERDB/redo_g1_m02.log', '/oradata/ALPERDB/redo_g1_m03.log')
      SIZE 56M;

SQL>
ALTER DATABASE 
  ADD LOGFILE GROUP 2 ('/oradata/ALPERDB/redo_g2_m00.log', '/oradata/ALPERDB/redo_g2_m01.log', '/oradata/ALPERDB/redo_g2_m02.log', '/oradata/ALPERDB/redo_g2_m03.log')
      SIZE 56M;

SQL>
ALTER DATABASE 
  ADD LOGFILE GROUP 3 ('/oradata/ALPERDB/redo_g3_m00.log', '/oradata/ALPERDB/redo_g3_m01.log', '/oradata/ALPERDB/redo_g3_m02.log', '/oradata/ALPERDB/redo_g3_m03.log')
      SIZE 56M;

SQL>
ALTER DATABASE
  ADD LOGFILE GROUP 4 ('/oradata/ALPERDB/redo_g4_m00.log', '/oradata/ALPERDB/redo_g4_m01.log', '/oradata/ALPERDB/redo_g4_m02.log', '/oradata/ALPERDB/redo_g4_m03.log')
      SIZE 56M;

switch log file for activing above log files

SQL> ALTER SYSTEM SWITCH LOGFILE;

check physical_block_size and logical_block_size if needed
$ cd /sys/block/sda/queue
$ cat physical_block_size
$ cat logical_block_size

drop logfile group if necessary

SQL> ALTER DATABASE DROP LOGFILE GROUP 4;