Problem
Solution
Tools -> Preferences -> Lock and Feel -> Set Windows -> Click OK
Saturday, 10 June 2017
Thursday, 8 June 2017
e.g. bind variable usage
bind variable;
When user executes an SQL query or a PL/SQL code, Oracle uses CPU resource for parsing operations on the SGA. However, if user execute the query with bind variable, "soft parsing" exists instead of "hard parsing". Therefore, "total DB time" and "parsing time" decreases, library cache size is not occupied unnecessarily as well.
e.g.
SET TIMING ON;
VARIABLE v_bind_value NUMBER;
DECLARE
v_value VARCHAR2 (30);
BEGIN
:v_bind_value := 100;
SELECT last_name
INTO v_value
FROM employees
WHERE employee_id = :v_bind_value;
DBMS_OUTPUT.put_line (v_value);
END;
/
When user executes an SQL query or a PL/SQL code, Oracle uses CPU resource for parsing operations on the SGA. However, if user execute the query with bind variable, "soft parsing" exists instead of "hard parsing". Therefore, "total DB time" and "parsing time" decreases, library cache size is not occupied unnecessarily as well.
e.g.
SET TIMING ON;
VARIABLE v_bind_value NUMBER;
DECLARE
v_value VARCHAR2 (30);
BEGIN
:v_bind_value := 100;
SELECT last_name
INTO v_value
FROM employees
WHERE employee_id = :v_bind_value;
DBMS_OUTPUT.put_line (v_value);
END;
/
Wednesday, 7 June 2017
A brief look to Oracle backup mode
Backup mode;
Oracle runs slightly different that database files are able to only use for read (not for write) in SGA and all database changes (DML, DDL, etc) are written into redo log files by Oracle. In that time, user can copy database files to desired folders because database file are frozen by system.
For example, when DML execute, all data block changes are written to redo log files as a exact block not as a delta log. Therefore,
Oracle runs slowly according to normal mode and staying backup mode for a long time is also important risk for recovery operations.
e.g. -> if you run "shutdown abort", database gets crash in startup (ORA-10873, ORA-01110). To solve it, run "recover database".
Enable backup mode:
SQL> alter database begin backup;
If you get ORA-01123 error;
Enable archivelog mode;
https://ozsoyler.blogspot.com.tr/2014/10/how-to-checkopen-archieve-log-in-sqlplus.html
To check current db file status:
SQL> select * from v$backup;
-- status column output should be "ACTIVE"
Now we are able to copy/paste operation on disk:
find datafile paths;
SQL> select * from v$datafile;
Finish file transfer operations then disable backup mode:
SQL> alter database end backup;
Oracle runs slightly different that database files are able to only use for read (not for write) in SGA and all database changes (DML, DDL, etc) are written into redo log files by Oracle. In that time, user can copy database files to desired folders because database file are frozen by system.
For example, when DML execute, all data block changes are written to redo log files as a exact block not as a delta log. Therefore,
Oracle runs slowly according to normal mode and staying backup mode for a long time is also important risk for recovery operations.
e.g. -> if you run "shutdown abort", database gets crash in startup (ORA-10873, ORA-01110). To solve it, run "recover database".
Enable backup mode:
SQL> alter database begin backup;
If you get ORA-01123 error;
Enable archivelog mode;
https://ozsoyler.blogspot.com.tr/2014/10/how-to-checkopen-archieve-log-in-sqlplus.html
To check current db file status:
SQL> select * from v$backup;
-- status column output should be "ACTIVE"
Now we are able to copy/paste operation on disk:
find datafile paths;
SQL> select * from v$datafile;
Finish file transfer operations then disable backup mode:
SQL> alter database end backup;
Monday, 5 June 2017
How to calculate maximum database file size?
Info:
-- Generally it is 8192 byte (8 KB)
e.g. if block size is 8 KB;
maximum database file size = 4194304 x 8 KB = 33554432 KB = 32768 MB = 32 GB
e.g. if block size is 32 KB (this can be maximum 32 KB)
maximum database file size = 4194304 x 32 KB = 134217728 KB = 131072 MB = 128 GB
Ref: https://docs.oracle.com/cd/B28359_01/server.111/b28320/limits002.htm#i287915
https://docs.oracle.com/cd/B28359_01/server.111/b32009/appg_db_lmts.htm#UNXAR408
- Maximum number of DB blocks are 4194304 blocks (222) per database file
- Maximum DB block size is 32 KB per DB block
-- Generally it is 8192 byte (8 KB)
e.g. if block size is 8 KB;
maximum database file size = 4194304 x 8 KB = 33554432 KB = 32768 MB = 32 GB
e.g. if block size is 32 KB (this can be maximum 32 KB)
maximum database file size = 4194304 x 32 KB = 134217728 KB = 131072 MB = 128 GB
Ref: https://docs.oracle.com/cd/B28359_01/server.111/b28320/limits002.htm#i287915
https://docs.oracle.com/cd/B28359_01/server.111/b32009/appg_db_lmts.htm#UNXAR408
Sunday, 4 June 2017
statistics_level parameter
Purpose
The purpose of this parameter is to gather database and operating system statistics into SYSAUX tablespace. For example, it uses for defining the detail of AWR reports.
Usage
Current setting:
SQL> show parameter statistics_level;
-- default is "TYPICAL"
(options -> BASIC | TYPICAL | ALL)
Change setting permanently:
add this into init.ora file;
*.statistics_level='ALL'
-- BASIC is not enough to get AWR Report
-- TYPICAL is recommended for AWR Report
-- ALL gathers operating system and plan execution statistics records in extra for AWR Report
Then restart the instance to perform.
Change setting for current instance - temporary:
SQL> alter system set statistics_level = ALL;
Result
SQL> show parameter statistics_level;
-- output; ALL
To check detail information, we may look at v$statistics_level view.
SQL> select * from v$statistics_level;
Ref: https://docs.oracle.com/cd/B28359_01/server.111/b28320/initparams240.htm#REFRN10214
The purpose of this parameter is to gather database and operating system statistics into SYSAUX tablespace. For example, it uses for defining the detail of AWR reports.
Usage
Current setting:
SQL> show parameter statistics_level;
-- default is "TYPICAL"
(options -> BASIC | TYPICAL | ALL)
Change setting permanently:
add this into init.ora file;
*.statistics_level='ALL'
-- BASIC is not enough to get AWR Report
-- TYPICAL is recommended for AWR Report
-- ALL gathers operating system and plan execution statistics records in extra for AWR Report
Then restart the instance to perform.
Change setting for current instance - temporary:
SQL> alter system set statistics_level = ALL;
Result
SQL> show parameter statistics_level;
-- output; ALL
To check detail information, we may look at v$statistics_level view.
SQL> select * from v$statistics_level;
Ref: https://docs.oracle.com/cd/B28359_01/server.111/b28320/initparams240.htm#REFRN10214
Saturday, 3 June 2017
db_ultra_safe parameter
Purpose
The purpose of this parameter is to see instant file corruption on disk then DBA takes action against the issue.
Usage
Current setting:
SQL> show parameter db_ultra_safe;
-- default is "OFF"
Change setting (options -> OFF | DATA_ONLY | DATA_AND_INDEX):
add this into init.ora file;
*.db_ultra_safe='DATA_ONLY'
-- DB_BLOCK_CHECKING, DB_BLOCK_CHECKSUM, and DB_LOST_WRITE_PROTECT parameter are set by automatically according to db_ultra_safe parameter setting.
Then restart the instance to perform.
Result
SQL> show parameter db_ultra_safe;
-- output; data_only
Ref: https://docs.oracle.com/cd/B28359_01/server.111/b28320/initparams064.htm#REFRN10295
The purpose of this parameter is to see instant file corruption on disk then DBA takes action against the issue.
Usage
Current setting:
SQL> show parameter db_ultra_safe;
-- default is "OFF"
Change setting (options -> OFF | DATA_ONLY | DATA_AND_INDEX):
add this into init.ora file;
*.db_ultra_safe='DATA_ONLY'
-- DB_BLOCK_CHECKING, DB_BLOCK_CHECKSUM, and DB_LOST_WRITE_PROTECT parameter are set by automatically according to db_ultra_safe parameter setting.
Then restart the instance to perform.
Result
SQL> show parameter db_ultra_safe;
-- output; data_only
Ref: https://docs.oracle.com/cd/B28359_01/server.111/b28320/initparams064.htm#REFRN10295
Sunday, 21 May 2017
Oracle Enterprise Manager Version History
- Oracle Enterprise Manager Cloud Control 13c Release 2 (13.2.0.0)
- Oracle Enterprise Manager Cloud Control 13c Release 1 (13.1.0.0)
- Oracle Enterprise Manager Cloud Control 12c Release 5 (12.1.0.5)
Last Updated: 21/05/2017
Thursday, 18 May 2017
e.g. row lock (tx) and table lock (tm)
e.g.
row lock (tx)
In -A- database:
SQL>
select * from employees where employee_id = 100 FOR UPDATE;
to see the error;
In -B- database:
SQL>
update employees@alper_database
set salary = 10000
where employee_id = 100;
error -> ORA-02049: timeout: distributed transaction waiting for lock
e.g.
table lock (tm)
In -A- database:
SQL>
LOCK TABLE employees IN EXCLUSIVE MODE NOWAIT;
to see the error;
In -B- database:
SQL>
update employees@alper_database
set salary = 2500
where employee_id = 105;
--or
In -B- database:
LOCK TABLE employees@alper_database IN SHARE MODE;
to see the error;
In -A- database:
SQL>
update employees
set salary = 2500
where employee_id = 105;
same error for both methods -> ORA-02049: timeout: distributed transaction waiting for lock
ref: https://docs.oracle.com/cd/E11882_01/server.112/e41084/ap_locks001.htm#SQLRF55502
row lock (tx)
In -A- database:
SQL>
select * from employees where employee_id = 100 FOR UPDATE;
to see the error;
In -B- database:
SQL>
update employees@alper_database
set salary = 10000
where employee_id = 100;
error -> ORA-02049: timeout: distributed transaction waiting for lock
e.g.
table lock (tm)
In -A- database:
SQL>
LOCK TABLE employees IN EXCLUSIVE MODE NOWAIT;
to see the error;
In -B- database:
SQL>
update employees@alper_database
set salary = 2500
where employee_id = 105;
--or
In -B- database:
LOCK TABLE employees@alper_database IN SHARE MODE;
to see the error;
In -A- database:
SQL>
update employees
set salary = 2500
where employee_id = 105;
same error for both methods -> ORA-02049: timeout: distributed transaction waiting for lock
ref: https://docs.oracle.com/cd/E11882_01/server.112/e41084/ap_locks001.htm#SQLRF55502
Tuesday, 16 May 2017
ORA-2049 examples
Error
ORA-2049 timeout: distributed transaction waiting for lock
SQL> sho parameter distr
-- Default: 60 sec
e.g.
In -A- Database
select * from employees where employee_id = 100 for update;
or
update employees
set salary = 15000
where employee_id = 100;
-- Locked the row
IN -B- Database
select * from employees@a_dblink where employee_id = 100
update employees@a_dblink
set salary = 10000
where employee_id = 100;
-- Error occurred after 60 seconds -> ORA-2049
To decrease wait time:
add this into init.ora file in -A- database;
*.distributed_lock_timeout=5
Then restart the instance to perform.
To wait just one second in -B- Database:
select* from employees@a_dblink
where employee_id = 100
for update wait 1;
-- Error occurred after 1 second -> ORA-2049 if the row locked
Then try update;
update employees@a_dblink
set salary = 10000
where employee_id = 100;
commit;
Not to wait:
select* from employees@a_dblink
where employee_id = 100
for update nowait;
-- Error occurred immediately -> ORA-2049 if the row locked
Then try update;
update employees@a_dblink
set salary = 10000
where employee_id = 100;
commit;
ORA-2049 timeout: distributed transaction waiting for lock
SQL> sho parameter distr
-- Default: 60 sec
e.g.
In -A- Database
select * from employees where employee_id = 100 for update;
or
update employees
set salary = 15000
where employee_id = 100;
-- Locked the row
IN -B- Database
select * from employees@a_dblink where employee_id = 100
update employees@a_dblink
set salary = 10000
where employee_id = 100;
-- Error occurred after 60 seconds -> ORA-2049
To decrease wait time:
add this into init.ora file in -A- database;
*.distributed_lock_timeout=5
Then restart the instance to perform.
To wait just one second in -B- Database:
select* from employees@a_dblink
where employee_id = 100
for update wait 1;
-- Error occurred after 1 second -> ORA-2049 if the row locked
Then try update;
update employees@a_dblink
set salary = 10000
where employee_id = 100;
commit;
Not to wait:
select* from employees@a_dblink
where employee_id = 100
for update nowait;
-- Error occurred immediately -> ORA-2049 if the row locked
Then try update;
update employees@a_dblink
set salary = 10000
where employee_id = 100;
commit;
Wednesday, 10 May 2017
e.g. OLD and NEW Pseudorecords
e.g.
SQL>
create table employees_salary_log (employee_id number, cur_salary number(8,2), new_salary number(8,2), updated_date date);
SQL>
CREATE OR REPLACE TRIGGER HR.TBU_EMPLOYEES
BEFORE UPDATE
ON HR.EMPLOYEES
REFERENCING NEW AS NEW_ROW OLD AS OLD_ROW
FOR EACH ROW
DECLARE
BEGIN
IF :OLD_ROW.MANAGER_ID IS NOT NULL
THEN
INSERT INTO employees_salary_log
VALUES (:NEW_ROW.employee_id, :OLD_ROW.salary, :NEW_ROW.salary, SYSTIMESTAMP);
END IF;
EXCEPTION
WHEN OTHERS
THEN
raise_application_error (
-20001,
'An error was encountered - ' || SQLCODE || ' -ERROR- ' || SQLERRM);
NULL;
END;
/
update employees
set salary = 17100
where employee_id = 101
/
update employees
set salary = 35000
where employee_id = 100
/
commit
/
select * from employees_salary_log
-- only display employee who is not a manager
SQL>
create table employees_salary_log (employee_id number, cur_salary number(8,2), new_salary number(8,2), updated_date date);
SQL>
CREATE OR REPLACE TRIGGER HR.TBU_EMPLOYEES
BEFORE UPDATE
ON HR.EMPLOYEES
REFERENCING NEW AS NEW_ROW OLD AS OLD_ROW
FOR EACH ROW
DECLARE
BEGIN
IF :OLD_ROW.MANAGER_ID IS NOT NULL
THEN
INSERT INTO employees_salary_log
VALUES (:NEW_ROW.employee_id, :OLD_ROW.salary, :NEW_ROW.salary, SYSTIMESTAMP);
END IF;
EXCEPTION
WHEN OTHERS
THEN
raise_application_error (
-20001,
'An error was encountered - ' || SQLCODE || ' -ERROR- ' || SQLERRM);
NULL;
END;
/
update employees
set salary = 17100
where employee_id = 101
/
update employees
set salary = 35000
where employee_id = 100
/
commit
/
select * from employees_salary_log
-- only display employee who is not a manager
Monday, 8 May 2017
How to know $ORACLE_HOME path via sqlplus in Windows ?
CMD> sqlplus / as sysdba
SQL>
set serveroutput on;
SQL>
DECLARE
Result_Val VARCHAR2(100);
BEGIN
dbms_system.get_env('ORACLE_HOME', Result_Val);
dbms_output.put_line(Result_Val);
END;
/
SQL>
set serveroutput on;
SQL>
DECLARE
Result_Val VARCHAR2(100);
BEGIN
dbms_system.get_env('ORACLE_HOME', Result_Val);
dbms_output.put_line(Result_Val);
END;
/
How to use recylebin ?
Check recyclebin is active whether or not
session scope:
SQL>
ALTER SESSION SET recyclebin = ON;
active instance scope:
SQL>
ALTER SYSTEM SET recyclebin = ON;
permanent setting in init.ora file:
*.recyclebin=on
usage of recylebin
SQL>
create table ex_emp as select * from employees;
SQL>
drop table ex_emp;
If you use purge parameter, you cannot take back the table from recyclebin!
-- SQL> drop table ex_emp purge;
check table versions if hr logged in
select * from USER_RECYCLEBIN order by droptime desc;
-- or
select * from RECYCLEBIN order by droptime desc;;
check table versions if sys logged in
select * from DBA_RECYCLEBIN order by droptime desc;;
Note object_name from result then:
SQL>
select * from "BIN$g7GY62bMSHOAic2pfNtsIg==$0";
To restore;
SQL>
create table new_ex_emp as
select * from "BIN$g7GY62bMSHOAic2pfNtsIg==$0";
-- or
SQL>
FLASHBACK TABLE ex_emp
TO BEFORE DROP
RENAME TO new_ex_emp;
session scope:
SQL>
ALTER SESSION SET recyclebin = ON;
active instance scope:
SQL>
ALTER SYSTEM SET recyclebin = ON;
permanent setting in init.ora file:
*.recyclebin=on
usage of recylebin
SQL>
create table ex_emp as select * from employees;
SQL>
drop table ex_emp;
If you use purge parameter, you cannot take back the table from recyclebin!
-- SQL> drop table ex_emp purge;
check table versions if hr logged in
select * from USER_RECYCLEBIN order by droptime desc;
-- or
select * from RECYCLEBIN order by droptime desc;;
select * from DBA_RECYCLEBIN order by droptime desc;;
Note object_name from result then:
SQL>
select * from "BIN$g7GY62bMSHOAic2pfNtsIg==$0";
To restore;
SQL>
create table new_ex_emp as
select * from "BIN$g7GY62bMSHOAic2pfNtsIg==$0";
-- or
SQL>
FLASHBACK TABLE ex_emp
TO BEFORE DROP
RENAME TO new_ex_emp;
Thursday, 4 May 2017
How to locate currently used listener.ora file?
e.g.
Find oracle client which is using by server:
$ which sqlplus
-- /ora11g/orahome/bin/sqlplus
Check listener.ora:
$ cat /ora11g/orahome/network/admin/listener.ora
-- e.g. port -> 1521
Ref: https://ozsoyler.blogspot.com/2016/11/how-to-configure-listener-port-when-it.html
Find oracle client which is using by server:
$ which sqlplus
-- /ora11g/orahome/bin/sqlplus
Check listener.ora:
$ cat /ora11g/orahome/network/admin/listener.ora
-- e.g. port -> 1521
Ref: https://ozsoyler.blogspot.com/2016/11/how-to-configure-listener-port-when-it.html
How to generate sql explain plan?
e.g.
prepare example table:
SQL>
create table ep_employees as select * from employees
observe costs values:
SQL>
explain plan for select * from ep_employees where job_id = 'SA_MAN';
-- Explained.
SQL>
select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------
-------------------------
Plan hash value: 745119821
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 675 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EP_EMPLOYEES | 5 | 675 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("JOB_ID"='SA_MAN')
Note
-----
- dynamic sampling used for this statement (level=2)
Fix full access issue on ep_employees table:
SQL>
create index ep_emp_job_ix on ep_employees (job_id);
SQL>
select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------
-------------------------
Plan hash value: 3994861717
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 675 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EP_EMPLOYEES | 5 | 675 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | EP_EMP_JOB_IX | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("JOB_ID"='SA_MAN')
Note
-----
- dynamic sampling used for this statement (level=2)
Results:
Costs are reduced so sql query performance got increased.
prepare example table:
SQL>
create table ep_employees as select * from employees
observe costs values:
SQL>
explain plan for select * from ep_employees where job_id = 'SA_MAN';
-- Explained.
SQL>
select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------
-------------------------
Plan hash value: 745119821
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 675 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EP_EMPLOYEES | 5 | 675 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("JOB_ID"='SA_MAN')
Note
-----
- dynamic sampling used for this statement (level=2)
Fix full access issue on ep_employees table:
SQL>
create index ep_emp_job_ix on ep_employees (job_id);
SQL>
select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------
-------------------------
Plan hash value: 3994861717
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 675 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EP_EMPLOYEES | 5 | 675 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | EP_EMP_JOB_IX | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("JOB_ID"='SA_MAN')
Note
-----
- dynamic sampling used for this statement (level=2)
Results:
Costs are reduced so sql query performance got increased.
Wednesday, 3 May 2017
How to find table by a column name in Oracle?
Check table info
SQL> desc employees
or
SQL>
select * from all_tab_columns where lower(table_name) = 'employees'
Find column name
e.g.
Current user scope:
SQL>
select * from all_tab_columns where lower(column_name) = 'employee_id'
DBA user scope:
SQL>
select * from dba_tab_columns where lower(column_name) = 'employee_id'
SQL> desc employees
or
SQL>
select * from all_tab_columns where lower(table_name) = 'employees'
Find column name
e.g.
Current user scope:
SQL>
select * from all_tab_columns where lower(column_name) = 'employee_id'
DBA user scope:
SQL>
select * from dba_tab_columns where lower(column_name) = 'employee_id'
Friday, 28 April 2017
ORA-06553: PLS-907 Solution
Error
ORA-06553: PLS-907: cannot load library unit aaa (object)
(referenced by bbb)
Solution
Compile related object (bbb)
SQL> ALTER FUNCTION HR.BBB COMPILE;
ORA-06553: PLS-907: cannot load library unit aaa (object)
(referenced by bbb)
Solution
Compile related object (bbb)
SQL> ALTER FUNCTION HR.BBB COMPILE;
Thursday, 27 April 2017
ORA-00059 solution
Error
ORA-00059: maximum number of DB_FILES exceeded
Solution
Check limit that total number of db files
SQL> show parameter db_files;
-- e.g. 250
Edit parameter file:
# add this on bottom
*.db_files=500
Shutdown and startup database:
SQL> shu immediate;
SQL> startup;
Extra:
If database is using spfile:
SQL> show parameter spfile;
-- output should be not empty
Delete spfile under $ORACLE_HOME/dbs directory such as
"spfileALPERDB.ora"
At last, shutdown and startup database again.
ORA-00059: maximum number of DB_FILES exceeded
Solution
Check limit that total number of db files
SQL> show parameter db_files;
-- e.g. 250
Edit parameter file:
# add this on bottom
*.db_files=500
Shutdown and startup database:
SQL> shu immediate;
SQL> startup;
Extra:
If database is using spfile:
SQL> show parameter spfile;
-- output should be not empty
Delete spfile under $ORACLE_HOME/dbs directory such as
"spfileALPERDB.ora"
At last, shutdown and startup database again.
Friday, 21 April 2017
How to restore database object such as package body via flashback?
Note that below SQL gets output if it is still in UNDO tablespace.
SQL>
SELECT text FROM all_source
AS OF TIMESTAMP TO_TIMESTAMP ('21-04-2017 14:30:00', 'DD-MM-YYYY HH24:MI:SS')
WHERE name = 'PRG_BODY_ALPER'
AND TYPE = 'PACKAGE BODY';
for DBA: dba_source table
Related: https://ozsoyler.blogspot.com/2016/12/how-to-rollback-committed-data-from.html
SQL>
SELECT text FROM all_source
AS OF TIMESTAMP TO_TIMESTAMP ('21-04-2017 14:30:00', 'DD-MM-YYYY HH24:MI:SS')
WHERE name = 'PRG_BODY_ALPER'
AND TYPE = 'PACKAGE BODY';
for DBA: dba_source table
Related: https://ozsoyler.blogspot.com/2016/12/how-to-rollback-committed-data-from.html
Wednesday, 12 April 2017
How to see TOP 10 SQL that consuming system resources highly?
e.g.
select * from
(
select * from v$sql
order by DISK_READS desc
)
where rownum < 11
options for order by:
RUNTIME_MEM, EXECUTIONS, DISK_READS, SORTS, ELAPSED_TIME
for old querries:
select * from dba_hist_sqltext where sql_id = '8j2hv3c6wskdy'
for SQL plan:
select * from V$SQL_PLAN where sql_id = '8j2hv3c6wskdy'
find the object location of SQL
select * from all_source where owner <> 'SYS' and upper(text) like '%ABC%'
select * from
(
select * from v$sql
order by DISK_READS desc
)
where rownum < 11
options for order by:
RUNTIME_MEM, EXECUTIONS, DISK_READS, SORTS, ELAPSED_TIME
for old querries:
select * from dba_hist_sqltext where sql_id = '8j2hv3c6wskdy'
for SQL plan:
select * from V$SQL_PLAN where sql_id = '8j2hv3c6wskdy'
find the object location of SQL
select * from all_source where owner <> 'SYS' and upper(text) like '%ABC%'
Tuesday, 11 April 2017
How to find and investigate a process in top command output?
Total connection by machine:
SQL> select machine, count(*) from v$session group by machine order by 2;
Total number of database user who connected:
SQL> select count(1) "NO. Of DB Users Connected", to_char(sysdate,'DD-MON-YYYY:HH24:MI:SS') sys_time from v$session where username is NOT NULL;
Define a process(pid) from top:
17831 ora11g .. ora_m000_ALPERDB
Get detailed info from database:
SQL> select * from v$process where spid = 17831;
-> M000 means MMON Slave Process
-> e.g. pid -> 16, sid -> 555
SQL>
SELECT s.username, osuser, process machine, p.terminal, type, sid, s.serial#, s.program
FROM v$session s, v$process p
WHERE p.pid = 16
AND s.paddr = p.addr;
SQL> select * from gv$session where sid = 555;
Get sql from below code related to example process if it has SQL:
SQL>
SELECT
s.sid
,s.CLIENT_INFO
,s.MACHINE
,s.PROGRAM
,s.TYPE
,s.logon_time
,s.osuser
,sq.sorts
,sq.DISK_READS
,sq.BUFFER_GETS
,sq.ROWS_PROCESSED
,sq.SQLTYPE
,sq.SQL_TEXT
FROM gv$session s
, gv$sql sq
WHERE s.SQL_HASH_VALUE = sq.HASH_VALUE
--AND s.inst_id= 1
AND s.sid = 555
AND sq.inst_id= s.inst_id
SQL> select machine, count(*) from v$session group by machine order by 2;
Total number of database user who connected:
SQL> select count(1) "NO. Of DB Users Connected", to_char(sysdate,'DD-MON-YYYY:HH24:MI:SS') sys_time from v$session where username is NOT NULL;
Define a process(pid) from top:
17831 ora11g .. ora_m000_ALPERDB
Get detailed info from database:
SQL> select * from v$process where spid = 17831;
-> M000 means MMON Slave Process
-> e.g. pid -> 16, sid -> 555
SQL>
SELECT s.username, osuser, process machine, p.terminal, type, sid, s.serial#, s.program
FROM v$session s, v$process p
WHERE p.pid = 16
AND s.paddr = p.addr;
SQL> select * from gv$session where sid = 555;
Get sql from below code related to example process if it has SQL:
SQL>
SELECT
s.sid
,s.CLIENT_INFO
,s.MACHINE
,s.PROGRAM
,s.TYPE
,s.logon_time
,s.osuser
,sq.sorts
,sq.DISK_READS
,sq.BUFFER_GETS
,sq.ROWS_PROCESSED
,sq.SQLTYPE
,sq.SQL_TEXT
FROM gv$session s
, gv$sql sq
WHERE s.SQL_HASH_VALUE = sq.HASH_VALUE
--AND s.inst_id= 1
AND s.sid = 555
AND sq.inst_id= s.inst_id
Subscribe to:
Posts (Atom)