Tuesday, 29 August 2017

e.g. shrink table usage

›
Steps: SQL> exec dbms_stats.gather_table_stats ('HR','EMPLOYEES'); -- Gathering table statistics SQL> SELECT own...
Monday, 28 August 2017

DBMS_JOBS vs DBMS_SCHEDULER

›
-> DBMS_SCHEDULER introduced in 10g releases so before this, we were able to use only DBMS_JOBS for scheduler jobs. e.g. DBMS_JOBS SQL...
Tuesday, 22 August 2017

How to monitor TEMP tablespace resource usage?

›
-> Check Total TEMP usage: SQL> SELECT B.TOTAL_MB,        B.TOTAL_MB - ROUND (A.USED_BLOCKS * 8 / 1024) CURRENT_FREE_MB,        RO...
Monday, 7 August 2017

e.g. simple DDL audit trigger

›
e.g. DDL audit trigger CREATE TABLE SYSTEM.audit_ddl_history ( osuser varchar2(50), session_user varchar2(50), host varchar2(50), ter...
Saturday, 5 August 2017

Solution ORA-01031, ORA-01994

›
Error ORA-01031: insufficient privileges Solution Give sysdba role to sys SQL> grant sysdba to sys;  If you get this error -> ...
Tuesday, 1 August 2017

How to start/stop/configure samba service?

›
If you need, uninstall old samba # yum erase samba samba-common samba-client  install tool # yum install samba4 or upgrade tool # yum...

ORA-28002 solution

›
Error ORA-28002: the password will expire within [A] days Solution Disable password expiration SQL> select profile from DBA_USERS wh...
Tuesday, 25 July 2017

SQL PLUS system parameters usage (set/show)

›
To show parameter in detail SQL> SHOW DEFINE; To escape from substitution variable (&): SQL> SET DEFINE OFF; To enable DBMS_...

e.g. changing the DBID and Database Name

›
e.g. Backup database RMAN> backup database Mount database SQL> shu immediate; SQL> startup mount; Change DBID $ nid TARGET...
Monday, 17 July 2017

SYS_CONTEXT function parameters and examples

›
Active parameters ACTION, AUDITED_CURSORID, AUTHENTICATED_IDENTITY, AUTHENTICATION_DATA, AUTHENTICATION_METHOD, BG_JOB_ID, CLIENT_IDENTIFIE...
Friday, 14 July 2017

Quiesce restricted mode vs Restricted mode

›
Differences; * Quiesce restricted option is less raugh than restricted option and they are quite similar. * In quiesce restricted option, ...
Tuesday, 11 July 2017

log_buffer, log_checkpoint_timeout and log_checkpoint_interval parameters

›
Purpose We are able to tune CKPT process with these paramaters and they should be configured together. Usage e.g. Check current parame...
Wednesday, 28 June 2017

plsql_optimize_level and plsql_code_type parameters

›
Purpose We are able to use  plsql_optimize_level parameter for getting better execution performance on plsql codes. On the other way,  pls...
Tuesday, 20 June 2017

How to find database object via SQL statements?

›
SQL> select * from all_source where owner <> 'SYS' and upper(text) like '%ABC%'
Saturday, 10 June 2017

How to solve SQL Developer redrawing problem on Windows?

›
Problem Solution Tools -> Preferences -> Lock and Feel -> Set Windows -> Click OK
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 u...
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 ch...
Monday, 5 June 2017

How to calculate maximum database file size?

›
Info:  Maximum number of DB blocks are 4194304 blocks ( 2 22 )  per database file Maximum DB block size is 32 KB per DB block SQL>...
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 f...
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 ...