Recent content by mark06

Here we go ... 1. Create a logical directory (connected as SYSTEM) in Oracle using ... CREATE DIRECTORY <DIRNAME> AS '<OSPATH>'; (don't include a '/' or a '\' at the end of the OS path) 2. Grant read access to the directory to the 'load user' ... GRANT READ ON DIRECTORY <DIRNAME> TO...

I have a table ... ID TXT 1 dogfood 1 catfood 1 catfood 1 catfood 2 dogfood 3 dogfood 3 <null> 4 <null> 5 dogfood 5 dogfood 5 dogfood and I want it to produce ... ID TXT 1 mixed 2 dogfood 3 mixed 4 <null> 5 dogfood (in other words, if all txt values are the same for an id...

Something like the following should work ... select <mydata> from ( select <fulldataselection> from <srctable> order by <sortclause> ) subq where subq.rownum <=20; This obviously takes a performance hit on large tables, but anything less than 5,000 records should be quick...

Don't know what version of Oracle you're using and I'm by no means a Rollback Segment 'guru', but you could try setting the OPTIMAL parameter which causes automatic shrinking (when I don't know!), or you could use 'alter rollback segment <rbs> shrink to 10M (for example)'. A useful view is...

You're most welcome. I need to point out that the use of v$open_cursors means that some uncommitted transactions may have been paged out of memory - nothing's perfect in this world. As an afterthought you may find the following useful to show current locks on the database (it can take some time...

Try this ... (require DBA privileges) column username format a20 column logon_time format a11 column load_time format a20 set recsep off set lines 140 break on username on logon_time on sid on load_time select substr(nvl(s.username,'Background'),1,20) username...

If you are using Oracle, there is a DBMS_RANDOM utility that may be useful.

I've seen Microsoft's distributed COM stuff on the same machine as SQL Server 7 cause problems (ie. they appeared to be incompatible).

Are you using multiple Oracle Homes? Oracle 8.1.5 or 8.1.6 with Oracle 8.0 can cause 'issues'.

If you explicitly define storage for your CLOB columns, make sure that initial_extent is the same as next_extent (I have pctincrease of 0), otherwise you'll find a HUGE index for the CLOB will be created. Don't know if this is addressed in 8.1.6. PS. Anybody got any tips on improving the...

Oracle used to licence 'Trusted Oracle' separately which I seem to recall added extensions to the RDBMS to provide row level security. May be a lower long term overhead in terms of maintenance if this is still an active product.<br> <br> Mark.

Oops - remember to put commas between the SQL Server stored procedure parameters.

Only tried with Oracle 7.3.4 and SQL Server 7.0. I'm sure it'll work with Oracle 8 as well, since all the translation is done through ODBC. <br> <br> Mark.

Mike,<br> <br> I had a battle setting this up, but got there in the end. Haven't got the script in front of me, but ...<br> <br> a) sp_addlinkedserver @server='OracleLinkedDB' @srvproduct='Oracle' @provider='MSDASQL' @provstr='DSN=&lt;OracleODBCDSN&gt;;UID=;PWD=;'<br> <br> b)...

I got off my lazy backside and sorted this out myself.<br> <br> a) I used sp_configure to compare the run_value of both databases and found that 'remote query timeout (s)' was different - it ought to have been zero on the server but was in fact 10.<br> <br> b) Solution :<br> <br> sp_configure...