How about using Minus? (I'm a set operator groupie.) It usually performs
well for me, though I've done no detailed analysis.
delete from theTable
where rowid in
(
select rowid from theTable where X is not null
minus
select min(rowid) from theTable where X is not null group by X
) ;
Jack C.
Jose,
In my experience - long ago - managing dates rarely involved Boolean logic,
which is Two Valued Logic, True or False. Instead, they involved MVL
(Multi-Valued Logic) with the most common return value being one of a
bewildering assortment of INDETERMINATE variations, followed by frequent
The 15th of March.
From http://www.infoplease.com/spot/ides1.html :
Kalends (1st day of the month)
Nones (the 7th day in March, May, July, and October; the 5th in the other months)
Ides (the 15th day in March, May, July, and October; the 13th in the other months
Jack C. Applewhite
Database
I've searched TechNet and MetaLink but can't find a downloadable 8i, 9i, anything Oracle Client for Apple Macintosh - OS 9 or OS X. What am I missing? Is it not available? Some docs on MetaLink mention it, but the versions seem related to SQL*Net, not the RDBMS. I'm clueless when it comes to
Raj,
It's Mongolian Barbeque and still there. Now there's even a Mongolian
BBQ North.
Tom,
You're forgetting that:
- In the summer it's hot.
- When it's hot, people wear less clothes to keep cool, sometimes a LOT
less.
- Austin is home to the University of Texas (almost 50,000 students at
peak,
100 deg. F and raining - Happens all over Texas during an afternoon summer
thunderstorm, though the rain usually cools things down a bit.
The wimps in Dallas can't complain about humidity at all. Folks in
Houston certainly can.
Unfortunately, my wife won't let me keep even ONE of the Dixie
David forgot to mention a couple of things about working for AISD:
- We don't wear neckties (well, David does sometimes).
- We get 23 paid Holidays (how does 2 weeks off for Christmas and a week off for Spring Break sound?).
- We have an unlimited site license for Oracle EE. I slap databases out
If MS means Micros**t, our Techs have been applying patches like crazy to
all our Win2k servers. Our production Student Info. database lives on one
of those - it's EE 8.1.7.4 - and I've seen no ill effects. We just had to
find a time to reboot the server so the patches would take effect.
Jack
8.1.7.0 on HP-UX.
Another DBA (really, it wasn't me) forgot which server he was on and deleted the RBS tablespace datafile and all the archived redo logs - on different mount points - of our Production Financials database. No time for the whys of that story or why we don't mirror our archived
I got the 9.2 docs and this is in the Concepts manual:
Dynamic Predicates
The function or package that implements the security policy you create
returns a predicate (a WHERE condition). This predicate controls access as
set out by the policy. Rewritten queries are fully optimized and
Raj,
Thanks. A mention of which FM would've saved me some searching, but I
found it under Introduction to Fine-Grained Access Control / Automatic
Reparse in the Oracle9i Application Developer's Guide - Fundamentals.
I'll have to do more investigation, since those paragraphs don't clearly
RLS doesn't use bind variables? How then does Oracle treat the Application
Context variables that you include in the predicates generated by the
Security Policy functions? If those aren't bind variables then I guess I
don't know what bind variables are.
Please refer me to the documentation on
OK, I went and looked in the 8i Concepts manual. It seems pretty clear
that Application Context variables are used as bind variables. It may have
changed for 9i, but I can't see how or why.
Application Context
Application context facilitates the implementation of fine-grained access
control.
Application Context is used to implement FGAC (aka RLS). You need to read
those sections in the Concepts doc.
Jack C. Applewhite
Database Administrator
Austin Independent School District
Austin, Texas
512.414.9715 (wk)
512.935.5929 (pager)
[EMAIL PROTECTED]
Tim,
As soon as I sent this I saw my mistake. There's got to be one more
table. Doc_Types and Doc_Attributes stay, but Documents gets changed and
Document_Attributes gets created. Actually, if you wanted you could make a
single integer PK column for Doc_Type_Attributes and use that as the FK
Tim,
How about this?
Table Doc_Types
Doc_Type_IDNumberPK
,Doc_Type_Descr VarChar2(100)
Table Doc_Attributes
Doc_Attrib_ID NumberPK
,Doc_Attrib_Descr VarChar2(100)
Table Doc_Type_Attributes (Intersect table of the above
Jared,
Yeah, I'm sure our Developers complain to their boss That dang DBA's
Jacking with my application again. ;-)
Actually, it would only take as long to generate the 50k triggers as it
would to loop through the list of tables in a PL/SQL procedure and
generate/execute the Dynamic SQL
OK, here's what I've found so far.
There is no way at all in Oracle8i (don't know about 9i or 10g) to
enable/disable/affect a User's Role from within a Trigger. Role processing
is automatically disabled in any Definer Rights PL/SQL module. All
Triggers can only be created to execute with
Short form of my question:
How can I enable a Role for a User within a database trigger (owned by another Schema) on a table owned by yet another Schema?
- The M's I've RTF'd indicate that a trigger (and any procedure it calls) can never execute with Invoker's Rights.
- I can't find a way to
Arup,
Thanks for your reply. As I said in my memo, I really want the User's Role to be changed from within a Trigger on a table, not a System Trigger. Actually, it doesn't matter, since no database trigger can be defined with Invoker's Rights. That means that any trigger in which I issue execute
Jonathon,
I've got a query for you that uses all 3 set operators at once!
I wrote it to compare two different versions of our 3rd Party Student
Information System (SASI) in two different databases. We were getting
ready to upgrade Production, having already upgraded a Test instance. The
query
Yes, complicated with as simple a combination as tables and their indexes.
Consider even more variables introduced by using LOBs and interMedia Text.
In a past job, I used to make myself crazy analyzing and placing all the
segments involved with interMedia-indexed CLOBs. Here's what I came up
Ryan,
To make a particular schema the focus for a session:
Alter Session Set Current_Schema = TheSchema ;
Better yet, instead of proliferating the same schema for each client, convert the tables to partitioned tables in a single schema, with each partiion being for a specific client. That way
Ryan,
Segment Exchange is a very easy, instantaneous, method of moving data back and forth between standalone tables and partitioned tables. Transportable Tablespaces can ingest the data into your DB, but then it can be comfortably digested into partitions via Segment Exchange.
Jack C.
Anne,
I use database triggers to capture system events and send me email. I use
the Event Attributes, though I've not captured IP address. My 8.1.7 docs
say that you should use ora_client_ip_address, though it looks like that
Attribute is only available in an After Logon Event, so you'd need
You might consider an interMedia Text index on the column(s) of interest.
The interMedia Fuzzy Search and scoring functionality (and others) may
prove more flexible than Soundex.
Jack C. Applewhite
Database Administrator
Austin Independent School District
Austin, Texas
512.414.9715 (wk)
Rachel,
At a recent past job, under 8.1.6 on Win2k we had tables with out-of-line
CLOB segments of 30,000 extents (1MB each). Every month we dropped one to
make room for another (6 months of CLOB documents online). It always just
took a few seconds for the drop. These were in DMTs.
Later we
Guang,
Is your RESINDEX tablespace a Locally-Managed Tablespace with Uniform
Extents of less than 3 database blocks? If so, that's your problem.
InterMedia indexes consist of some LOB segments and those require extents
of at least 3 database blocks - at least in 8i.
If not, I don't know.
Jack
A Perl client app on Linux can successfully access an 8.1.7.4 DB via a
listener on a non-standard port through a firewall. The Perl developer
figured out where he has to configure the non-standard port# to connect to
the DB.
However, we want this client to access the DB using native Net8
Jeremiah,
Perhaps the fact that my experience was with Personal Oracle 7.3.2.3 on
Win95 explains the hosed database - Win95 not being the best platform, to
say the least.
I'll definitely try it again - on a test database, of course.
BTW, I can't find any way to tell DBV to verify more than one
Dan,
That must be new for 9i, since my 8.1.7 docs say it is an offline
utility. I remember hosing an entire 7.3 test database years ago when I
first ran DBV on it while it was up and open - can't remember the errors,
but the DB was unusable. One of the (too) many times I learned to read the
Ken,
Do all the following as a way to minimize MTTR (Mean Time To Recovery) for
a variety of scenarios and, by the way, they happen to use a lot of disk:
;-)
1. Mirror your online and archived redo logs to multiple drives
2. Put backup copies of drive A' datafiles on drive B (or C) and vice
Lisa,
What error do you get when you try
lsnrctl start
from the command line?
Have you tried oradim from the command line to start the DB service?
Jack C. Applewhite
Database Administrator
Austin Independent School District
Austin, Texas
512.414.9715 (wk)
512.935.5929 (pager)
Craig,
I had to smile when you stated you had 400 tables and were reluctant to
list them in the export or import parfile. Our 3rd party Student
Information system has over 47,000 tables. I frequently do exports and/or
imports with parfiles listing several thousand tables. It's not a problem
John,
A quick check turned up a medium parfile for me - only 1201 tables
listed. The file size is 14KB, so I guess it's not an issue for 8i. Even
on larger parfiles I've never gotten an error - for neither HP-UX nor
Win2k.
Jack C. Applewhite
Database Administrator
Austin Independent School
Ed,
Perhaps you could use the DBMS_SQL Define_Column_Long and Column_Value_Long
procedures to manipulate the Long?
Converting the Long to a LOB using To_LOB function, then manipulating it
with the DBMS_LOB package might do, too.
Jack C. Applewhite
Database Administrator
Austin Independent
Dennis,
Using VSize on a Long results in:
ORA-00997: illegal use of LONG datatype
Just tried in on Trigger_Body in DBA_Triggers. (8.1.7.0.0 on HP-UX).
Jack C. Applewhite
Database Administrator
Austin Independent School District
Austin, Texas
512.414.9715 (wk)
512.935.5929 (pager)
[EMAIL
Guang Mei,
All the DR$ objects are interMedia Index segments - some are tables, some
are indexes, some are IOTs. Check out OTN and/or MetaLink for docs on how
each segment type contributes to the interMedia indexing process. It's
quite interesting.
Jack C. Applewhite
Database Administrator
Rick,
Might there be a Scheduled Task or DBMS_Job that runs shortly after the
load and deletes rows?
Just a thought - such automagic processes have bitten me in the past.
Jack C. Applewhite
Database Administrator
Austin Independent School District
Austin, Texas
512.414.9715 (wk)
512.935.5929
Actually, Interval is evaluated at the beginning of the job according to
the docs.
I've not seen anyone mention the real cause behind DBMS_Job creep. That
is the setting of Job_Queue_Interval which, by default, is 60 seconds. So
your jobs will run 1 minute later each time unless you set
Sean,
In your case the PK would need to be on (Last, ID) in order for the index
to be Local. Otherwise, it's gotta be Global or else you need to change
your partition key.
Jack C. Applewhite
Database Administrator
Austin Independent School District
Austin, Texas
512.414.9715 (wk)
512.935.5929
Bill,
I agree with Michael. You've already got the data in a suitable
datatype. Why move it to a cumbersome, soon-to-be-obsolete datatype? You
can use DBMS_LOB functionality on LOBs, not on Raw.
I'd be so happy if the couple dozen tables in our 3rd party Student
Information system that have
David,
What OS and Oracle version? At least in 8.1.7.0.0 on HPUX that error
message could be the result of a bug - we get it every few weeks on our
production financials database and have to bounce the DB. Flushing the
Shared Pool every night doesn't prevent it. Eventually we'll patch to
Well... I've got to take off here in a bit for our two week Winter
Break (can't call it Chrismas Holiday - gotta be denominationally
neutral), though I *will* get to come in the weekend of the 28th to rebuild
our Student Information database.
A five day Thanksgiving Holiday was hard to get
Lisa,
A couple of years ago, when I was a consultant, I implemented Application
Context and Fine-Grained Access Control, AKA Row Level Security for a
client.
Since it causes a predicate to be appended to the Where clause of every SQL
statement issued against the tables having a Security Policy,
Steve,
It's pretty simple. No need to record anything - see the Order By
Descending in the SQL below.
Here are a batch file and a SQL script I used on an 8.1.7 Standby DB under
Win2k. It ran reliably for months as a Scheduled Task once every hour and
got rid of all applied logs, assuming that
Ron,
Saw your memo and fooled around with Task Scheduler a bit. All it is is
Windows Explorer pointed at the Scheduled Tasks folder in Control Panel.
To back up your Scheduled Tasks, simply single click on one or more of them
(or use the Edit / Select All menu item, then de-select Add
Laura,
Are those really zeros in $34,000.05 or are they letter Os? If so use
Replace. (Beware of letter l being used instead of numeral 1 as well.)
Any leading or trailing spaces? If so use Trim(unit_cost).
Just a couple of quick suggestions.
Jack C. Applewhite
Database Administrator
Rachel,
We have two Third Party apps here for Finance and Student Information that
do ridiculous stuff like this so often, I just immediately jumped into fix
it mode without even questioning. Don't even ask why is our motto.;
-)
Jack C. Applewhite
Database Administrator
Austin Independent
FWIW ... in my last job we stored 13 million CLOB documents (max size of
5KB, stored out-of-line in their own CLOB segments) in an 8.1.7.3 DB under
Win2k (2 CPU HP NetServer with 4GB RAM and twenty-three 36GB drives;
JBOD). The CLOB documents were all interMedia Text indexed.
Contains queries
David,
How about Dynamic SQL in an Instead Of trigger?
Jack C. Applewhite
Database Administrator
Austin Independent School District
Austin, Texas
512.414.9715 (wk)
512.935.5929 (pager)
[EMAIL PROTECTED]
Oracle 8.1.7.0.0 on Win2k (4 CPU, 4GB RAM)
A 3rd Party app. is experiencing very slow performance on one of our
databases. I think I've nailed it down to slow, row-at-a-time inserts.
The same app. performs very fast on another DB with LMTs. After switching
the tables and indexes in the slow DB
Rich,
If you've got the disk space, do it while the DB is up. Much simpler.
Create a new Temporary LMT - call it NewTemp - with the appropriate
tempfiles.
Switch everyone to NewTemp by spooling and executing this and running the
spooled file:
Select 'Alter User ' || UserName || ' Temporary
Seema,
Our production Student Information database (8.1.7 under Win2k) has 40,000
tables and 60,000 indexes. It's a third party app designed for dBaseIV -
go ahead and laugh, we do all the time (when we're not crying). Anyhow, we
have to regularly clone out the data to a couple of other
Sam,
Autoextend caused tablespace corruption for me once, but it was over 5
years ago with Personal Oracle 7.3.2.3 on Win95 - not the most reliable OS
that Oracle has ever ported to. ;-)
We have several 8.1.7 databases here, on both Win2k and HPUX. Autoextend,
even on System, has caused no
Rich,
Several years ago in the old Database Programming and Design magazine (a
really useful publication, IMHO - too bad it's gone), C. J. Date and
another database guru (I can't remember his name) carried on a debate that
lasted several months about the badness (Date) vs goodness (the other
Rich,
I piqued my own curiosity and looked at Database Programming and Design
On-Line. Below is a link to a lengthy response (1995) by Date and others
to Tom Johnston's article defending MVL (Multi-Valued Logic). Plenty to
chew on.
http://www.dbpd.com/vault/dateresp.htm
Jack C. Applewhite
If you can modify the application code, how about incrementing a sequence
for each row inserted? Not to hamper performance with a Select
MySequence.NextVal From Dual you could select from - I vaguely remember
this from a thread a few weeks ago - x$dual (or something like that - I
can't remember
John,
Here's the syntax from the Supplied PL/SQL Packages doc - refer to it for
details:
TABLESPACE_MIGRATE_TO_LOCAL(tablespace_name, allocation_unit, relative_fno)
Jack C. Applewhite
Database Administrator
Austin Independent School District
Austin, Texas
512.414.9715
[EMAIL PROTECTED]
Dave,
Your Oracle instance won't have a problem with it.
Your Oracle Sales Rep. will be absolutely delighted, because you will owe
them more $$$ for the extra CPU license!
Better check with your CIO (or equivalent) to see if your organization
really wants to add that CPU.
Jack C.
Arun,
Here are a couple of files (a .bat and .sql) that let me maintain a
constant number of Archived Redo Logs online.
The first batch file executes SQL*Plus to produce two other batch files to
delete the excess logs and move some others, maintaining, in this case
about 450 logs. It ran every
61 matches
Mail list logo
