-> unrar .zip file
e.g. C:\oracle_client\12.1.0\client_1
-> add this to "Environment Variables->Path"
e.g. C:\oracle_client\12.1.0\client_1
-> add this to "Environment Variables->Path"
That's all :)
Thursday 27 October 2016
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
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
Wednesday 26 October 2016
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';
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';
Tuesday 25 October 2016
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
DBMS_SNAPSHOT.REFRESH( 'v_alper_ss','f');
end;
/
f -> fast refresh
? -> force refresh
c -> complete refresh
a -> always refresh
Monday 24 October 2016
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>
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>
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
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>'ALTER SYSTEM KILL SESSION '''
|| b.sid
|| ','
|| b.serial#
|| ''' IMMEDIATE;'
AS KILL_THEM_ALL
FROM sys.v_$session B
WHERE b.sid = 2283;
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
SELECT 'begin sys.dbms_ijob.remove(' || job || '); commit; end;' || chr(10) || '/' AS DROP_JOBS
'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>
SQL>
FROM dba_jobs;
-- ijob uses for all jobs in dbms_scheduler (new version of dbms_jobs)
Friday 21 October 2016
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;
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;
Wednesday 19 October 2016
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;
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;
Monday 17 October 2016
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
$ 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);
e.g.
delete from tableA where rowid in (select a.rowid from tableA a, tableB b where a.columnA = b.columnB);
Tuesday 11 October 2016
How to recompile invalid objects and compile all database objects?
Generate and Run SQL for recompiling invalid objects
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';
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';
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;
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;
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;
Monday 3 October 2016
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
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
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
Subscribe to:
Posts (Atom)