FAQ
We aren't sure WHAT is the discrepancy that you see -- whether it
relates to specific types of segments or depends on extent sizes.
See MetaLink Note#463101.1

Hemant K Chitale
http://hemantoracledba.blogspot.com
At 10:57 PM Wednesday, genegurevich_at_discover.com wrote:

Hi everybody:

I am running oracle 10.2.0.3 and it looks like the data in dba_extents and
dba_segmetns views are
not consistent. When I aggregate them on a owner level (select owner,
sum(bytes/1024)/1024 from dba_???)
the results from dba_extents seem to be different from dba_segments. I
have opened an SR with Oracle
a week ago and so far got nothing.

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

Search Discussions

  • Jared Still at Mar 11, 2009 at 3:44 pm

    On Wed, Mar 11, 2009 at 7:57 AM, wrote:
    I am running oracle 10.2.0.3 and it looks like the data in dba_extents and
    dba_segmetns views are
    not consistent. When I aggregate them on a owner level (select owner,
    sum(bytes/1024)/1024 from dba_???)
    the results from dba_extents seem to be different from dba_segments. I
    have opened an SR with Oracle
    a week ago and so far got nothing.

    I'm sure I am not the first to hit this issue - does anyone have a fix for
    that?
    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
  • Tim Gorman at Mar 11, 2009 at 3:55 pm
    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:
    Hi everybody:

    I am running oracle 10.2.0.3 and it looks like the data in dba_extents and
    dba_segmetns views are
    not consistent. When I aggregate them on a owner level (select owner,
    sum(bytes/1024)/1024 from dba_???)
    the results from dba_extents seem to be different from dba_segments. I
    have opened an SR with Oracle
    a week ago and so far got nothing.

    I'm sure I am not the first to hit this issue - does anyone have a fix for
    that?

    thank you

    Gene Gurevich


    Please consider the environment before printing this email.


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



    --
    http://www.freelists.org/webpage/oracle-l
  • Ajay c at Mar 11, 2009 at 4:23 pm
    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.
    On 3/11/09, genegurevich_at_discover.com wrote:


    Hi everybody:

    I am running oracle 10.2.0.3 and it looks like the data in dba_extents and
    dba_segmetns views are
    not consistent. When I aggregate them on a owner level (select owner,
    sum(bytes/1024)/1024 from dba_???)
    the results from dba_extents seem to be different from dba_segments. I
    have opened an SR with Oracle
    a week ago and so far got nothing.

    I'm sure I am not the first to hit this issue - does anyone have a fix for
    that?

    thank you

    Gene Gurevich


    Please consider the environment before printing this email.



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

    --
    http://www.freelists.org/webpage/oracle-l
  • Genegurevich_at_discover.com at Mar 11, 2009 at 8: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.
  • Asif Momen at Mar 12, 2009 at 11:35 am
    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: oracle-l@freelists.org
    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.
  • Genegurevich_at_discover.com at Mar 12, 2009 at 2:15 pm
    Thank you.

    Let me check if this is the case

    thank you

    Gene Gurevich

    Asif Momen
    To
    oracle-l@freelists.org,
    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: oracle-l@freelists.org
    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.

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

    Please consider the environment before printing this email.

    --
    http://www.freelists.org/webpage/oracle-l
  • Genegurevich_at_discover.com at Mar 18, 2009 at 3:58 pm
    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.
  • Bradd Piontek at Mar 18, 2009 at 4:33 pm
    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
    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
    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.


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

    --
    http://www.freelists.org/webpage/oracle-l
  • Genegurevich_at_discover.com at Mar 18, 2009 at 5:31 pm
    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 oracle-l@freelists.org
    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.
  • Genegurevich_at_discover.com at Mar 24, 2009 at 4:10 pm
    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

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

    Please consider the environment before printing this email.

    --
    http://www.freelists.org/webpage/oracle-l
  • Genegurevich_at_discover.com at Mar 12, 2009 at 7:45 pm
    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
    oracle-l@freelists.org,
    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: oracle-l@freelists.org
    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.

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

    Please consider the environment before printing this email.

    --
    http://www.freelists.org/webpage/oracle-l
  • Jared Still at Mar 12, 2009 at 9: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
  • Asif Momen at Mar 12, 2009 at 10:06 pm
    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, oracle-l@freelists.org
    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
  • Crisler, Jon at Mar 12, 2009 at 10:35 pm
    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.
  • Jared Still at Mar 12, 2009 at 11:03 pm

    On Thu, Mar 12, 2009 at 3:35 PM, Crisler, Jon wrote:

    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.
    Not on AMD, but I have successfully used it on Windows 2003 64 bit servers
    without issue.

    Jared
  • Genegurevich_at_discover.com at Mar 18, 2009 at 6:05 pm
    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,
    oracle-l@freelists.org
    03/18/2009 12:49 Subject
    PM Re: ORA-12154 and standby

    Please respond to
    stellr_at_cns.vt.edu
    On Wed, Mar 18, 2009 at 12:31:25PM -0500, genegurevich_at_discover.com wrote:
    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
    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).

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

    Please consider the environment before printing this email.

    --
    http://www.freelists.org/webpage/oracle-l
  • Ray Stell at Mar 18, 2009 at 6:29 pm

    On Wed, Mar 18, 2009 at 01:05:16PM -0500, genegurevich_at_discover.com wrote:
    I have it set to the database name on the standby and primary sides:
    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
  • Genegurevich_at_discover.com at Mar 27, 2009 at 3:13 pm
    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.
  • Michael Moore at Mar 27, 2009 at 5:43 pm
    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

    --
    http://www.freelists.org/webpage/oracle-l
  • Genegurevich_at_discover.com at Mar 27, 2009 at 6:57 pm
    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: oracle-l@freelists.org
    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.
  • Mark W. Farnham at Mar 27, 2009 at 7:31 pm
    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: oracle-l@freelists.org
    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.
  • Toon Koppelaars at Mar 27, 2009 at 9:15 pm
    [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
    On Fri, Mar 27, 2009 at 4:13 PM, 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

    --
    Toon Koppelaars
    RuleGen BV
    +31-615907269
    Toon.Koppelaars_at_RuleGen.com
    www.RuleGen.com
    thehelsinkideclaration.blogspot.com

    (co)Author: "Applied Mathematics for Database Professionals"
    www.RuleGen.com/pls/apex/f?p=14265:13

    --
    http://www.freelists.org/webpage/oracle-l
  • Hrishy at Mar 30, 2009 at 6:57 am
    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.
  • Amar Kumar Padhi at Mar 27, 2009 at 7:29 pm
    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.
  • Genegurevich_at_discover.com at Mar 27, 2009 at 8:10 pm
    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.
  • Amar Kumar Padhi at Mar 27, 2009 at 8:45 pm
    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
  • Genegurevich_at_discover.com at Jun 17, 2009 at 10:17 pm
    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.
  • TESTAJ3_at_nationwide.com at Jun 17, 2009 at 11:22 pm
    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:
    oracle-l@freelists.org
    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.
  • Nilesh kumar at Jun 19, 2009 at 3:17 am
    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
    On Thu, Jun 18, 2009 at 4:52 AM, wrote:


    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: oracle-l@freelists.org 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.


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



    --
    http://www.freelists.org/webpage/oracle-l
  • Mathias Magnusson at Jun 19, 2009 at 5:57 am
    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
    On Fri, Jun 19, 2009 at 5:17 AM, nilesh kumar wrote:

    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

    On Thu, Jun 18, 2009 at 4:52 AM, wrote:


    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: oracle-l@freelists.org 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.


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



    --
    http://www.freelists.org/webpage/oracle-l
  • Genegurevich_at_discover.com at Jul 2, 2009 at 6:55 pm
    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.
  • Jonathan Lewis at Jul 2, 2009 at 9:14 pm
    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
    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:
    --
    http://www.freelists.org/webpage/oracle-l
  • Jonathan Lewis at Jul 2, 2009 at 9:14 pm
    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
    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:
    --
    http://www.freelists.org/webpage/oracle-l
  • Jonathan Lewis at Jul 2, 2009 at 9:14 pm
    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
    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:
    --
    http://www.freelists.org/webpage/oracle-l
  • Genegurevich_at_discover.com at Jul 6, 2009 at 3:44 pm
    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
    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:
    --
    http://www.freelists.org/webpage/oracle-l

    Please consider the environment before printing this email.

    --
    http://www.freelists.org/webpage/oracle-l
  • Jonathan Lewis at Jul 6, 2009 at 8:44 pm
    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
    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?
    --
    http://www.freelists.org/webpage/oracle-l
  • Genegurevich_at_discover.com at Jul 6, 2009 at 10:26 pm
    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
    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?
    --
    http://www.freelists.org/webpage/oracle-l

    Please consider the environment before printing this email.

    --
    http://www.freelists.org/webpage/oracle-l
  • Jonathan Lewis at Jul 7, 2009 at 4:57 am
    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:

    Here is my output :

    Allocated Bytes = 1740636160

    Allocated Bytes = 1735000064

    Allocated Bytes = 1736441856

    Allocated Bytes = 1782579200

    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
    --
    http://www.freelists.org/webpage/oracle-l
  • Genegurevich_at_discover.com at Jul 7, 2009 at 2:05 pm
    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
    Jonathan:

    Here is my output :

    Allocated Bytes = 1740636160

    Allocated Bytes = 1735000064

    Allocated Bytes = 1736441856

    Allocated Bytes = 1782579200

    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
    --
    http://www.freelists.org/webpage/oracle-l

    Please consider the environment before printing this email.
    †Ûiÿü0ÁúÞzX¬¶Ê+ƒün– {ú+iÉ^
  • Genegurevich_at_discover.com at Jul 8, 2009 at 10:00 pm
    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.
  • Jonathan Lewis at Jul 2, 2009 at 9:14 pm
    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
    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:
    --
    http://www.freelists.org/webpage/oracle-l
  • Jonathan Lewis at Jul 2, 2009 at 9:17 pm
    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

    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:
    --
    http://www.freelists.org/webpage/oracle-l
    --
    http://www.freelists.org/webpage/oracle-l
  • Jonathan Lewis at Jul 2, 2009 at 9:14 pm
    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
    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:
    --
    http://www.freelists.org/webpage/oracle-l
  • Yong Huang at Jun 22, 2009 at 2:33 am

    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, 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

Related Discussions

People

Translate

site design / logo © 2022 Grokbase