How to Clone Database Manually without any tool such as RMAN?

Collect live database paths before start
$ ps -ef | grep pmon
$ echo $ORACLE_SID
$ echo $ORACLE_BASE
$ echo $ORACLE_HOME
$ . oraenv

SQL> select name from v$datafile; --db files
SQL> select member from v$logfile; --redo log files
SQL> select name from v$controlfile; --control files
SQL> select name from v$tempfile; --temp files

Copy parameter file (pfile) from live database
SQL> create pfile='/tmp/Alper-init.ora' from spfile;
or
* Copy livedb pfile under $ORACLE_HOME/dbs directory as desired file name like 'Alper-init.ora'

Edit pfile
* Change related parameter such as db_name and control files path (Alper-init.ora)

Copy control file from live database
SQL> alter database backup controlfile to trace as '/tmp/Alper-ctl';
or
SQL> alter database backup controlfile to trace; 
-- Run SQL> show parameter user_dump_dest; to find it

Edit control file
* Prepare a new script with this script
SQL> CREATE CONTROLFILE REUSE SET DATABASE "ALPERDB" RESETLOGS  NOARCHIVELOG
-- Use this if you want to re-use livedb controlfiles in new clone db (if you already moved the controlfiles to new clone db)
or
SQL> CREATE CONTROLFILE SET DATABASE "ALPERDB" RESETLOGS  NOARCHIVELOG
-- Use this if you want to use fresh control file in new clone db
-- SQL> show parameter control; -- to find control file

Copy all database files to remote machine
$ scp -r /oradata/ALPERDB/ oracle@10.10.10.10:/oradata/NEW_ALPERDB

Open database
SQL> alter database open resetlogs;

Define temp tablespace for new database
SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/oradata/NEW_ALPERDB/temp_01.dbf'
     SIZE 10240M REUSE AUTOEXTEND OFF;
For detail information please click this

That's all :)

How to configure listener port when it changes (like 1522) ?

After listener.ora and tnsnames.ora file changes, only run this command
SQL> ALTER SYSTEM SET LOCAL_LISTENER='(ADDRESS = (PROTOCOL=TCP)(HOST=10.10.10.10)(PORT=1522))' SCOPE=BOTH;

Check current setting
SQL> SHOW PARAMETER local_listener;

Check listener
lsnrctl status

That's all.

How to configure instant client for Toad?

-> unrar .zip file 
e.g. C:\oracle_client\12.1.0\client_1
-> add this to "Environment Variables->Path"

That's all :)

How see latest sql histories from audit view?

Check audit paramater
SQL> show parameter audit;
-- basic options; 'DB','DB,EXTENDED', 'OS', 'NONE'

Enable the paramater permanently
add this into initora file;
*.audit_trail='DB'

Flush the aud table
TRUNCATE TABLE sys.aud$

Backup related table
CREATE TABLE ALPER_AUD
AS
   SELECT * FROM dba_audit_trail

Examine the sessions
SELECT * FROM ALPER_AUD

How to define snapshot and synonym?

Define source table at DB1 machine
CREATE TABLE t_alper_source
AS
   SELECT * FROM employees;

ALTER TABLE t_alper_source ADD CONSTRAINT pk_alper_source PRIMARY KEY (employee_id);

CREATE SNAPSHOT LOG ON t_alper_source;

Define snapshot at DB2 machine
CREATE SNAPSHOT "SS_ALPER_SOURCE"
   TABLESPACE "USERS"
   BUILD IMMEDIATE
   USING INDEX TABLESPACE "USER_INDEX"
   REFRESH FAST
AS
   SELECT * FROM T_ALPER_SOURCE@DB1;

-- There should be two objects that are "Table" and "Materialized View" after executes.

Define synonym at DB2 machine
CREATE PUBLIC SYNONYM ALPER_TABLE FOR SS_ALPER_SOURCE;

Dynamic SQL for snapshots
SQL>
select 'create snapshot '||owner||'.ss_new_employees as select * from '||owner||'.new_employees@'||owner||'db;
create synonym '||owner||'.new_employees for '||owner||'.ss_new_employees;' from dba_snapshots where name='SS_EMPLOYEES';

What are the snapshot (Materialized Views) refresh methods?

begin
DBMS_SNAPSHOT.REFRESH( 'v_alper_ss','f'); 
end;
/

f -> fast refresh
? -> force refresh
c -> complete refresh
a -> always refresh

How to define/check/drop jobs?

Define a new job (will run every hour)
DECLARE
   X   NUMBER;
BEGIN
   BEGIN
      SYS.DBMS_JOB.SUBMIT (
         job         => X,
         what        => 'HR.P_COMPILE_INVALID_OBJECTS;',
         next_date   => TO_DATE ('24.10.2016 10:00:00',
                                 'dd/mm/yyyy hh24:mi:ss'),
         interval    => '(SYSDATE)+1/24',
         no_parse    => FALSE);
      SYS.DBMS_OUTPUT.PUT_LINE ('Job Number is: ' || TO_CHAR (x));
   EXCEPTION
      WHEN OTHERS
      THEN
         RAISE;
   END;

   COMMIT;
END;

Check job parameter
SQL> select * from v$parameter where name like '%job%';

if value is "0":
SQL> alter system set job_queue_processes=20;

Add this setting into init.ora file:
*.job_queue_processes=20

Info: If job_queue_processes parameter value is bigger than "1", jobs runs automatically such as refresh materialized view job. On the other hand, user can run this job manually as well while job_queue_processes="0".

Check current job status 
SQL> select * from user_jobs;
or
SQL> select * from dba_jobs;

Check running jobs 
SQL> select * from dba_jobs_running;
-- take a note that sid output (e.g. 2283)

Get detail info about running job
SQL> SELECT s.username, osuser, process machine, p.terminal, type, sid, s.serial#, s.program
FROM v$session s, v$process p
WHERE s.sid = 2283
AND s.paddr = p.addr;
-- s.program; CQJ0 (Job queue controller), J000, J001 ... J999 (Job Queue) 
-- These are called as background processes

Drop the job with sys or system user (as sysdba)
-- Before do this, make sure related job is broken, no enq. tx row lock session on SYS.JOB$ table that killed.

SQL>
SELECT DISTINCT
          'ALTER SYSTEM KILL SESSION '''
       || b.sid
       || ','
       || b.serial#
       || ''' IMMEDIATE;'
          AS KILL_THEM_ALL
  FROM sys.dba_dml_locks a, sys.v_$session B
 WHERE     B.SID = a.session_id
       AND owner NOT IN ('WMSYS',
                         'CTXSYS',
                         'DBSNMP')
       AND B.TYPE <> 'BACKGROUND'
       AND name = 'JOB$';

-- Run that 
SQL>
begin
sys.dbms_job.broken(1111,true); -- 1111 -> the job id
commit;
end;

SQL>

SELECT DISTINCT
          'ALTER SYSTEM KILL SESSION '''
       || b.sid
       || ','
       || b.serial#
       || ''' IMMEDIATE;'
          AS KILL_THEM_ALL
  FROM sys.v_$session B
 WHERE b.sid = 2283;

SQL>
begin
sys.dbms_job.remove(1111); -- 1111 -> the job id
commit;
end;

Dynamic SQL for dropping jobs with sys or system user (as sysdba)
-- Before do this, make sure related job is broken and session is killed.
-- Run that 
SQL>
SELECT 'begin sys.dbms_ijob.broken(' || job || ', true); commit; end;' || chr(10) || '/' AS DROP_JOBS 
FROM dba_jobs;

SQL>

SELECT DISTINCT

          'ALTER SYSTEM KILL SESSION '''

       || b.sid

       || ','

       || b.serial#

       || ''' IMMEDIATE;'

          AS KILL_THEM_ALL

  FROM sys.v_$session B, dba_jobs_running A

 WHERE a.sid = b.sid;

SQL>

SELECT 'begin sys.dbms_ijob.remove(' || job || '); commit; end;' || chr(10) || '/' AS DROP_JOBS 
FROM dba_jobs;
-- ijob uses for all jobs in dbms_scheduler (new version of dbms_jobs)

Info: Please click this for P_COMPILE_INVALID_OBJECTS 

How to compile invalid objects with custom procedure?

CREATE OR REPLACE PROCEDURE P_COMPILE_INVALID_OBJECTS
   AUTHID CURRENT_USER
IS
   str_exec    VARCHAR2 (200);
   total_inv   NUMBER;
   err_code    VARCHAR2 (200);
   err_msg     VARCHAR2 (200);
BEGIN
   /*Author: Alper Özsöyler */

   FOR cur_object IN (  SELECT owner, object_name, object_type
                          FROM dba_objects
                         WHERE status = 'INVALID'
                      ORDER BY owner)
   LOOP
      str_exec := 'ALTER ';

      IF cur_object.object_type = 'PACKAGE BODY'
      THEN
         str_exec := str_exec || 'PACKAGE ';
      ELSE
         str_exec := str_exec || cur_object.object_type || ' ';
      END IF;

      str_exec :=
            str_exec
         || cur_object.owner
         || '.'
         || cur_object.object_name
         || ' COMPILE ';


      IF cur_object.object_type = 'PACKAGE BODY'
      THEN
         str_exec := str_exec || 'BODY ';
      END IF;

      BEGIN
         EXECUTE IMMEDIATE str_exec;
      EXCEPTION
         WHEN OTHERS
         THEN
            DBMS_OUTPUT.put_line (
                  cur_object.owner
               || '.'
               || cur_object.object_name
               || ' ('
               || cur_object.object_type
               || ') '
               || '-> still invalid!');

            err_code := SQLCODE;
            err_msg := SUBSTR (SQLERRM, 1, 200);

            INSERT INTO t_cmp_invalid_objects_log
                 VALUES (s_cpl_inv_objects_error_log.NEXTVAL,
                         cur_object.object_name,
                         cur_object.owner,
                         cur_object.object_type,
                         err_code || ' -ERROR- ' || err_msg);

            COMMIT;
      END;
   END LOOP;

   str_exec := 'select count(*) FROM dba_objects
                       WHERE status = ''INVALID''';

   EXECUTE IMMEDIATE str_exec INTO total_inv;

   DBMS_OUTPUT.put_line ('Total invalid number: ' || total_inv);
END;
/
DROP TABLE t_cmp_invalid_objects_log
/
CREATE TABLE t_cmp_invalid_objects_log
(
   log_id          INT PRIMARY KEY,
   object_name     VARCHAR2 (100),
   object_schema   VARCHAR2 (100),
   object_type     VARCHAR2 (100),
   error           VARCHAR2 (1000)
)
/
DROP SEQUENCE s_cpl_inv_objects_error_log
/
CREATE SEQUENCE s_cpl_inv_objects_error_log MINVALUE 0
                                            START WITH 0
                                            INCREMENT BY 1
                                            CACHE 5;

Usage:
exec P_COMPILE_INVALID_OBJECTS();

Check error logs:
SQL> show error;
or
SQL> SELECT *
  FROM ALL_ERRORS
 WHERE OWNER = USER;

How to drop invalid objects from database?

Specify invalid objects
SQL>
  SELECT *
    FROM dba_objects
   WHERE status = 'INVALID'
ORDER BY owner;

Drop invalid objects
SQL> 
  SELECT 'DROP ' || object_type || ' ' || owner || '.' || object_name || ';'
    FROM dba_objects
   WHERE status = 'INVALID'
ORDER BY owner;

How to kill remote sessions or specific sessions?

Observe remote sessions
$ ps -ef | grep LOCAL=NO | grep ALPERDB| awk '{print $2}'
$ ps -ef | grep LOCAL=NO | grep ALPERDB| wc -l

Kill all remote sessions
$ sudo kill -9 ` ps -ef|grep LOCAL=NO|grep ALPERDB|awk '{print $2}'`

Kill spesific sessions
SQL>
SELECT DISTINCT
          'ALTER SYSTEM KILL SESSION '''
       || b.sid
       || ','
       || b.serial#
       || ''' IMMEDIATE;'
  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 name like '%EMPLOYEES%';

-- EMPLOYEES -> Table (Object) Name

Ref: https://ozsoyler.blogspot.com.tr/2017/01/how-to-kill-inactive-session-for-non.html

How to use rowid for deleting data?

If you desire delete data from joined tables such as log table, you may use rowid to prevent delete unwanted rows.

e.g.
delete from tableA where rowid in (select a.rowid from tableA a, tableB b where a.columnA = b.columnB);

How to recompile invalid objects and compile all database objects?

Generate and Run SQL for recompiling invalid objects

serial option:
SQL> 
EXEC UTL_RECOMP.recomp_serial();
SQL> 
select distinct 'EXEC UTL_RECOMP.recomp_serial(' || '''' || owner || ''');' AS SQL from dba_objects where status = 'INVALID';

parallel option (4 parallel threads):
SQL> 
EXEC UTL_RECOMP.recomp_parallel(4);
SQL> 
select distinct 'EXEC UTL_RECOMP.recomp_parallel( 4, ' || '''' || owner || ''');' AS SQL from dba_objects where status = 'INVALID';

or recompile with manual approach

SQL> 
select 'ALTER ' || (CASE WHEN object_type = 'PACKAGE BODY' THEN 'PACKAGE' ELSE object_type END) || ' ' || owner || '.' || object_name || ' COMPILE' || (CASE WHEN object_type = 'PACKAGE BODY' THEN ' BODY;' ELSE ';' END) AS SQL from dba_objects where status = 'INVALID' and object_type <> 'SYNONYM' order by owner;

Generate and Run SQL for compile all objects

SQL> 
select distinct 'EXEC DBMS_UTILITY.compile_schema(schema =>' || '''' || owner || ''')' AS SQL from dba_objects where status = 'INVALID';

Check invalid objects again
SQL> 
select * from dba_objects where status = 'INVALID' order by owner;

Count invalid objects group by object type
SQL>
select object_type,count(*) from dba_objects where status = 'INVALID' group by object_type;

How to define "Auto Replace" in Toad?

e.g.
View -> Toad Options -> Editor -> Behavior -> Auto replace -> Add -> enter "*s*" as value and "select * from " as replace with columns -> OK -> OK

Useful tool shortcuts

TOAD
Shift + Ctrl + f -> Format code
Shift + b -> Comment out code
Ctrl + Shift + b -> Uncomment code
Ctrl + z -> Undo
Ctrl + Shift + z -> Redo
Ctrl + g -> Go to line
Ctrl + . (point) -> Show object list
Ctrl + t -> Show object list
Shift + F4 -> Show action list
Ctrl + DEL -> Delete by word
Ctrl + e -> SQL Explain Plan
Ctrl + u -> Make uppercase keywords 
Ctrl + l -> Make lowercase keywords

Visual Studio
Ctrl + k + d -> Format code
Ctrl + k, Ctrl + c -> Comment code
Ctrl + k, Ctrl + u -> Uncomment code

Oracle SQL Developer
Ctrl + F7 -> Format code

How to import plugin dll for Notepad++?

Dispath the dll file to installation folder of Notepad++ under "plugins" directory.

e.g.
copy ComparePlugin.dll to C:\Program Files (x86)\Notepad++\plugins
then reopen notepad++

How to change NLS paramater?

Check current nls parameter as sys user
SQL> select * from v$nls_parameters;

e.g.
modifying date format parameter

Add below row into init.ora parameter file - apply forever
*.NLS_DATE_FORMAT='DD-MM-YYYY'

Then, restart database.
SQL> shutdown immediate;
SQL> startup;

Run below command - apply just for session
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MM-YYYY';

Referance: http://docs.oracle.com/cd/A84870_01/doc/server.816/a76966/ch2.htm

How to use existed tns connection for new one?

* The key point is 'Using' keyword that checks tnsnames.ora connection node then you can reach remote database.

SQL> CREATE DATABASE LINK "ALPER_DATABASE_NEW_LINK"
 CONNECT TO hr
 IDENTIFIED BY hr
 USING 'ALPER_DB';

SQL> CREATE DATABASE LINK "ALPER_DATABASE_NEW_LINK2"
 CONNECT TO hr
 IDENTIFIED BY hr
 USING 'ALPER_DB';

SQL> CREATE DATABASE LINK "ALPER_DATABASE_NEW_LINK3"
 CONNECT TO hr
 IDENTIFIED BY hr
 USING 'ALPER_DB';

Oracle escape character examples

First, we need an example data for study.
SQL> insert into departments values ('280','Ad_New',200,1700);

e.g.
SQL> select * from departments where department_name like 'A%';
DEPARTMENT_ID  DEPARTMENT_NAME  MANAGER_ID  LOCATION_ID
10             Administration   200         1700
110            Accounting       205         1700
280            Ad_New           200         1700

SQL> select * from departments where department_name like 'Ad_%';
10             Administration   200         1700
280            Ad_New           200         1700

SQL> select * from departments where department_name like 'Ad/_%' escape '/';
280            Ad_New           200         1700

SQL> select * from departments where department_name like 'Ad__%' escape '_';
280            Ad_New           200         1700

How to gain root access without entering root password on Linux?

Add the line into the end of /etc/sudoers file as root
vi /etc/sudoers
alper ALL=(ALL) NOPASSWD: ALL

Test your connection
ssh alper@10.10.10.10
$ sudo su -
(executes all /etc/profile, .profile and .bashrc files)
# ifconfig
$ sudo su
(executes only .bashrc file)
# ifconfig

References:

• http://askubuntu.com/questions/168461/how-do-i-sudo-without-having-to-enter-my-password
• http://askubuntu.com/questions/376199/sudo-su-vs-sudo-i-vs-sudo-bin-bash-when-does-it-matter-which-is-used

How to define new db link?

Add a new connection node into tnsnames.ora file.
vi $ORACLE_HOME/network/admin/tnsnames.ora
e.g.

ALPER_DB =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.10.10)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SID = XE )

    ))

Check remote host via tnsping

$ tnsping ALPER_DB

Define a new db link

For specific user:

SQL> CREATE DATABASE LINK "ALPER_DATABASE"

 CONNECT TO hr

 IDENTIFIED BY hr

 USING 'ALPER_DB';

If you desire all users use this link you should add "public" into create statement

For All users (public):

SQL> CREATE PUBLIC DATABASE LINK "ALPER_DATABASE"

 CONNECT TO hr

 IDENTIFIED BY hr

 USING 'ALPER_DB';

Alternative option: define a new db link

For specific user:

SQL> CREATE DATABASE LINK "ALPER_DATABASE"

 CONNECT TO hr

 IDENTIFIED BY hr

 USING '(DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.10.10)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SID = XE )

    ))';

Observe the new link and check it

For specific users:

SQL> select * from user_db_links where db_link = 'ALPER_DATABASE';

SQL> select * from employees@ALPER_DATABASE

For All users (public):

SQL> select * from all_db_links where db_link = 'ALPER_DATABASE';

SQL> select * from employees@ALPER_DATABASE

Extra
Connect without db link:
$ sqlplus "hr@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=10.10.10.10)(Port=1521))(CONNECT_DATA=(SID=XE)))"
or
CMD> sqlplus hr@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=10.10.10.10)(Port=1521))(CONNECT_DATA=(SID=XE)))