[Oracle-l] Re: dba_extents vs dba_segments

While I think I know what you are referring to, I am not sure.

Perhaps after reviewing this: *http://tinyurl.com/how-to-ask-questions

*... you would include a reproducible example and point out the
exact data that you believe is in error.

Version and platform information wouldn't hurt either.

Jared

Review Metalink notes:

463101.1 "HOW TO DISCOVER AND FIX THE MISMATCH BETWEEN DBA_SEGMENTS

AND DBA_EXTENTS"

271030.1 "Difference between BYTES in DBA_SEGMENTS and SUM(BYTES) in
DBA_EXTENTS"

There might be more or newer notes as well... lots of forum threads too...

genegurevich_at_discover.com wrote:

How much is the difference?
Is this a uniform extent locally managed tablespace with big uniform extent
and having big objects having more than 1 million extents? If so then there
is a bug for that.

Hi everybody:

Thank you for your responses. I will take a look at the documents that you
referred me to.

I was under impression that others have surely stumbled upon this and
therefore decided not to put any examples
into my Email. However, here is what I see:

SQL> select sum(bytes/1024)/1024 from dba_Segments where tablespace_name=

'PCM_D_01';

SUM(BYTES/1024)/1024

696300

SQL> ch /Segments/extents
1* select sum(bytes/1024)/1024 from dba_extents where tablespace_name=

'PCM_D_01'

SQL> /

SUM(BYTES/1024)/1024

471100

As you see the difference is quite significant and the only change in my
SQL was going from dba_segments to
dba_extents. The tablespace is a LMTS with uniform extens. The max number
of extents in a single segment is
441.

thank you

Gene Gurevich

Please consider the environment before printing this email.

Hi Gene,

Well, the discrepancy between the views is due to Oracle 10g's new feature of Recyclebin.
Some time back, I have blogged on this issue, you may read it here:

http://momendba.blogspot.com/2007/12/inconsistency-between-dbadatafiles.html

Lets reproduce the same:

SQL> conn / as sysdba
Connected.
SQL> select sum(bytes)/1024/1024 from dba_segments where tablespace_name = 'TEST';

SUM(BYTES)/1024/1024

31.1875

1 row selected.

SQL> select sum(bytes)/1024/1024 from dba_extents where tablespace_name = 'TEST';

SUM(BYTES)/1024/1024

31.1875

1 row selected.

Currently, DBA_SEGMENTS & DBA_EXTENTS report the same amount of usage "31.1875"
I connect to a TEST schema and create a table and later drop it:

SQL> conn test/test
Connected.
SQL> create table big_table as select * from all_objects;

Table created.

SQL> insert into big_table select * from big_table;

32617 rows created.

SQL> insert into big_table select * from big_table;

65234 rows created.

SQL> commit;

Commit complete.

SQL> drop table big_table;

Table dropped.

Connect as SYS (well, you may also query using USER_*) and run the previous queries again:

SQL> conn / as sysdba
Connected.
SQL> select sum(bytes)/1024/1024 from dba_segments where tablespace_name = 'TEST';

SUM(BYTES)/1024/1024

46.1875

1 row selected.

SQL> select sum(bytes)/1024/1024 from dba_extents where tablespace_name = 'TEST';

SUM(BYTES)/1024/1024

31.1875

1 row selected.

Voila, DBA_SEGMENTS reports a higher space usage. Ok, connect as TEST and check whats there in your recyclebin:

SQL> conn test/test
Connected.
SQL> show recyclebin
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME

---------------- ------------------------------ ------------ -------------------
BIG_TABLE        BIN$+Ml+aeo2R8eXlKWaSyTBvw==$0 TABLE        2009-03-12:14:18:03

SQL> purge recyclebin;

Recyclebin purged.
SQL> show recyclebin
SQL>

Hmm, Oracle has moved the dropped table into user's recyclebin. We can throw it out using the PURGE command.

Check the space usage again:

SQL> conn /as sysdba
Connected.
SQL> select sum(bytes)/1024/1024 from dba_segments where tablespace_name = 'TEST';

SUM(BYTES)/1024/1024

31.1875

1 row selected.

SQL> select sum(bytes)/1024/1024 from dba_extents where tablespace_name = 'TEST';

SUM(BYTES)/1024/1024

31.1875

1 row selected.

SQL>

Yep, the space reported is correct. :)

Wondering, Oracle Support took a week and hasn't replied yet.

Regards

Asif Momen
http://momendba.blogspot.com

On Wed, 3/11/09, genegurevich_at_discover.com wrote:
From: genegurevich_at_discover.com
Subject: Re: dba_extents vs dba_segments
To: [email protected]
Cc: hkchital_at_singnet.com.sg, jkstill_at_gmail.com, tim_at_evdbt.com, ajaycajay_at_gmail.com
Date: Wednesday, March 11, 2009, 1:28 PM

Hi everybody:

Thank you for your responses. I will take a look at the documents that you
referred me to.

I was under impression that others have surely stumbled upon this and
therefore decided not to put any examples
into my Email. However, here is what I see:

SQL> select sum(bytes/1024)/1024 from dba_Segments where tablespace_name=
'PCM_D_01';

SUM(BYTES/1024)/1024

696300

SQL> ch /Segments/extents
1* select sum(bytes/1024)/1024 from dba_extents where tablespace_name=
'PCM_D_01'

SQL> /

SUM(BYTES/1024)/1024

471100

As you see the difference is quite significant and the only change in my
SQL was going from dba_segments to
dba_extents. The tablespace is a LMTS with uniform extens. The max number
of extents in a single segment is
441.

thank you

Gene Gurevich

Please consider the environment before printing this email.

Thank you.

Let me check if this is the case

thank you

Gene Gurevich

Asif Momen
To
[email protected],
03/12/2009 06:35 genegurevich_at_discover.com
AM cc
hkchital_at_singnet.com.sg,
jkstill_at_gmail.com, tim_at_evdbt.com,
Please respond to ajaycajay_at_gmail.com
asif_oracle_at_yahoo Subject
.com Re: dba_extents vs dba_segments

Hi Gene,

Well, the discrepancy between the views is due to Oracle 10g's new feature of Recyclebin.
Some time back, I have blogged on this issue, you may read it here:

http://momendba.blogspot.com/2007/12/inconsistency-between-dbadatafiles.html

Lets reproduce the same:

SQL> conn / as sysdba
Connected.
SQL> select sum(bytes)/1024/1024 from dba_segments where tablespace_name = 'TEST';

SUM(BYTES)/1024/1024
--------------------
31.1875

1 row selected.

SQL> select sum(bytes)/1024/1024 from dba_extents where tablespace_name = 'TEST';

SUM(BYTES)/1024/1024
--------------------
31.1875

1 row selected.

Currently, DBA_SEGMENTS & DBA_EXTENTS report the same amount of usage "31.1875"
I connect to a TEST schema and create a table and later drop it:

SQL> conn test/test
Connected.
SQL> create table big_table as select * from all_objects;

Table created.

SQL> insert into big_table select * from big_table;

32617 rows created.

SQL> insert into big_table select * from big_table;

65234 rows created.

SQL> commit;

Commit complete.

SQL> drop table big_table;

Table dropped.

Connect as SYS (well, you may also query using USER_*) and run the previous queries again:

SQL> conn / as sysdba
Connected.
SQL> select sum(bytes)/1024/1024 from dba_segments where tablespace_name = 'TEST';

SUM(BYTES)/1024/1024
--------------------
46.1875

1 row selected.

SQL> select sum(bytes)/1024/1024 from dba_extents where tablespace_name = 'TEST';

SUM(BYTES)/1024/1024
--------------------
31.1875

1 row selected.

Voila, DBA_SEGMENTS reports a higher space usage. Ok, connect as TEST and check whats
there in your recyclebin:

SQL> conn test/test
Connected.
SQL> show recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
BIG_TABLE BIN$+Ml+aeo2R8eXlKWaSyTBvw==$0 TABLE 2009-03-12:14:18:03

SQL> purge recyclebin;

Recyclebin purged.
SQL> show recyclebin
SQL>

Hmm, Oracle has moved the dropped table into user's recyclebin. We can throw it out using
the PURGE command.

Check the space usage again:

SQL> conn /as sysdba
Connected.
SQL> select sum(bytes)/1024/1024 from dba_segments where tablespace_name = 'TEST';

SUM(BYTES)/1024/1024
--------------------
31.1875

1 row selected.

SQL> select sum(bytes)/1024/1024 from dba_extents where tablespace_name = 'TEST';

SUM(BYTES)/1024/1024
--------------------
31.1875

1 row selected.

SQL>

Yep, the space reported is correct. :)

Wondering, Oracle Support took a week and hasn't replied yet.

Regards

Asif Momen
http://momendba.blogspot.com

--- On Wed, 3/11/09, genegurevich_at_discover.com wrote:
From: genegurevich_at_discover.com
Subject: Re: dba_extents vs dba_segments
To: [email protected]
Cc: hkchital_at_singnet.com.sg, jkstill_at_gmail.com, tim_at_evdbt.com, ajaycajay_at_gmail.com
Date: Wednesday, March 11, 2009, 1:28 PM

Hi everybody:

Thank you for your responses. I will take a look at the documents that
you
referred me to.

I was under impression that others have surely stumbled upon this and
therefore decided not to put any examples
into my Email. However, here is what I see:

SQL> select sum(bytes/1024)/1024 from dba_Segments where tablespace_name=
'PCM_D_01';

SUM(BYTES/1024)/1024
--------------------
696300

SQL> ch /Segments/extents
1* select sum(bytes/1024)/1024 from dba_extents where tablespace_name=
'PCM_D_01'
SQL> /

SUM(BYTES/1024)/1024
--------------------
471100

As you see the difference is quite significant and the only change in my
SQL was going from dba_segments to
dba_extents. The tablespace is a LMTS with uniform extens. The max number
of extents in a single segment is
441.

thank you

Gene Gurevich

Please consider the environment before printing this
email.

Hello:

I have set up a standby database under oracle 10.2.0.1 and everything
looked great except for a minor detail -
the archived logfiles do not get copied to the standby server. When I look
at the v$archive_dest view I see

ORA-12154: TNS:could not resolve the connect identifier specified
03-18-2009 10:47:32 pcrp01d
for dest_ID which correspond to my standby.

I have checked the tnsnames.ora on the primary side and it looks OK.
tnsping finds the standby; I can
ping the host. I have tried bouncing the listener on the standby side;
bouncind the standby DB itself
as well as primary db, but this error keep reapearring after 5 min pause.
If tnsnames.ora is OK, what
else could be the issue?

thank you

Gene Gurevich

Please consider the environment before printing this email.

Gene,
It may help to see your standby configuration.
log_archive_dest_n entries, as well as the log_archive_dest_state_n,
fal_server, fal_client, log_archive_config, as well as the tnsnames.ora
entries.

Is there more than one home? Are you using a dataguard broker?

Bradd Piontek
"Next to doing a good job yourself,

the greatest joy is in having someone
else do a first-class job under your
direction."

William Feather

Bradd and Ray

Here are the relevant parameters

log_archive_dest_1 string
LOCATION=/opt/oracle/admin/pcrp01/arch mandatory reopen=30
log_archive_dest_2 string SERVICE=pcrp01c ARCH
NOAFFIRM OPTIONAL
log_archive_dest_3 string SERVICE=pcrp01d ARCH

NOAFFIRM OPTIONAL

log_archive_dest_state_1 string enable
log_archive_dest_state_2 string enable
log_archive_dest_state_3 string enable

log_archive_config string

fal_client string
fal_server string

I have two standby DBs located on the same server and sharing the same
oracle_home. No dataguard brokes used

thank you

Gene Gurevich

Bradd Piontek
To
genegurevich_at_discover.com
03/18/2009 11:33 cc
AM [email protected]
Subject
Re: ORA-12154 and standby

Gene,log_archive_dest_n entries, as well as the log_archive_dest_state_n,
fal_server, fal_client, log_archive_config, as well as the tnsnames.ora
entries.

Is there more than one home? Are you using a dataguard broker?

Bradd Piontekis in having someone direction."

On Wed, Mar 18, 2009 at 10:58 AM, wrote:
Hello:

I have set up a standby database under oracle 10.2.0.1 and everything
looked great except for a minor detail -
the archived logfiles do not get copied to the standby server. When I
look
at the v$archive_dest view I see connect identifier specified
03-18-2009 10:47:32 pcrp01d
for dest_ID which correspond to my standby.

I have checked the tnsnames.ora on the primary side and it looks OK.
tnsping finds the standby; I can
ping the host.  I have tried bouncing the listener on the standby side;
bouncind the standby DB itself
as well as primary db, but this error keep reapearring after 5 min pause.
If tnsnames.ora is OK, what
else could be the issue?

thank you

Gene Gurevich

Please consider the environment before printing this email.

--
http://www.freelists.org/webpage/oracle-l

Please consider the environment before printing this email.

Hello all:

It looks like the issue was that my primary database was using a different
tnsnames.ora file (located in $ORACLE_HOME/network/admin)
to connect to the standby. I however only added the standby datbases to the
$TNS_ADMIN/tnsnames.ora file. After I have added them
to the other tnsnames.ora file everything fell into place.

Thank you again to all who replied to my posts here

thank you

Gene Gurevich

Sent by: To
oracle-l-bounce_at_f oracle-l_at_freelists.org
reelists.org cc

Subject
03/18/2009 11:03 ORA-12154 and standby
AM

Please respond to
genegurevich_at_disc
over.com

Hello:

I have set up a standby database under oracle 10.2.0.1 and everything
looked great except for a minor detail -
the archived logfiles do not get copied to the standby server. When I look
at the v$archive_dest view I see

ORA-12154: TNS:could not resolve the connect identifier specified
03-18-2009 10:47:32 pcrp01d
for dest_ID which correspond to my standby.

I have checked the tnsnames.ora on the primary side and it looks OK.
tnsping finds the standby; I can
ping the host. I have tried bouncing the listener on the standby side;
bouncind the standby DB itself
as well as primary db, but this error keep reapearring after 5 min pause.
If tnsnames.ora is OK, what
else could be the issue?

thank you

Gene Gurevich

Looks like this is an answer.

Here is my initial test

SQL> select sum(bytes/1024)/1024 from dba_segments where tablespace_name =
'PCM_D_01';

SUM(BYTES/1024)/1024

696300

SQL> select sum(bytes/1024)/1024 from dba_extents where tablespace_name =
'PCM_D_01';

SUM(BYTES/1024)/1024

536900

Now I exclude the recyclebin objects

SQL> select sum(bytes/1024)/1024 from dba_segments where tablespace_name =
'PCM_D_01' and segment_name not like 'BIN$%';

SUM(BYTES/1024)/1024

536900

SQL> ch /segments/extents/g
1* select sum(bytes/1024)/1024 from dba_extents where tablespace_name =
'PCM_D_01' and segment_name not like 'BIN$%'
SQL> /

SUM(BYTES/1024)/1024

536900

thank you very much for your help. Also thanks to everyone who responded to
my Emails - Mark, Tim, Jared, Ajay
(hope I did not miss anyone)

Gene Gurevich

Asif Momen
To
[email protected],
03/12/2009 06:35 genegurevich_at_discover.com
AM cc
hkchital_at_singnet.com.sg,
jkstill_at_gmail.com, tim_at_evdbt.com,
Please respond to ajaycajay_at_gmail.com
asif_oracle_at_yahoo Subject
.com Re: dba_extents vs dba_segments

Hi Gene,

Well, the discrepancy between the views is due to Oracle 10g's new feature of Recyclebin.
Some time back, I have blogged on this issue, you may read it here:

http://momendba.blogspot.com/2007/12/inconsistency-between-dbadatafiles.html

Lets reproduce the same:

SQL> conn / as sysdba
Connected.
SQL> select sum(bytes)/1024/1024 from dba_segments where tablespace_name = 'TEST';

SUM(BYTES)/1024/1024
--------------------
31.1875

1 row selected.

SQL> select sum(bytes)/1024/1024 from dba_extents where tablespace_name = 'TEST';

SUM(BYTES)/1024/1024
--------------------
31.1875

1 row selected.

Currently, DBA_SEGMENTS & DBA_EXTENTS report the same amount of usage "31.1875"
I connect to a TEST schema and create a table and later drop it:

SQL> conn test/test
Connected.
SQL> create table big_table as select * from all_objects;

Table created.

SQL> insert into big_table select * from big_table;

32617 rows created.

SQL> insert into big_table select * from big_table;

65234 rows created.

SQL> commit;

Commit complete.

SQL> drop table big_table;

Table dropped.

Connect as SYS (well, you may also query using USER_*) and run the previous queries again:

SQL> conn / as sysdba
Connected.
SQL> select sum(bytes)/1024/1024 from dba_segments where tablespace_name = 'TEST';

SUM(BYTES)/1024/1024
--------------------
46.1875

1 row selected.

SQL> select sum(bytes)/1024/1024 from dba_extents where tablespace_name = 'TEST';

SUM(BYTES)/1024/1024
--------------------
31.1875

1 row selected.

Voila, DBA_SEGMENTS reports a higher space usage. Ok, connect as TEST and check whats
there in your recyclebin:

SQL> conn test/test
Connected.
SQL> show recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
BIG_TABLE BIN$+Ml+aeo2R8eXlKWaSyTBvw==$0 TABLE 2009-03-12:14:18:03

SQL> purge recyclebin;

Recyclebin purged.
SQL> show recyclebin
SQL>

Hmm, Oracle has moved the dropped table into user's recyclebin. We can throw it out using
the PURGE command.

Check the space usage again:

SQL> conn /as sysdba
Connected.
SQL> select sum(bytes)/1024/1024 from dba_segments where tablespace_name = 'TEST';

SUM(BYTES)/1024/1024
--------------------
31.1875

1 row selected.

SQL> select sum(bytes)/1024/1024 from dba_extents where tablespace_name = 'TEST';

SUM(BYTES)/1024/1024
--------------------
31.1875

1 row selected.

SQL>

Yep, the space reported is correct. :)

Wondering, Oracle Support took a week and hasn't replied yet.

Regards

Asif Momen
http://momendba.blogspot.com

--- On Wed, 3/11/09, genegurevich_at_discover.com wrote:
From: genegurevich_at_discover.com
Subject: Re: dba_extents vs dba_segments
To: [email protected]
Cc: hkchital_at_singnet.com.sg, jkstill_at_gmail.com, tim_at_evdbt.com, ajaycajay_at_gmail.com
Date: Wednesday, March 11, 2009, 1:28 PM

Hi everybody:

Thank you for your responses. I will take a look at the documents that
you
referred me to.

I was under impression that others have surely stumbled upon this and
therefore decided not to put any examples
into my Email. However, here is what I see:

SQL> select sum(bytes/1024)/1024 from dba_Segments where tablespace_name=
'PCM_D_01';

SUM(BYTES/1024)/1024
--------------------
696300

SQL> ch /Segments/extents
1* select sum(bytes/1024)/1024 from dba_extents where tablespace_name=
'PCM_D_01'
SQL> /

SUM(BYTES/1024)/1024
--------------------
471100

As you see the difference is quite significant and the only change in my
SQL was going from dba_segments to
dba_extents. The tablespace is a LMTS with uniform extens. The max number
of extents in a single segment is
441.

thank you

Gene Gurevich

Please consider the environment before printing this
email.

This is quite interesting, I wasn't previously aware of this
issue with recyclebin.

This might be playing havoc with my script for determining
space in the database, guess it would be a good idea
check which is being used - dba_extents or dba_segments.

Jared

Hi,

Yes, the recyclebin created a nuisance with me as well. Developers use to report that they see garbage table names starting with "BIN", when they issue "select * from tab".

Regards

Asif Momen
http://momendba.blogspot.com

On Thu, 3/12/09, Jared Still wrote:
From: Jared Still
Subject: Re: dba_extents vs dba_segments
To: genegurevich_at_discover.com
Cc: asif_oracle_at_yahoo.com, [email protected]
Date: Thursday, March 12, 2009, 2:58 PM

On Thu, Mar 12, 2009 at 12:45 PM, wrote:

Looks like this is an answer.

Now I exclude the recyclebin objects

SQL> select sum(bytes/1024)/1024 from dba_segments where tablespace_name =

'PCM_D_01' and segment_name not like 'BIN$%';

SUM(BYTES/1024)/1024

536900

This is quite interesting, I wasn't previously aware of this
issue with recyclebin.

This might be playing havoc with my script for determining
space in the database, guess it would be a good idea

check which is being used - dba_extents or dba_segments.

Jared

Has anybody successfully used Orion on a Windows 2003 server running AMD
? I keep trying to run it, and get no errors from Orion itself, but it
refuses to run - the program just dies with no errors.



In the Application Event Viewer I get the following-



Faulting application orion.exe, version 0.0.0.0, faulting module
orion.exe, version 0.0.0.0, fault address 0x0004db7f.



I find it hard to believe it has anything to do with an AMD processor,
but the only other machine I tried it on was a Windows XP 32 bit Intel
laptop, and it works fine there. My feeling is that its more Windows
2003 and/or 64 bit I tried to alter the compatibility of orion.exe
(using Win XP compatibility) but I suspect this did not do anything, and
it had no positive affect.



I am using a test.lun file to point to a dbf file I created. I also
tried it on a real DBF file that I copied from a 10g database, with no
change. I am stumped.

Not on AMD, but I have successfully used it on Windows 2003 64 bit servers
without issue.

Jared

I have it set to the database name on the standby and primary sides:

SQL> show parameter uniq

NAME TYPE VALUE
------------------------------------ -----------
------------------------------
db_unique_name string pcrp01

SQL> show parameter uniq

NAME TYPE VALUE
------------------------------------ -----------
------------------------------
db_unique_name string pcrp01d

SQL> show parameter uniq

NAME TYPE VALUE
------------------------------------ -----------
------------------------------
db_unique_name string pcrp01c

thank you

Gene Gurevich
Oracle MySQL Operations
224-405-4079

Ray Stell
To
Sent by: genegurevich_at_discover.com
oracle-l-bounce_at_f cc
reelists.org Bradd Piontek,
[email protected]
03/18/2009 12:49 Subject
PM Re: ORA-12154 and standby

Please respond to
stellr_at_cns.vt.edu
I'm guessing DB_UNIQUE_NAME:

DB_UNIQUE_NAME

Usage Notes

This attributes is required if the LOG_ARCHIVE_CONFIG=DG_CONFIG
initialization parameter is specified and if this is a remote
destination (specified with the SERVICE attribute).

the value I have set for the dest_2 is different from yours and
includes the db_unique_name for the far end, again, just a guess:

SQL> show parameter log_archive_dest_2

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------
log_archive_dest_2 string SERVICE=fred_stby ASYNC
VALID_FOR=(ONLINE_LOGFILES,
PRIMARY_ROLE)
DB_UNIQUE_NAME=fred_stby

Hello everybody:

I am looking for some input on a table design (oracle 10.2.0.3). Here is
what I know:

the table will probably have several hundreds of millions of rows (I am
looking for a better estimate from my customers)
it will store the counts per ID pre date per code for each of the ID.
Something like

ID - Year - Month - Day - Code1 - Code2 - Code3 - Count

We will have a primary key on all the columns except for the count
The data will be stored for the 15 half-month periods (the Day will be
either 1 or 15)
The data will be selected based on the ID and the users will need to get
all the data
I will partition the table by Year/Month/Day to make purging easier.

What I wonder is whether the following design (which I do not like very
much) may give a better performance:

ID - Code1 - Code2 - Code3 - Count1 - Count2 - ... Count15

Because it may potentially return less rows and therefore require less IO.

Any thoughts?

thank you

Gene Gurevich

Please consider the environment before printing this email.

If performance is going to be a problem, and you can improve performance be
denormalization, then do it. There are many factors which you have not
mentioned such as Service Level Agreement, frequency of update, average
transaction size etc etc. that will determine your final decision.

At this point I am trying to do now is to compe up with an initial design.
I do lean towards a more normalized one, but I am also trying to see
whether there is reasonable possibility that denormalizing will indeed give
a significant performance improvement. If it looks reasonable
I would rather start with the denormalized design so that we don't have to
make a big change at the end of the process

thank you

Gene Gurevich

Michael Moore
To
Sent by: [email protected]
oracle-l-bounce_at_f cc
reelists.org
Subject
Re: Looking for table design input
03/27/2009 12:49
PM

Please respond to
michaeljmoore_at_gma
il.com

If performance is going to be a problem, and you can improve performance be
denormalization, then do it. There are many factors which you have not
mentioned such as Service Level Agreement, frequency of update, average
transaction size etc etc. that will determine your final decision.

On Fri, Mar 27, 2009 at 8:13 AM, wrote:
Hello everybody:

I am looking for some input on a table design (oracle 10.2.0.3). Here is
what I know:

the table will probably have several hundreds of millions of rows (I am
looking for a better estimate from my customers)
it will store the counts per ID pre date per code for each of the ID.
Something like

ID - Year - Month - Day - Code1 - Code2 - Code3 - Count

We will have a primary key on all the columns except for the count
The data will be stored for the 15 half-month periods (the Day will be
either 1 or 15)
The data will be selected based on the ID and the users will need to
get
all the data
I will partition the table by Year/Month/Day to make purging easier.

What I wonder is whether the following design (which I do not like very
much) may give a better performance:

ID -  Code1 - Code2 - Code3 - Count1 - Count2 - ... Count15

Because it may potentially return less rows and therefore require less
IO.

Any thoughts?

thank you

Gene Gurevich

Please consider the environment before printing this email.

--
http://www.freelists.org/webpage/oracle-l

Please consider the environment before printing this email.

I'm not sure why you're calling that a denormalization.



Count1 through Count15 are likely on the same domain, but they are each in
different roles.



Now if you use the role name intelligently, say 20090315_count,
20090401_count, etc. then you actually need only the ID as the primary key
to completely convey to the user the meaning of each column in the 16-tuple.



Now I find it surpassing strange that a single abstract ID gives meaning
about your data to your users, but perhaps there is another table to which
id is a foreign key that has further description of the rows. Not strictly
needed, since it is likely to be 1 to 1, but possibly useful especially if
there is a lot in it and it is somewhat static compared to the
(id,count(1..15)) relation, forgiving the shorthand which I hope is
understandable.



What about rotating months? That is, making a new relation where the oldest
count column is removed and a newest count column is added? It seems likely
to me that the drop column, add column on this relatively short row is
likely to remain within a single block, so that is probably not a big deal.



Now, as for maintenance of programs, your first impression might be "OUCH!"
now if I want to set up some query that compares the two most recent "count"
columns you're going to have to constantly update the programs.



However, THAT is a tremendously good use of views, where it is quite easy to
define views that you only have to trivally update in concert (just after)
the drop column add column exercise. Then the reports on the relative
columns remain static and are valid in context of when they are run, while
ad hoc reports specifically naming columns make sense and no one has to look
up what count1 means, because the column name (or role.domain column as Dr.
Codd put it) is clear unto itself.



Further, since presumably there is period in time where the newest
yyyymmdd_count column must be populated, you could well populate that column
before redefining the views and then drop the oldest column at your leisure.
Users directly using the yyyymmdd_count name would need to have some
mechanism to know the column is valid, but users of views would never be
exposed to columns partially filled or partially dropped.



Now I suspect some folks are just itching to write back that this is
denormalized. Before you pull that trigger, I suggest you actually read at
least pages 377-380 of Communications of the ACM, Volume 13, Number 6, June
1970.



Regards,



mwf



From: oracle-l-bounce_at_freelists.org
On Behalf Of Michael Moore
Sent: Friday, March 27, 2009 1:44 PM
To: [email protected]
Subject: Re: Looking for table design input



If performance is going to be a problem, and you can improve performance be
denormalization, then do it. There are many factors which you have not
mentioned such as Service Level Agreement, frequency of update, average
transaction size etc etc. that will determine your final decision.

On Fri, Mar 27, 2009 at 8:13 AM, wrote:

Hello everybody:

I am looking for some input on a table design (oracle 10.2.0.3). Here is
what I know:

the table will probably have several hundreds of millions of rows (I am
looking for a better estimate from my customers)
it will store the counts per ID pre date per code for each of the ID.
Something like

ID - Year - Month - Day - Code1 - Code2 - Code3 - Count

We will have a primary key on all the columns except for the count
The data will be stored for the 15 half-month periods (the Day will be
either 1 or 15)
The data will be selected based on the ID and the users will need to get
all the data
I will partition the table by Year/Month/Day to make purging easier.

What I wonder is whether the following design (which I do not like very
much) may give a better performance:

ID - Code1 - Code2 - Code3 - Count1 - Count2 - ... Count15

Because it may potentially return less rows and therefore require less IO.

Any thoughts?

thank you

Gene Gurevich

Please consider the environment before printing this email.

[had a few offline clarifications with Gene]

I'd consider changing the three code columns into one column, just to save
space (by minimizing the row-length) on your huge table.
Like this:

create table CODE_COMBO_TABLE -- Holds all valid combinations of code1/2/3.

(ID number,CODE1 ...,CODE2 ...,CODE3 ...,primary key (ID),unique (CODE1,CODE2,CODE3)
)
/

YEAR/MONTH/HALF would be required to remain in the main table, as they drive
your partitioning scheme.
ID also, as that is your main filter criteria in queries on this table.

create table COUNTS_TABLE

(ID number not null FK references ....,YEAR number(4,0) not null,MONTH number(2,0) not null check(MONTH between 1 and 12),HALF number(1,0) not null check(HALF in (1,2)),CODE_COMBO_ID number not null references CODE_COMBO_TABLE(ID),COUNT# number,primary key (YEAR,MONTH,HALF,ID,CODE_COMBO)
)
/

Note the order in the PK...

Then partition the COUNTS_TABLE such that every partition holds only one
combination of Year, Month, Half.
You would want the PK index to be a local index, this is why I start it with
the three-columns that are driving the partioning scheme. Queries with
'WHERE ID=:bindvalue' should perform without any problems, since each local
index effectively starts with the ID-column.

The required (hash) join to get the actual CODE1/2/3 values for every query
now, is a bet I'm willing to take.
But I do not know how critical your requirements are.

Btw.
Your proposed setup

ID - Code1 - Code2 - Code3 - Count1 - Count2 - ... Count15

Would require massive updates every half month (shifting 14 of the 15 values
to the right).
I'd not be in favor of such a design.

Toon

Hi Gene

I am currently proposing such a design to one of my projects

ID - Code1 - Code2 - Code3 - Count1 - Count2 - ... Count15

Reason this is a part of a online application where the data needs to be shown on a webfront end.
This means the response time needs to be typically less then 10 seconds.I tested the approach that you had earlier and in my case with 42 Million rows and all kind of sql pivoting techniques applied i cannot get the answer in less then 10 seconds unless i design my data structure in the way you are suggesting.

Best way to do this i can think of is the hard way only.To answer the query upfront and design it that way.

Downside to the approach is
Some sort fo ETL needs to be designed so the data is structured that way.
(i was thinking of Oracle streams replication to do this but ran out of ideas on how to implement that)
Tomorrow if more counts are added you need to add more columns (in my case i would have to add more columns) earlier approach does not require manual intervention and would be just inserting more rows.

Hi Gene,
From the look of it, the table represents a FACT table of a star schematic design, I am just guessing here. If the table is part of warehouse or intended for reporting purpose, then the design is probably applicable, provided you have proper dimension tables in relation to this table. normalizing it will not be a good thing to do if such is the case.

Thanks!
Amar
Www.amar-Padhi.com

-original message-
Re: Looking for table design input
From: genegurevich_at_discover.com
Date: 27-03-2009 23:13

At this point I am trying to do now is to compe up with an initial design.

Amar:

This table does look like a fact table, but it will be part of the
curstoer-facing online system. The users will need to be able to retrieve
all the data
per a specific ID

thank you

Gene Gurevich

Amar Kumar Padhi
To

03/27/2009 02:32 cc
PM
Subject
Re: Looking for table design input

Hi Gene,
From the look of it, the table represents a FACT table of a star schematic
design, I am just guessing here. If the table is part of warehouse or
intended for reporting purpose, then the design is probably applicable,
provided you have proper dimension tables in relation to this table.
normalizing it will not be a good thing to do if such is the case.

Thanks!
Amar
Www.amar-Padhi.com

-original message-
Re: Looking for table design input
From: genegurevich_at_discover.com
Date: 27-03-2009 23:13

At this point I am trying to do now is to compe up with an initial design.

Please consider the environment before printing this email.

it is ok to retrieve data for query purpose from a FACT (through a dimension) to an online processing screen, as long as this does not require the retrieved data to be updated also. I have seen such setup and in effect FACT query proved to be much cheaper than online table access, as data is already summarized and pre-computed for reporting. This could be true for your case also, but needs to be determined case to case.

And for clarity sake, we follow standards not to mix online access with data mart objects, this is to avoid any kind of wrong usage. But as an exception we allowed it in few sceens as we could see significant benefit in performance.

Thanks!
Amar
Www.amar-Padhi.com

-original message-
Re: Looking for table design input
From: genegurevich_at_discover.com
Date: 28-03-2009 00:15

Amar:

This table does look like a fact table, but it will be part of the

Hello:

If I have two users existing in two separate databases (10.2.0.3) and these
users have the same value in the password column of the dba_users
view, does this mean that they have the same passwords?

In other words. if I enter the following command:

alter user USER1 identified by values 'XXXXXXX'

where XXXXXX = select password from dba_users where username = 'USER2'

will the USER1 and USER2 have the same passwords?

thank you

Gene Gurevich

Please consider the environment before printing this email.

that should work as a full import does a alter user username identified by
values, and I have taken the hex value passed thru a password cracker and
found a password that with the exact same hex value on a different db and
it worked.

all this is 10g and below, 11g is a different animal with case sensitive
password and the such.

joe

Joe Testa, Oracle Certified Professional
(Work) 614-677-1668
(Cell) 614-312-6715

Interested in helping out your marriage?
Ask me about "Weekend to Remember"
Dec 11-13, 2009 here in Columbus.

From:

To:
[email protected]
Date:
06/17/2009 06:21 PM
Subject:
passwords in different instances
Sent by:
oracle-l-bounce_at_freelists.org

Hello:

If I have two users existing in two separate databases (10.2.0.3) and
these
users have the same value in the password column of the dba_users
view, does this mean that they have the same passwords?

In other words. if I enter the following command:

alter user USER1 identified by values 'XXXXXXX'

where XXXXXX = select password from dba_users where username = 'USER2'

will the USER1 and USER2 have the same passwords?

thank you

Gene Gurevich

Please consider the environment before printing this email.

Yes they will have the same password. It wouldn't be possible if the user
exists in the same database, since they are in two different databases so
there password will match.

Thanks
Nilesh

I believe they will have the same password if they have the same username.
Since those to are concatenated ant then hashed, the hash value you get to
with one password would not be the same for two different user
names. I'm not sure, but it doesn't feel as OP or the thread has made it
clear if the user in the second database will have the same user name or
not.

Mathias

Hello everybody:

I am using create_index_cost procedure (oracle 10.2.0.3) to estimate the
size of a future index and find an appropriate tablespace parameters for
it.
Here is my code:

declare

l_used_bytes number;
l_alloc_bytes number;
begin

dbms_space.create_index_cost (

ddl =>

'create unique index owner.ACCT_KEY_UA_SEGMEMBERSHIP_ALT1 ' ||

' on owner.ACCT_KEY_UA_SEGMEMBERSHIP (SEGMENTID, ACCT_KEY) ' ||
' tablespace TS1 nologging parallel 4',

used_bytes => l_used_bytes,
alloc_bytes => l_alloc_bytes
);
dbms_output.put_line ('Used Bytes = '||l_used_bytes);

dbms_output.put_line ('Allocated Bytes = '||l_alloc_bytes);
end;
/

I am seeing a significant difference between used bytes and allocated bytes

Used Bytes = 723481137
Allocated Bytes = 1782579200

I thought that this is due to the tablespace's extent size (my tablespace
is locally partitioned). So I tried running the same procedure with
different values
of the tablespace name. The tablespaces I used had the extent size from
128K to 50M and yet the difference between used and allocated bytest
did not change that much. So I think my reasoning of what is the root of
the difference is incorrect. Does any one have a better explanation for
this?

thank you

Gene Gurevich

Please consider the environment before printing this email.

Answers here.

http://jonathanlewis.wordpress.com/2009/05/22/index-size/

Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Original Message -----
From:
To:
Sent: Thursday, July 02, 2009 7:55 PM
Subject: create_index_cost procedure

Answers here.

http://jonathanlewis.wordpress.com/2009/05/22/index-size/

Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Original Message -----
From:
To:
Sent: Thursday, July 02, 2009 7:55 PM
Subject: create_index_cost procedure

Answers here.

http://jonathanlewis.wordpress.com/2009/05/22/index-size/

Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Original Message -----
From:
To:
Sent: Thursday, July 02, 2009 7:55 PM
Subject: create_index_cost procedure

Johathan,

Thank you for your article. I am still unclear though why in my example the
value of allocated parameter did not change
even though I have specified different tablespaces with varying next_Extent
values (from 128K to 50M). All these
tablespaces a local with uniform extent value. I expected the allocated
value to differ based on the size of an extent.
IS that incorrect?

thank you

Gene Gurevich
Phone 224-405-4079
Database Engineering and Operations
Enterprise Data Management (EDM)
Discover Financial Services

"Jonathan Lewis"
To
Sent by:
oracle-l-bounce_at_f cc
reelists.org
Subject
Re: create_index_cost procedure
07/02/2009 04:17
PM

Please respond to
jonathan_at_jlcomp.d
emon.co.uk

Answers here.

http://jonathanlewis.wordpress.com/2009/05/22/index-size/

Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Original Message -----
From:
To:
Sent: Thursday, July 02, 2009 7:55 PM
Subject: create_index_cost procedure

Didn't change at all, or didn't change much ?

How about posting the output from one dbms_xplan, and a list of
extent sizes with allocated sizes.

Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Original Message -----
From:
To:
Cc:;
Sent: Monday, July 06, 2009 4:44 PM
Subject: Re: create_index_cost procedure

Jonathan:

Here is my output :

SQL> _at_est_index
SQL> declare

2 l_used_bytes number;
3 l_alloc_bytes number;
4 begin
5 dbms_space.create_index_cost (
6 ddl =>
7 'create unique index unicav7.ACCT_KEY_UA_SEGMEMBERSHIP_ALT1 ' ||

8 ' on unicav7.ACCT_KEY_UA_SEGMEMBERSHIP (SEGMENTID, ACCT_KEY) ' ||
9 ' tablespace unica_x_01 ',

10 used_bytes => l_used_bytes,
11 alloc_bytes => l_alloc_bytes
12 );
13 dbms_output.put_line ('Used Bytes = '||l_used_bytes);
14 dbms_output.put_line ('Allocated Bytes = '||l_alloc_bytes);

15 end;
16 /
Used Bytes = 723481137
Allocated Bytes = 1740636160

PL/SQL procedure successfully completed.

SQL> declare

2 l_used_bytes number;
3 l_alloc_bytes number;
4 begin
5 dbms_space.create_index_cost (
6 ddl =>
7 'create unique index unicav7.ACCT_KEY_UA_SEGMEMBERSHIP_ALT1 ' ||

8 ' on unicav7.ACCT_KEY_UA_SEGMEMBERSHIP (SEGMENTID, ACCT_KEY) ' ||
9 ' tablespace users ',

10 used_bytes => l_used_bytes,
11 alloc_bytes => l_alloc_bytes
12 );
13 dbms_output.put_line ('Used Bytes = '||l_used_bytes);
14 dbms_output.put_line ('Allocated Bytes = '||l_alloc_bytes);

15 end;
16 /
Used Bytes = 723481137
Allocated Bytes = 1735000064

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL> declare
2 l_used_bytes number;
3 l_alloc_bytes number;
4 begin
5 dbms_space.create_index_cost (
6 ddl =>
7 'create unique index unicav7.ACCT_KEY_UA_SEGMEMBERSHIP_ALT1 ' ||

8 ' on unicav7.ACCT_KEY_UA_SEGMEMBERSHIP (SEGMENTID, ACCT_KEY) ' ||
9 ' tablespace PCM_D_03 ',

10 used_bytes => l_used_bytes,
11 alloc_bytes => l_alloc_bytes
12 );
13 dbms_output.put_line ('Used Bytes = '||l_used_bytes);
14 dbms_output.put_line ('Allocated Bytes = '||l_alloc_bytes);

15 end;
16 /
Used Bytes = 723481137
Allocated Bytes = 1736441856

PL/SQL procedure successfully completed.

SQL>

SQL> declare

2 l_used_bytes number;
3 l_alloc_bytes number;
4 begin
5 dbms_space.create_index_cost (
6 ddl =>
7 'create unique index unicav7.ACCT_KEY_UA_SEGMEMBERSHIP_ALT1 ' ||

8 ' on unicav7.ACCT_KEY_UA_SEGMEMBERSHIP (SEGMENTID, ACCT_KEY) ' ||
9 ' tablespace unica_d_01 ',

10 used_bytes => l_used_bytes,
11 alloc_bytes => l_alloc_bytes
12 );
13 dbms_output.put_line ('Used Bytes = '||l_used_bytes);
14 dbms_output.put_line ('Allocated Bytes = '||l_alloc_bytes);

15 end;
16 /
Used Bytes = 723481137
Allocated Bytes = 1782579200

PL/SQL procedure successfully completed

These are some of the TS parameters:

1 select tablespace_name, block_size, initial_extent, next_extent,
logging, allocation_type, extent_management
2 from dba_tablespaces
3* where tablespace_name in
('UNICA_D_01','PCM_D_03','USERS','UNICA_X_01') order by 3 asc
SQL> /

TABLESPACE_NAME BLOCK_SIZE INITIAL_EXTENT NEXT_EXTENT

LOGGING ALLOCATIO EXTENT_MAN

------------------------------ ---------- -------------- -----------
--------- --------- ----------
USERS 16384 131072 131072
LOGGING UNIFORM LOCAL
PCM_D_03 16384 2097152 2097152
LOGGING UNIFORM LOCAL
UNICA_X_01 16384 10485760 10485760
LOGGING UNIFORM LOCAL
UNICA_D_01 16384 52428800 52428800

LOGGING UNIFORM LOCAL

thank you

Gene Gurevich

"Jonathan Lewis"
To
Sent by:
oracle-l-bounce_at_f cc
reelists.org
Subject
Re: create_index_cost procedure
07/06/2009 05:13
PM

Please respond to
jonathan_at_jlcomp.d
emon.co.uk

Didn't change at all, or didn't change much ?

How about posting the output from one dbms_xplan, and a list of
extent sizes with allocated sizes.

Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Original Message -----
From:
To:
Cc:;
Sent: Monday, July 06, 2009 4:44 PM
Subject: Re: create_index_cost procedure

Allocated Bytes = 1,735,000,064 = 13,237 extents _at_ 128KB
Allocated Bytes = 1,736,441,856 = 828 extents _at_ 2MB
Allocated Bytes = 1,740,636,160 = 166 extents _at_ 10MB
Allocated Bytes = 1,782,579,200 = 34 extents _at_ 50MB

Notice that the change from one figure to the next is less than
the larger of the two extent sizes. Note also that each figure is
an exact multiple of the extent size.

Oracle is rounding up to allow for the unused portion of the
last extent.

Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Original Message -----
From:
To:
Cc:;
Sent: Monday, July 06, 2009 11:26 PM
Subject: Re: create_index_cost procedure

Jonathan:

Now that I have reread your article, I think my question is the same as you
raised:
" 24,000 bytes as the used bytes – and that should fit comfortably inside a
single 64KB extent in the systemtablespace – so why was the allocated space
reported as 128KB / two extents?:

And your answer seems to be that this number does not include the various
overheads as well as other factors that could affect the size.
Does that mean that it is allocated bytes that should be considered a rough
estimate in your opinion?

thank you

Gene Gurevich

"Jonathan Lewis"
To
Sent by:
oracle-l-bounce_at_f cc
reelists.org
Subject
Re: create_index_cost procedure
07/07/2009 12:00
AM

Please respond to
jonathan_at_jlcomp.d
emon.co.uk

Allocated Bytes = 1,735,000,064 = 13,237 extents @ 128KB
Allocated Bytes = 1,736,441,856 = 828 extents @ 2MB
Allocated Bytes = 1,740,636,160 = 166 extents @ 10MB
Allocated Bytes = 1,782,579,200 = 34 extents @ 50MB

Notice that the change from one figure to the next is less than
the larger of the two extent sizes. Note also that each figure is
an exact multiple of the extent size.

Oracle is rounding up to allow for the unused portion of the
last extent.

Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Original Message -----
From:
To:
Cc:;
Sent: Monday, July 06, 2009 11:26 PM
Subject: Re: create_index_cost procedure

Hi everybody:

Now I have an issue with the create_table_cost procedure. I have executed
it for a table and got the following results (oracle 10.2.0.3)

used - 11141112
allocated - 1114112

That seem to indicate that there is no space wasted associates with this
table

But when I queried dba_segments select sum(bytes/1024)/1024 from
dba_segments where segment_name = and owner =
I got 2785280 - or more than twice the size that I received from
create_table_cost. What is the reason for this discrepancy?

thank you

Gene Gurevich

Please consider the environment before printing this email.

Answers here.

http://jonathanlewis.wordpress.com/2009/05/22/index-size/

Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Original Message -----
From:
To:
Sent: Thursday, July 02, 2009 7:55 PM
Subject: create_index_cost procedure

Sorry about the repeats - my mail program keeps saying
that it hasn't sent the mail.

Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Original Message -----
From: "Jonathan Lewis"
To:
Sent: Thursday, July 02, 2009 10:14 PM
Subject: Re: create_index_cost procedure

Answers here.

http://jonathanlewis.wordpress.com/2009/05/22/index-size/

Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Original Message -----
From:
To:
Sent: Thursday, July 02, 2009 7:55 PM
Subject: create_index_cost procedure

Joe, I think you're referring to the default behavior when a new user is
created in 11g. In many cases involving "migrating" a user from an older
version as with exp and imp, the password is still case insensitive. You
can check dba_users.password_versions to confirm this.

Yong Huang