Tuesday, 28 November 2017

How to show line number in SQL Developer?

›
Tools -> Preferences -> Code Editor -> Line Gutter -> Activate " Show Line Numbers " -> OK
Wednesday, 8 November 2017

How to export/set ORACLE_SID?

›
Linux export variable $ export ORACLE_SID=XE check variable $ echo $ORACLE_SID Windows set variable > set ORACLE_SID=XE check...
Friday, 3 November 2017

ORA-01950 Solution

›
Action SQL> CREATE TABLE HR2.EMPLOYEES_NEW AS SELECT * FROM HR.EMPLOYEES; Error ORA-01950: no privileges on tablespace 'USERS...
Wednesday, 25 October 2017

ORA-02266 solution

›
Action A table has a PK (Primary Key) B table has a FK (Foreign Key) and referenced to A table's PK -> User tries to truncate tab...

How to disable/enable all triggers for tables?

›
Disable triggers for a table SQL> alter table HR.TRG_EMPLOYEES disable all triggers; Enable triggers for a table SQL> alter table ...
Saturday, 14 October 2017

ORA-01045 solution

›
Error ORA-01045: user HR lacks CREATE SESSION privilege; logon denied Solution Grant CREATE SESSION privilege to 'HR' user SQL...
Friday, 6 October 2017

ORA-28000 solution

›
Error ORA-28000: the account is locked Solution Find the assigned profile for related user SQL> select profile from DBA_USERS where ...
Tuesday, 5 September 2017

How to enable/disable DBA auto task jobs?

›
Jobs Automatic Optimizer Statistics Collection -> Gathers stale or missing statistics Automatic Segment Advisor -> Identifies segm...
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...