Oracle SQL to show all columns for all tables in particular schema

Oracle SQL to show all columns for all tables in particular schema:

SELECT   table_name, SUBSTR (MAX (all_columns), 2) all_columns
       FROM (SELECT     table_name,
                        SYS_CONNECT_BY_PATH (column_name, ',') all_columns
                   FROM (SELECT table_name, column_name,
                                ROW_NUMBER () OVER (PARTITION BY table_name ORDER BY column_id)
                                                                    column_no
                           FROM all_tab_columns c
                          WHERE c.owner = 'YOUR_SCHEMA'
                            --AND column_name NOT IN ('OLD_', 'DEP_')
                            )
             CONNECT BY PRIOR table_name = table_name
                    AND PRIOR column_no = column_no - 1
             START WITH column_no = 1)
   GROUP BY table_name;


output is like:
TABLE_NAME | ALL_COLUMNS
------------------------------------------------- 
FLAGS            | ID,VALUE,LASTUPD
MESSAGES    | DB_TIMESTAMP,PRIORITY,MESSAGE,DATASETS,IDS,CODE

Oracle listener is stopped unexpectedly on Xubuntu 14.04

Oracle listener is always launched on startup, I did not nothing special to stop it, but during work I found that listener is not started ....

15:01 $ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 24-MAR-2015 15:01:40

Copyright (c) 1991, 2011, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC_FOR_XE)))
TNS-12541: TNS:no listener
 TNS-12560: TNS:protocol adapter error
  TNS-00511: No listener
   Linux Error: 111: Connection refused
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=rivanov)(PORT=1521)))
TNS-12541: TNS:no listener
 TNS-12560: TNS:protocol adapter error
  TNS-00511: No listener
   Linux Error: 111: Connection refused



15:01 $ lsnrctl start

LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 24-MAR-2015 15:12:35

Copyright (c) 1991, 2011, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC_FOR_XE)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.2.0 - Production
Start Date                24-MAR-2015 15:02:20
Uptime                    0 days 0 hr. 10 min. 15 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Default Service           XE
Listener Parameter File   /u01/app/oracle/product/11.2.0/xe/network/admin/listener.ora
Listener Log File         /u01/app/oracle/product/11.2.0/xe/log/diag/tnslsnr/vdu-rivanov/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC_FOR_XE)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rivanov)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rivanov)(PORT=8088))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "XE" has 1 instance(s).
  Instance "XE", status READY, has 1 handler(s) for this service...
Service "XEXDB" has 1 instance(s).
  Instance "XE", status READY, has 1 handler(s) for this service...
The command completed successfully


BUT SQLDeveloper still have no ability to connect to local instance.
Java still cannot connect to ORACLE XE on "mvn clean test"


15:10 $ sudo /etc/init.d/oracle-xe restart

after that command all works fine

How to remove trailing zeros from csv files

I had a task to do extraction from Oracle DB to CSV files and then remove all trailing zeros at last column.

Trim trailing zeros from all file:

ls $OUT_PATH/*.csv | while read file; do
   sed -i 's/[ ]*$//;s/\.00$//;s/\.0$//;s/\(\.[0-9]\)0$/\1/' ${file}
done

Script that do extraction to file by sqlplus:

Bash script:

#!/usr/bin/env bash

CONNECT="user/password@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=SERVER)(Port=1521))(CONNECT_DATA=(SID=my_sid)))"

sqlplus $CONNECT @extract.sql result.csv

SQL file (extract.sql):

set echo off
set feedback off
set verify off
set pagesize 0
set head off

spool '&&1'
select /*csv*/ name||','||TO_CHAR(value,'fm999G999G990D00') from my_table;

Explanation why Oracle can not do human readable formatting for decimal numbers:

Require a Number Format Mask to show leading zeros on decimals
Number format (trailing zeros)

Example of orastat usage to find problematic SQL on Oracle server

In Short:

orastat -ser | egrep -v INACT\|BG

# grab PID number of process , for example 9

orastat -sal 9

Here is logs of steps:

[oracle@server ~]$ orastat -ser | egrep -v INACT\|BG
2013/11/07-17:56:50 orastat | oraver=11.2. oraver_short=11.2 oraver_num=11.2 sqlcmd=sp arg=-ser arg2=
2013/11/07-17:56:50 orastat | ORACLE_SID=MYSID ORACLE_HOME=/opt/oracle/product/11.2.0.3 - SunOS hana 5.10 Generic_147441-12 i86pc i386 i86pc
2013/11/07-17:56:50 orastat | Version=3.53 Host=hana Company=some-company
2013/11/07-17:56:50 orastat | -ser: Session Details

Session      Ses    Ses      Wait    Wait       DB     OS                              Session             Last      Last                 +-UserCommits-+ Rollback Program
SID-Ser#     Type   Status   Class / Seq        Userid-Userid-PID-SPID-Term@Server     Start Time          Call ET   SQL    SQL_ID           Count   /Sec    Count Identifier
------------ ------ -------- ------------------ -------------------------------------- ------------------- --------- ------ ------------- -------- ------ -------- ------------------------
9,33801      USER   ACTIVE   User_I/O/51809     SCHEMA2-sb-1234-7525-unknown@other-server.rev 2013/11/07-17:10:05     8_Sec SELECT 1wrbrdf8qrrx8        0      ?        0 JDBC_Thin_Client      
482,56061    USER*  ACTIVE   Network/13         SYS-oracle-14955-14956-?@localhost          2013/11/07-17:56:50     0_Sec SELECT 9d688j2s309du        0      ?        0 sqlplus@localhost_(TNS_V1-V3)

2013/11/07-17:56:50 orastat | Count of USER session(s) in ACTIVE status: 2
2013/11/07-17:56:50 orastat | There are 50 total sessions (mysid=482 - denoted by *)

[oracle@server ~]$ orastat -sal 9
2013/11/07-17:57:23 orastat | oraver=11.2. oraver_short=11.2 oraver_num=11.2 sqlcmd=sp arg=-sal arg2=9
2013/11/07-17:57:23 orastat | ORACLE_SID=MYSID ORACLE_HOME=/opt/oracle/product/11.2.0.3 - SunOS hana 5.10 Generic_147441-12 i86pc i386 i86pc
2013/11/07-17:57:23 orastat | Version=3.53 Host=hana Company=some-company

Session SQL:
--------------------------------------------------------------------------------

SELECT
  cc.company_id  .....
WHERE
  cc.start$ <= sysdate and sysdate < cc.end$

--------------------------------------------------------------------------------

Session Current Wait State:

SID    DB-User          WaitType St WTSeq  Tmr WTTimSec Wait Event Details
------ ---------------- -------- -- ------ --- -------- -------------------------------------
9      SCHEMA2           RealWait  A  51976  -1        1 P1Raw=0000000000000021 P2Raw=00000000000233F5 P3Raw=0000000000000001 db file sequential read WAITED SHORT TIME file# 33 block# 144373 blocks 1

Session Stats:

                                                           ------------------------------------------Session------------------------------------------
SID-Ser#  OS:Userid-PID-Server     DB:User    Program       Hours  CPUTm Commit PFBlks TScans  MemSorts DiskSorts Con+BuGet PhysReads RHRat  LokWaitTm
--------- ------------------------ ---------- ------------ ------ ------ ------ ------ ------ --------- --------- --------- --------- ------ ---------
9,33801   sb-1234-other-server SCHEMA2     JDBCThinClie    0.8 277617      0      0  41609       175         0 118634411     12000  100.0         0

2013/11/07-17:57:25 orastat | orastat | -sal Done






[oracle@server ~]$ orastat -wr
2013/11/07-17:57:48 orastat | oraver=11.2. oraver_short=11.2 oraver_num=11.2 sqlcmd=sp arg=-wr arg2=
2013/11/07-17:57:48 orastat | ORACLE_SID=MYSID ORACLE_HOME=/opt/oracle/product/11.2.0.3 - SunOS hana 5.10 Generic_147441-12 i86pc i386 i86pc
2013/11/07-17:57:48 orastat | Version=3.53 Host=hana Company=some-company
2013/11/07-17:57:48 orastat | -wr: Wait Reasons - By User and Event Type (Idle Waits Excluded)

DB User    Wait Event Type                          Sessions Waiting
---------- ---------------------------------------- ----------------
SCHEMA2     db file sequential read                                 1

2013/11/07-17:57:48 orastat | Total Session(s) Waiting: 1



[oracle@server ~]$ orastat -wr
2013/11/07-17:57:56 orastat | oraver=11.2. oraver_short=11.2 oraver_num=11.2 sqlcmd=sp arg=-wr arg2=
2013/11/07-17:57:56 orastat | ORACLE_SID=MYSID ORACLE_HOME=/opt/oracle/product/11.2.0.3 - SunOS hana 5.10 Generic_147441-12 i86pc i386 i86pc
2013/11/07-17:57:56 orastat | Version=3.53 Host=hana Company=some-company
2013/11/07-17:57:56 orastat | -wr: Wait Reasons - By User and Event Type (Idle Waits Excluded)

DB User    Wait Event Type                          Sessions Waiting
---------- ---------------------------------------- ----------------
SCHEMA2     db file sequential read                                 1

So problem is found, inadequate sequential read that was caused by problems with missed oracle statistics recalculation.

Force Oracle to recalculate execution plan for a table

Steps to force Oracle to recalculate execution plan for tables:

EXEC DBMS_STATS.GATHER_TABLE_STATS('<Schema_name>','<Table_name>'); 

-- deprecated approach

ANALYZE table <SCHEMA_NAME>.<TABLE_NAME> compute statistics;

and, DBMS_STATS.GATHER_TABLE_STATS is preferable (source: link, oracle-link)

Connect from to Oracle DB from Groovy

http://www.javaworld.com/community/node/2886

For Ubuntu 12.04:

groovy -cp /u01/app/oracle/product/11.2.0/xe/jdbc/lib/ojdbc6.jar toOracle.groovy

content of toOracle.groovy:

import groovy.sql.Sql

sql = Sql.newInstance("jdbc:oracle:thin:@localhost:1521:xe", "user_schema", "password",
"oracle.jdbc.pool.OracleDataSource")
sql.eachRow("SELECT id FROM user_schema.table_name")
{
println "The employee's name is ${it.id}."
}

Filter out all Oracle system schema from SqlDeveloper

http://www.adp-gmbh.ch/ora/misc/known_schemas.html

grep "<h2" known_schemas.html | cut -d '<' -f 2 | cut -d '>' -f 2
+ few schemas see at tail of list (link).

#!/usr/bin/env groovy

def stringList = [ "anonymous", "apex_public_user", "apex_030200", "appqossys", "bi", "ctxsys", "dbsnmp", "dip", "dmsys", "exfsys", "flows_XXXXXX", "hr", "ix", "lbacsys", "mddata", "mdsys", "mgmt_view", "odm", "odm_mtr", "oe", "olapsys", "oracle_ocm", "orddata", "ordplugins", "ordsys", "outln", "pm", "scott", "sh", "si_informtn_schema", "spatial_csw_admin_usr", "spatial_wfs_admin_usr", "sys", "sysman", "system", "tracesrv", "mtssys", "oaspublic", "olapsys", "owbsys", "owbsys_audit", "websys", "wk_proxy", "wksys", "wk_test", "wmsys", "xdb", "OSE\$HTTP\$ADMIN", "AURORA\$JIS\$UTILITY\$", "AURORA\$ORB\$UNAUTHENTICATED", "XS$NULL", "TRACESVR", "PERFSTAT" ];

stringList.each() { print """          <hash>
            <value n="ARG_CASE" v="false"/>
            <value n="ARG_NAME" v="USERNAME"/>
            <value n="ARG_PRED" v="NOT_LIKE"/>
            <list n="ARG_VALS">
               <string v="${it}"/>
            </list>
         </hash>
""" }; println "";

Result search in file:///home/USER/.sqldeveloper/system3.2.09.30/o.sqldeveloper.11.2.0.9.30/product-preferences.xml

and substitute block

Oracle recompile invalid objects

SQL to select all invalid objects(that have errors or need to be just recompiled because Oracle is lazy):
select
   object_type ,
   owner,
   object_name
from
   dba_objects
where
   status != 'VALID';
 
SQL to grab objects and try to recompile them to fileter out that have Errors and object that just invalidated by Oracle for any internal reason:
select
   'ALTER ' || object_type || ' ' ||
   owner       || '.' ||
   object_name || ' COMPILE;'
from
   dba_objects
where
   status != 'VALID'
   and (object_type='VIEW'
   or object_type='MATERIALIZED VIEW'
   or object_type='TRIGGER'
   or object_type='FUNCTION'
   or object_type='PROCEDURE'
   or object_type='PACKAGE'
);

select
   'ALTER PUBLIC ' || object_type || ' ' ||
   object_name || ' COMPILE;'
from
   dba_objects
where
   status != 'VALID'
   and object_type='SYNONYM'
   and owner='PUBLIC';

select
   'ALTER ' || object_type || ' ' || OWNER || '.' ||
   object_name || ' COMPILE;'
from
   dba_objects
where
   status != 'VALID'
   and object_type='SYNONYM'
   ;

-- SYNONYMs could be compiled only as sysdba , so do it by "sqlplus sys/oraclexe as sysdba"

select
   'ALTER ' || object_type || ' ' ||
   owner       || '.' ||
   object_name || ' COMPILE;'
from
   dba_objects
where
   status != 'VALID'
   and object_type='SYNONYM'
   and owner<>'PUBLIC';
 
select
   'ALTER PACKAGE' || ' ' ||
   owner       || '.' ||
   object_name || ' COMPILE BODY;'
from
   dba_objects
where
   status != 'VALID'
   and object_type='PACKAGE BODY';

SQL Developer Exchange: feature requests

SQL Developer Exchange:
 https://apex.oracle.com/pls/apex/f?p=43135:1:6863031674522

Please vote for "Allow connection to PostgreSQL", link
Please vote for "git support", link

I opened proposal "Allow search in SQL tab for Table":
https://apex.oracle.com/pls/apex/f?p=43135:7:7880665214684::NO:RP,7:P7_ID:34801