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;

How to resize maximum dump file size?

This parameter only controls size of log files except alert.log file.

Check max_dump_file_size
SQL> show parameter max_dump_file_size

Solid solution
add this into initora file;
*.max_dump_file_size=25M

Temporary solution
SQL> alter system set max_dump_file_size='25M' scope=both;

How to enable 'Diagnostic' and 'Tuning' diagnostic packs?

Check parameter
SQL> SHOW PARAMETER control_management_pack_access

Solid solution
add this into initora file;
*.control_management_pack_access='DIAGNOSTIC+TUNING'  

Revoke the operation temporary
SQL> ALTER SYSTEM SET control_management_pack_access=NONE;

How to merge text files in cmd?

Run this command in current path
CMD> 
for %f in (*.txt) do type "%f" >> merged.txt

Ref: How to run cmd from current path?

How to rollback committed data from flashback?

To Check
SQL>
SELECT *
  FROM HR.EMPLOYEES
       AS OF TIMESTAMP TO_TIMESTAMP ('2016-12-19 21:35:00', 'YYYY-MM-DD HH24:MI:SS')

To Save Data As Temp Table
SQL>
CREATE TABLE HR.EMPLOYEES_TEMP AS
SELECT *
  FROM HR.EMPLOYEES
       AS OF TIMESTAMP TO_TIMESTAMP ('2016-12-19 21:35:00', 'YYYY-MM-DD HH24:MI:SS')
-- WHERE EMPLOYEE_ID = 100

How to see file extensions in current directory?

$ find . -type f | perl -ne 'print $1 if m/\.([^.\/]+)$/' | sort -u

bash: /bin/rm: Argument list too long error Solution

Command
rm -f *

Error 
bash: /bin/rm: Argument list too long error

Solution
Use following command line
$ find . -maxdepth 1 -name "*.txt" -print0 | xargs -0 rm

How to remove shutdown, restart, sleep and hibernate options from start menu?

Run gpedit.msc -> User Configuration -> Administrative Templates -> Startup Menu and Taskbar -> Edit policy setting to Enable "Remove and prevent access to the Shut Down, Restart, Sleep, and Hibernate commands" -> Done! :)

How to disable sleep option?

Run gpedit.msc -> Computer Configuration -> Administrative Templates -> System -> Power Management -> Sleep Settings -> Edit policy setting to Enable "Turn Off Hybrid Sleep (Plugged In) and (On Battery)" ->  Edit policy setting to Disable "Allow Standby States (S1-S3) when sleeping (Plugged In) and (On Battery)" -> Done! :)