Oracle

Oracle – Table Import Progress

Posted by 

select substr(sql_text,instr(sql_text,'INTO "'),30) table_name,
 rows_processed,
 round((sysdate-to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60,1) minutes,
 trunc(rows_processed/((sysdate-to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60)) rows_per_min
 from sys.v_$sqlarea
 where sql_text like 'INSERT %INTO "%'
 and command_type = 2
 and open_versions > 0;select substr(sql_text,instr(sql_text,'INTO "'),30) table_name,
 rows_processed,
 round((sysdate-to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60,1) minutes,
 trunc(rows_processed/((sysdate-to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60)) rows_per_min
 from sys.v_$sqlarea
 where sql_text like 'INSERT %INTO "%'
 and command_type = 2
 and open_versions > 0;
 
 
 
 Select
 a.sid,
 a.serial#,
 b.status,
 a.opname,
 to_char(a.START_TIME,' dd-Mon-YYYY HH24:mi:ss') START_TIME,
 to_char(a.LAST_UPDATE_TIME,' dd-Mon-YYYY HH24:mi:ss') LAST_UPDATE_TIME,
 a.time_remaining as "Time Remaining Sec" ,
 a.time_remaining/60 as "Time Remaining Min",
 a.time_remaining/60/60 as "Time Remaining HR"
 From v$session_longops a, v$session b
 where a.sid = b.sid
 And time_remaining > 0

OPENROWSET – Query Oracle from SQL Server using a linked server

Posted by

Query Oracle from SQL Server using a linked server

This post describes the basics of querying an Oracle database from Microsoft SQL Server.

Oracle data can be queried from SQL Server via a linked server defined in Microsoft SQL Server

Install oracle client on the SQL Server Database Machine

The Oracle client is required to expose the OraOLEDB Oracle provider and for subsequent connections to Oracle databases via OracleNet – Oracle transparent network substrate (tnsnames etc.)</p

Create an entry for the Oracle database in the tnsnames.ora Oracle Net File

ORCL=(DESCRIPTION =  (ADDRESS = (PROTOCOL = TCP)(HOST = oracle_server)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED)   (SERVICE_NAME = ORCL)))

Create a linked server in SQL Server

USE [master]
GO
EXEC master.dbo.sp_addlinkedserver @server = N'ORCL', @srvproduct=N'Oracle', @provider=N'OraOLEDB.Oracle', @datasrc=N'ORCL'
GO
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'ORACLE_ORCL',@useself=N'False',@locallogin=NULL,@rmtuser=N'orcl_user',@rmtpassword='orcl_pwd'
GO

Query the Oracle data

Grant the Oracle user “select any dictionary” privileges for test purposes. oracle data can be queried using “openrowset” or directly in a SQL statement using the linked server

-- using openrowset
select * from openrowset('OraOLEDB.Oracle','ORCL';'orcl_user';'orcl_pwd','select * from dual')

-- direct sql using [&lt;LINKED_SERVER&gt;]..[OWNER].[OBJECT_NAME]
select * from [ORCL]..[SYS].[DUAL]

Provider Properties

SQL queries can be executed “inprocess” or not “inprocess”.

“inprocess” may cause sql server crash if there are issues with the provider, so it is recommended to execute “out of process”. To run “out of process”, permissons are required on the MSDAINITIALIZE DCOM component, to initialize the provider.

Run dcomcnfg from cmd

Navigate to MSDAINITIALIZE component

 

Edit the permissions to allow groups or specific users to use the provider according to your requirements.

Ad-Hoc Distributed Queries

If there is an error reported when the “openrowset” sql statement is executed, as follows:

Msg 15281, Level 16, State 1, Line 3
SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', search for 'Ad Hoc Distributed Queries' in SQL Server Books Online.

The use of ‘Ad Hoc Distributed Queries’ can be enabled by an administrator as decribed im the error message.

sp_configure 'Ad Hoc Distributed Queries',1  
reconfigure

Or use SSMS GUI, right-click on instance, select Facets, navigate to the “Surface Area Configuraion in the Facet drop-down list, and set Facet property “AdHocRemoteQueriesEnabled” to true”

Add User to DCOM Users Group

 

 

Display the SQL statement that a Linux Process is Executing

Posted by 
-- get the process id from Linux (use top or ps)
-- the process id is spid in v$process
select sql_id from v$session where paddr in (select addr from v$process where spid in (4567))

-- put the sql_id from v$session into v$sqlarea/v$sql_text/v$sql
select sql_text from v$sqlarea where sql_id = 'ap3qms77rt67k'
-- this will display the sql that the process is executing

-- In one query
select sql_text
from v$sqlarea
where sql_id = 
(select sql_id from 
 v$session where paddr in 
 (select addr from v$process where spid = 1820)
);