SQLDeveloper

Using SQL Developer DBA Export Wizard to write PL/SQL DBMS_DATAPUMP exports for you

Simple enough: Go into SQL Developer DBA module Data Pump -> Create Export Wizard and get to the Summary/Finish page and select the “PL/SQL” tab. set scan off set serveroutput on set escape off whenever sqlerror exit DECLARE h1 number; errorvarchar varchar2(100):= ‘ERROR’; tryGetStatus number := 0; begin h1 := dbms_datapump.open (operation => ‘EXPORT’, job_mode …

Using SQL Developer DBA Export Wizard to write PL/SQL DBMS_DATAPUMP exports for you Read More »

SQLDeveloper – on a connect – Got minus one from a read call on an EBS VIS instance

SQLDeveloper on a connect – I received the error: got minus one from a read call on an EBS VIS instance Oracle protects EBS R12 instances by limiting who can connect – it appears by using TCP.VALID_NODE_CHECKING=YES. Login to your VIS host server. cd $ORACLE_HOME/network/admin cd VIS_oel66-79 vi sqlnet.ora change this tcp.validnode_checking = yes to …

SQLDeveloper – on a connect – Got minus one from a read call on an EBS VIS instance Read More »

how far has your dbms_stats.gather_database_stats job progressed?

–alter session set nls_date_format=’dd-mon-yy hh24:mi:ss’; set sqlformat ansiconsole select * from v$session_longops where time_remaining > 0; SID SERIAL# OPNAME SOFAR TOTALWORK UNITS START_TIME LAST_UPDATE_TIME TIME_REMAINING ELAPSED_SECONDS 654 48,837 Gather Database Statistics 1,855 3,286 Objects 23-sep-16 20:05:40 23-sep-16 20:47:05 1,917 2,485 SID SERIAL# OPNAME SOFAR TOTALWORK UNITS START_TIME LAST_UPDATE_TIME TIME_REMAINING ELAPSED_SECONDS 654 48,837 Gather Database Statistics …

how far has your dbms_stats.gather_database_stats job progressed? Read More »

What is Oracle 12c dbms_stats.gather_schema_stats doing by default?

What is 12c dbms_stats.gather_schema_stats doing by default?  It uses predefined dbms_stats preferences.  Here an example of how to show those preferences on 12c – some of the listed preferences below are new to 12c.  Then an example of how to show what a gather_schema_stats call will do for default calls. Here is a call to dbms_stats.gather_schema_stats …

What is Oracle 12c dbms_stats.gather_schema_stats doing by default? Read More »

An Oracle SQLPlus / SQLCL script that generates sql statements then executes them

Here is a easy example of a sql script that generates sql statements and then executes them. Hope it helps.  Mike SET PAGESIZE 0 SET TERMOUT OFF SET TRIMSPOOL ON SPOOL executeStatements.sql SELECT ‘SELECT count(*) from dba_tables where owner=”’||trim(username)||”’;’ from dba_users WHERE ROWNUM < 15; SELECT ‘SELECT count(*) from dba_indexes where owner=”’||trim(username)||”’;’ from dba_users WHERE …

An Oracle SQLPlus / SQLCL script that generates sql statements then executes them Read More »

Oracle SQLCL – Use SQLCL to simply export via SET SQLFORMAT CSV and import via load

Need a very simple way to export Oracle data to CSV format – and import from CSV format? Here is a fairly easy example of doing both sides (import and export) using Oracle’s early adopter “sqlcl” product.  And yes, there are many other ways to accomplish this.  Once your have this method down – the …

Oracle SQLCL – Use SQLCL to simply export via SET SQLFORMAT CSV and import via load Read More »

sqlDeveloper 4.0 no longer uses sqldeveloper.conf to set the java path – now with version 4 product.conf is used to SetJavaHome

Recently solved a mystery that puzzled me a couple of times when installing the newer version 4 of sqlDeveloper in a Windows 10 Pro environment. In the past with version 3 and lower the java path that sqldeveloper used was set by assigning a value to SetJavaHome in the <whatever your path to sqldeveloper>\bin directory …

sqlDeveloper 4.0 no longer uses sqldeveloper.conf to set the java path – now with version 4 product.conf is used to SetJavaHome Read More »