Monday, 18 December 2017

How to backup Oracle SQL Developer connection list?

›
Backup connection.xml file from SQL Developer Setup folder e.g. Oracle SQL Developer Version 17.3.1.279.0537 -> C:\Users\ %USERNAME% ...

char vs byte

›
e.g. SQL>  CREATE TABLE exp_table (    col1   CHAR (1 CHAR),    col2   CHAR (1 BYTE),    col3   CHAR (1) ) -> col1 can store 4...
Friday, 8 December 2017

How to install/use rlwrap?

›
Installation e.g. # rpm -ivh rlwrap-0.42-1.el6.x86_64.rpm   Usage Add alias to .bash_profile file (or .bashrc, etc.) alias sqlplus=...
Wednesday, 6 December 2017

How to define "Code Templates" in SQL Developer?

›
e.g. SQL DEVELOPER -> TOOLS -> PREFERENCES -> DATABASE -> SQL EDITOR CODE TEMPLATES -> Click "ADD TEMPLATE" butto...
Tuesday, 5 December 2017

ORA-00900, ORA-00911 solution

›
SQL DECLARE    sql_text   VARCHAR (1000); BEGIN    sql_text := ' exec P_COMPILE_INVALID_OBJECTS()';    DBMS_OUTPUT.PUT_LINE (s...
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...