SQL Developer 4 on OSX (10.8) - How to Connect to SQL Server

Installing SQL Developer 4 on OSX is pretty straightforward. The only issue I had was that it wasn't picking up on updated version of Java and was giving me a error upon launch. 

That was a pretty easy fix - you just need to update the SqlDeveloper.conf file with the path. On a OSX (10.8.5) - the path will be something like:

SetJavaHome /Library/Java/JavaVirtualMachines/1.7.0.jdk/Contents/Home 

To add drivers for MS SQL Server, download and install the add in: jtds-1.3.1.jar (not 1.2) following these instructions for Third-Party Drivers:

http://www.oracle.com/technetwork/developer-tools/sql-developer/thirdparty-095608.html

Get 1.3 here:  http://sourceforge.net/projects/jtds/files/jtds/1.3.1/

Download and extract to a permanent folder follow instructions on adding into the Application above.

Here's the part that seems to elude most users - especially if you are connecting using Windows Authentication. The GUI panel just doesn't seem to work. 

On a OSX - you will also need to modify the "connections.xml" file by adding the DOMAIN value such as:

<StringRefAddr addrType="customUrl">

   <Contents>jdbc:jtds:sqlserver://Server_FQDN:1433;instance=<instance_name>;domain=<domain_name>/</Contents>

This folder is hidden and is located at:

/Users/<user_name>/.sqldeveloper/system4.0.1.14.48/o.jdeveloper.db.connection.12.1.3.2.41.140207.1351/

Note: To access hidden files, navigate to /Users/<username> and the click the "Go" top menu drop-down and select "Go to folder..."  Type ".sqldeveloper" (without quotes) and then follow the path from there. Directories that start with "." are hidden by default.

Once the above is saved, just try connecting from the main Connections Tree by right-click the connection and choose "Connect" 

Note: To see what Java version you are running in OSX you can issue this at the cmd line:

/Library/Internet/Plug-Ins/JavaAppletPlugin.plugin/Contents/Home/bin/java -version

java version "1.7.0_51"

Java(TM) SE Runtime Environment (build 1.7.0_51-b13)

Java HotSpot(TM) 64-Bit Server VM (build 24.51-b03, mixed mode)

Table Information - including: rows counts, column counts and size

The script below can be run to get a Row Count (from Stats), Column Count and size (in MEG) for all Tables in a Schema:

select 
   a.table_name, 
   a.num_rows ROW_count_FROM_STATS,
   b.col_count,
   C.MEG
from 
   dba_tables a, (select TABLE_NAME, count(column_name) as col_count from dba_tab_cols where owner='&owner' GROUP BY TABLE_NAME
ORDER BY TABLE_NAME) b,
(SELECT
   owner, table_name, TRUNC(sum(bytes)/1024/1024) Meg
FROM
(SELECT segment_name table_name, owner, bytes
 FROM dba_segments
 WHERE segment_type = 'TABLE' and owner = '&owner'
 UNION ALL
 SELECT i.table_name, i.owner, s.bytes
 FROM dba_indexes i, dba_segments s
 WHERE s.segment_name = i.index_name
 AND   s.owner = i.owner
 AND   s.segment_type = 'INDEX'
 UNION ALL
 SELECT l.table_name, l.owner, s.bytes
 FROM dba_lobs l, dba_segments s
 WHERE s.segment_name = l.segment_name
 AND   s.owner = l.owner
 AND   s.segment_type = 'LOBSEGMENT'
 UNION ALL
 SELECT l.table_name, l.owner, s.bytes
 FROM dba_lobs l, dba_segments s
 WHERE s.segment_name = l.index_name
 AND   s.owner = l.owner
 AND   s.segment_type = 'LOBINDEX')
WHERE owner in UPPER('&owner')
GROUP BY table_name, owner
HAVING SUM(bytes)/1024/1024 > 5  /* Ignore really small tables */
ORDER BY SUM(bytes) desc) C
where 
   a.owner = '&owner'
   and A.TABLE_NAME = b.table_name(+)
   AND A.TABLE_NAME = C.TABLE_NAME(+)
order by 
   a.table_name;

Proxy User in Oracle

If you find yourself with the requirement to allow a user to create objects in another user's schema, you may want to take a look at Proxy User functionality.

As a test, I created a new user (abtest) and, as expected, was not able to create an object in my regular (aballo) Schema. You can perhaps grant ‘EXECUTE PROCEDURE’ and do the DDL in the statement (which is messy)….

I then tried logging is as “ABTEST” as a Proxy User for “ABALLO” and this DID appear to work. A test is below…  maybe this will work for your requirements? This is also useful when you don't want to give out a particular user's password.

16:43:11 ODST> create user abtest

16:43:31   2  identified by pwd4ab

16:43:44   3  default tablespace users

16:44:35   4  temporary tablespace temp_group1

16:45:11   5  quota 1m on users;

User created.

17:00:48 ODST> grant create session,create table to abtest;

Grant succeeded.

17:00:53 ODST> connect abtest/pwd4ab@odst;

Connected.

17:01:46 ODST> CREATE TABLE ABALLO.T2

17:01:47   2  (

17:01:47   3    N1  NUMBER,

17:01:47   4    N2  NUMBER

17:01:47   5  )

17:01:47   6  TABLESPACE USERS

17:01:47   7  RESULT_CACHE (MODE DEFAULT)

17:01:47   8  PCTUSED    0

17:01:47   9  PCTFREE    1

17:01:47  10  INITRANS   1

17:01:47  11  MAXTRANS   255

17:01:47  12  STORAGE    (

17:01:47  13              PCTINCREASE      0

17:01:47  14              BUFFER_POOL      DEFAULT

17:01:47  15              FLASH_CACHE      DEFAULT

17:01:47  16              CELL_FLASH_CACHE DEFAULT

17:01:47  17             )

17:01:47  18  LOGGING

17:01:47  19  NOCOMPRESS

17:01:47  20  NOCACHE

17:01:47  21  NOPARALLEL

17:01:47  22  MONITORING;

CREATE TABLE ABALLO.T2

*

ERROR at line 1:

ORA-01031: insufficient privileges

17:13:02 ODST> connect ###mgr/##########@odst

Connected.

17:24:39 ODST> alter user aballo grant connect through abtest;

User altered.

17:28:44 ODST> connect abtest[aballo]/pwd4ab@odst;

Connected.

17:29:22 ODST> CREATE TABLE ABALLO.T2

17:29:27   2  (

17:29:27   3    N1  NUMBER,

17:29:27   4    N2  NUMBER

17:29:27   5  )

17:29:27   6  TABLESPACE USERS

17:29:27   7  RESULT_CACHE (MODE DEFAULT)

17:29:27   8  PCTUSED    0

17:29:27   9  PCTFREE    1

17:29:27  10  INITRANS   1

17:29:27  11  MAXTRANS   255

17:29:27  12  STORAGE    (

17:29:27  13              PCTINCREASE      0

17:29:27  14              BUFFER_POOL      DEFAULT

17:29:27  15              FLASH_CACHE      DEFAULT

17:29:27  16              CELL_FLASH_CACHE DEFAULT

17:29:27  17             )

17:29:27  18  LOGGING

17:29:27  19  NOCOMPRESS

17:29:27  20  NOCACHE

17:29:27  21  NOPARALLEL

17:29:27  22  MONITORING;

Table created.

17:51:44 ODST> select user from dual;

USER

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

ABALLO

17:51:59 ODST> select sys_context('userenv','proxy_user') from dual;

SYS_CONTEXT('USERENV','PROXY_USER')

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

ABTEST

17:52:05 ODST>

Another way to view current Proxy Users logged into the instance:


SELECT *
  FROM v$session JOIN V$SESSION_CONNECT_INFO USING (sid, serial#)
 WHERE authentication_type = 'PROXY'
 and network_service_banner = 'TCP/IP NT Protocol Adapter for Linux: Version 12.1.0.1.0 - Production'