Wednesday, 30 November 2016

How to export/import schema/table with Oracle Data Pump?

›
Define new user: SQL> CREATE USER NEWHR   IDENTIFIED BY newhr   DEFAULT TABLESPACE USERS   TEMPORARY TABLESPACE TEMP   PROFILE DEFA...

What are database startup options?

›
SQL>  STARTUP --> starts normally SQL>   STARTUP nomount --> starts as nomount mode SQL>   STARTUP  restrict --> s...
Tuesday, 29 November 2016

How to enable debug mode?

›
SQL> GRANT DEBUG CONNECT SESSION TO hr; SQL> GRANT DEBUG ANY PROCEDURE TO hr; Lastly, reconnect to the session and try again :)
Wednesday, 23 November 2016

PLS-00201 Solution

›
Error PLS-00201: identifier ' TABLE_ALPER ' must be declared Solution Define public synonym for identifier e.g. SQL> CREA...

How to truncate a folder in Outlook?

›
Right mouse click on desired folder -> Select " Delete All " -> Click " Yes " -> Done! :)
Wednesday, 16 November 2016

ORA-01578 solution (if object is INDEX)

›
Error ORA-01578: ORACLE data block corrupted (file # 100, block # 50000) Solution Find the object that was corrupted: SQL>  ...
Thursday, 10 November 2016

How to get AWR report as html file?

›
Generate Snapshots SQL> EXEC DBMS_WORKLOAD_REPOSITORY.create_snapshot; -> Run this SQL as twice (at the begin and at the end) Get...

How to add target manually in 12c EM ?

›
Prepare the 12c EM server # vi /etc/hosts 10.10.10.10 alperdb.com -> remote machine (agent) IP and hostname Prepare the environment...
Wednesday, 9 November 2016

ORA-27102 solution

›
Error SQL> startup; ORA-27102: out of memory Linux-x86_64 Error: 28: No space left on device  Solution $ vi /etc/sysctl.conf   ed...
Monday, 7 November 2016

How to import old Toad profile for new one?

›
Export old  User Files from  %USERPROFILE%\AppData\Roaming\Quest Software Import them to %USERPROFILE%\AppData\Roaming\Quest Software u...
Wednesday, 2 November 2016

How to Clone Database Manually without any tool such as RMAN?

›
Collect live database paths before start $ ps -ef | grep pmon $ echo $ORACLE_SID $ echo $ORACLE_BASE $ echo $ORACLE_HOME $ . oraenv ...
Tuesday, 1 November 2016

How to configure listener port when it changes (like 1522) ?

›
After listener.ora and tnsnames.ora file changes, only run this command SQL> ALTER SYSTEM SET LOCAL_LISTENER ='(ADDRESS = (PROTOCOL...
Thursday, 27 October 2016

How to configure instant client for Toad?

›
-> unrar .zip file  e.g. C:\oracle_client\12.1.0\client_1 -> add this to "Environment Variables-> Path " That'...

How see latest sql histories from audit view?

›
Check audit paramater SQL> show parameter audit; -- basic options; 'DB','DB,EXTENDED', 'OS', 'NONE' ...
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 CONSTRAIN...
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 -> comple...
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...
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; ...
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 obj...
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 ...