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! :)

How to compare schemas in Toad?

Open two connections like "HR" and "HRNEW" -> Database -> Compare -> Schemas -> Define Source and Target Schemas -> Select Object Types in "Object Types to Compare" -> Check and Set "Stop when # of differences reaches" option to more than "1000" like "10000" in "Misc Options" tab -> Click "Run" image -> Check script in "Sync Script" -> Lastly click "Move Script to Editor and Run Now" button -> That's all :)

How to export/import schema/table with Oracle Data Pump?

Define new user:
SQL> CREATE USER NEWHR
  IDENTIFIED BY newhr
  DEFAULT TABLESPACE USERS
  TEMPORARY TABLESPACE TEMP
  PROFILE DEFAULT
  ACCOUNT UNLOCK;

SQL>
GRANT CREATE SESSION TO NEWHR;
GRANT ALTER SESSION TO NEWHR;
GRANT CONNECT TO NEWHR;
GRANT RESOURCE TO NEWHR;
GRANT CREATE DATABASE LINK TO NEWHR;
GRANT CREATE SEQUENCE TO NEWHR;
GRANT CREATE SESSION TO NEWHR;
GRANT CREATE SYNONYM TO NEWHR;
GRANT CREATE VIEW TO NEWHR;
GRANT CREATE TYPE TO NEWHR;
GRANT CREATE TABLE TO NEWHR;
GRANT CREATE TRIGGER TO NEWHR;

Check current Oracle directories
SQL> select * from dba_directories;

Define new/replace new Oracle Directory
SQL> create or replace directory export_dir AS 'C:\Alper_ORACLE';

Give grants to user
SQL> grant read, write on directory export_dir to hr;

Export the schema
expdp hr/hr schemas=hr directory=export_dir dumpfile=hr.dmp logfile=hr.log

Export tables
expdp hr/hr tables=employees,employees_new directory=export_dir dumpfile=hr_table.dmp logfile=hr.log

Give grants to new user
SQL> grant read, write on directory export_dir to newhr;

Import schema to new machine
impdp newhr/newhr schemas=hr remap_schema=hr:newhr directory=export_dir dumpfile=hr.dmp logfile=hr.log

Import tables to new machine
impdp newhr/newhr tables=employees,employees_new remap_schema=hr:newhr directory=export_dir dumpfile=hr_table.dmp logfile=hr_table.log

If local storage is not enough to locate export file, use database link
e.g.
HR located in A machine 
NEWHR located in B machine
"XE" is A's machine DB link connection 

On B machine:
SQL> grant create database link to newhr;

SQL> create database link old_hr connect to hr identified by "HR" using 'XE';

To check db link;
SQL> select * from v$instance@XE;

impdp newhr/newhr DIRECTORY=export_dir NETWORK_LINK=old_hr remap_schema=hr:newhr logfile=newhr.log

What are database startup options?

SQL> STARTUP
--> starts normally

SQL> STARTUP nomount
--> starts as nomount mode

SQL> STARTUP restrict
--> start as restrict mode

SQL> ALTER SYSTEM DISABLE RESTRICTED SESSION
--> unlock restrict mode

SQL> STARTUP force
--> tries to ignore startup issues

SQL> STARTUP open recover
--> starts as recover mode

SQL> ALTER DATABASE OPEN READ ONLY
--> starts as read only mode

SQL> ALTER DATABASE OPEN READ WRITE
--> starts unrestricted mode

In extra:
SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION
--> enable restrict mode 

How to enable debug mode?

SQL> GRANT DEBUG CONNECT SESSION TO hr;
SQL> GRANT DEBUG ANY PROCEDURE TO hr;

Lastly, reconnect to the session and try again :)

PLS-00201 Solution

Error
PLS-00201: identifier 'TABLE_ALPER' must be declared

Solution
Define public synonym for identifier
e.g.
SQL>
CREATE PUBLIC SYNONYM TABLE_ALPER
FOR HR.TABLE_ALPER;