Oracle Technical

Posts about Oracle databases

Oracle – Recyclebin

Posted by

0

This will take into account subsequent sessions
- without the DEFERRED option, the command will fail

alter system set recyclebin=OFF DEFERRED SCOPE=BOTH;

------------------------------------------------------------------------
PURGE RECYCLEBIN;

To remove all dropped objects from the recyclebin (system wide):
PURGE DBA_RECYCLEBIN;

Drop a table without sending it to the recyclebin. Example:
DROP TABLE tab1 PURGE;

Oracle – Sessions using TEMP tablespace

Posted by

0

SELECT   b.TABLESPACE
       , b.segfile#
       , b.segblk#
       , ROUND (  (  ( b.blocks * p.VALUE ) / 1024 / 1024 ), 2 ) size_mb
       , a.SID
       , a.serial#
       , a.username
       , a.osuser
       , a.program
       , a.status
    FROM v$session a
       , v$sort_usage b
       , v$process c
       , v$parameter p
   WHERE p.NAME = 'db_block_size'
     AND a.saddr = b.session_addr
     AND a.paddr = c.addr
ORDER BY b.TABLESPACE
       , b.segfile#
       , b.segblk#
       , b.blocks

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