Links
Home
Oracle DBA Forum
Frequent Oracle Errors
TNS:could not resolve the connect identifier specified
Backtrace message unwound by exceptions
invalid identifier
PL/SQL compilation error
internal error
missing expression
table or view does not exist
end-of-file on communication channel
TNS:listener unknown in connect descriptor
insufficient privileges
PL/SQL: numeric or value error string
TNS:protocol adapter error
ORACLE not available
target host or object does not exist
invalid number
unable to allocate string bytes of shared memory
resource busy and acquire with NOWAIT specified
error occurred at recursive SQL level string
ORACLE initialization or shutdown in progress
archiver error. Connect internal only, until freed
snapshot too old
unable to extend temp segment by string in tablespace
Credential retrieval failed
missing or invalid option
invalid username/password; logon denied
unable to create INITIAL extent for segment
out of process memory when trying to allocate string bytes
shared memory realm does not exist
cannot insert NULL
TNS:unable to connect to destination
remote database not found'>ora-02019
exception encountered: core dump
inconsistent datatypes
no data found
TNS:operation timed out
PL/SQL: could not find program
existing state of packages has been discarded
maximum number of processes exceeded
error signaled in parallel query server
ORACLE instance terminated. Disconnection forced
TNS:packet writer failure
see ORA-12699
missing right parenthesis
name is already used by an existing object
cannot identify/lock data file
invalid file operation
quoted string not properly terminated
-none-

-none-

2004-08-31       - By -not available-

Reply:     <<     181     182     183     184     185     186     187     188     189     190     >>  

1) the tables are "small " (say, e.g. partitions under about 1G each)
2) you are willing to delete from about-to-be-archived partitions
   before dropping them, in order to preserve the globals. (Not an
issue
   in 9i, but haven 't tested this in battle. Looks great on marketing
paper.)

I haven 't found a single case where a well-thought app absolutely needed
globals (IMHO, the "I need my PK, Waaaah " argument is spurious) on a
partitioned
table.

Having said that, it 's been a real pleasure having the partitions to work
with.

Final thought: You might want to consider separate tspaces for your really
large
part_tables and their indexes. Could come in handy if you decide to do the
transportable tablespace shuffle in the future. (Of course, globals make
this
harder, as well) .

JMTC,

- RM



-- --Original Message-- --
From: Gogala, Mladen [mailto:Mladen.Gogala@(protected)]
Sent: Tuesday, August 31, 2004 1:31 PM
To: 'oracle-l@(protected) '
Subject: RE: Partitioning opinion


Harvinder, partitions are tables in disguise. In Oracle7 there were things
called "partition views ". In Oracle8, there was a logical leap with respect
to the notion of "segment ". Before Oracle8 there was 1-to-1 correspondence
( "bijective mapping ") between tables and data segments and between indexes
and index segments. That is no longer true. With partitioning option, the
bijective mapping is broken and a table can have more then a single segment.
Why am I telling you all that? If you take a look at traces generated by the
event 10053, you 'll see that partition pruning is done first, then the
access path to the particular partition(s) is determined. If an optimizer
decides that the desired result lies within a single partition, and it will
consider all indexes it has on that partition to resolve the query,
regardless whether they 're global or local. Local indexes have one B-tree
for each partition, which (hopefully) has fewer entries then then the global
B-tree index and is of fewer degree (level). Local indexes are easier to
search because of the sheer size. The dark secret
of the partitioning option is that partitions are tables in disguise, and
should be treated as such.

--
Mladen Gogala
A & E TV Network
Ext. 1216


> -- --Original Message-- --
> From: Harvinder Singh [mailto:Harvinder.Singh@(protected)]
> Sent: Tuesday, August 31, 2004 1:02 PM
> To: oracle-l@(protected)
> Subject: Partitioning opinion
>
>
> Hi,
>
> We have a table having 14 columns and contain historical data
> and we are planning to implement partitioning so that we will
> keep only 12 months data online and purge the old partition
> every month. We will be using Range partitioning on Date
> column name Interv and have primary key on (id,Interv). Data
> is never updated and only deleted for archiving that we are
> planning to do as drop partition so for most of the time only
> inserts will go against this table and few Selects. For
> performance of select we have to add 2 more non-unique global
> indexes. So the schema will look like: Primary key index on
> (id,Interv) Does non-prefix local indexes be Ok or we should
> change the order of PK to (interv,id) to have prefix local
> index? Non-unique index on 3 columns Non-unique index on 3
> columns Should we partition these global non-unique indexes
> or it does not matter since partitions will not be used by
> optimizer for pruning?
>
> Thanks
> --Harvinder
>
>
>
>
> -- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> -- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ------
> To unsubscribe send email to: oracle-l-request@(protected)
> put 'unsubscribe ' in the subject line.
> --
> Archives are at http://www.freelists.org/archives/oracle-l/
> FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
> -- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- --
>
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ------
To unsubscribe send email to: oracle-l-request@(protected) put
'unsubscribe ' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- --


-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ------
To unsubscribe send email to: oracle-l-request@(protected)
put 'unsubscribe ' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- --