Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2009
    Posts
    11

    Cool Unanswered: size of datafile

    if we doesn't specify the size of datafile while creating a tablespace
    what it's size?

    regards,
    suji

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >what it's size?
    issue OS command like DIR or ls -l to see the answer
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jun 2009
    Posts
    11

    Cool am i write?

    Quote Originally Posted by anacedent
    >what it's size?
    issue OS command like DIR or ls -l to see the answer
    Just now i tried it out?

    By set system as
    >alter system set db_create_file_dest=' ';
    I create a tablespace as
    >create tablespace ss datafile;
    It create a datafile with its own name like c:\.........\01_....._ss.dbf.
    Then see the size of datafile in dba_data_file;
    It shows 104857600 bytes.

    thanks anacedent,
    regards,
    suji.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    or query dba_data_fies

    Code:
    SQL> desc dba_data_files
     Name					   Null?    Type
     ----------------------------------------- -------- ----------------------------
     FILE_NAME					    VARCHAR2(513)
     FILE_ID					    NUMBER
     TABLESPACE_NAME				    VARCHAR2(30)
     BYTES						    NUMBER
     BLOCKS 					    NUMBER
     STATUS 					    VARCHAR2(9)
     RELATIVE_FNO					    NUMBER
     AUTOEXTENSIBLE 				    VARCHAR2(3)
     MAXBYTES					    NUMBER
     MAXBLOCKS					    NUMBER
     INCREMENT_BY					    NUMBER
     USER_BYTES					    NUMBER
     USER_BLOCKS					    NUMBER
     ONLINE_STATUS					    VARCHAR2(7)
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Jun 2009
    Posts
    11

    Smile got ans

    hi anacedent,

    Thanks,I got clear answer.when we create a tablespace with datafile we can't create without specifying the size. i.e.,
    > create tablespace <tsname> datafile 'fully specifyied path';
    It shows the error like,(file does not found,....).
    It is only possible to create a tablespace with datafile specification with size.
    like ,
    >create tablespace <tsname> datafile 'fully specifyied path' size 10m;

    But it is possible to add datafile without size specification. like,
    >alter tablespace <tsname> add datafile;
    note: here datafile create with size 104857600 bytes. i.e., 100m.

    Also it is possible to create a tablespace without size specification. That's by

    >alter system set dba_create_data_file=' path ';
    >create tablespace <tsname> datafile;

    When we assaign initializing parameter " dba_create_data_file" we need not to specify the datafile path and size. It automatically create a datafile in specified path and it automatically assaign the size as 100m.

    The automatically created datafile name will be like this,

    [c:\oracle\product\10.2.0\oradata\ss\ss\datafile\O1 _MF_KING_55GKD94D_.DBF]

    The following queries will make clear,

    SQL> alter system set db_create_file_dest = 'c:\oracle\product\10.2.0\oradata\ss';
    System altered.
    SQL> create tablespace king datafile;
    Tablespace created.
    SQL> select file_name,bytes,tablespace_name from dba_data_files;
    FILE_NAME
    ----------------------------------------------------------------------------------------------------
    BYTES TABLESPACE_NAME
    ---------- ------------------------------
    C:\ORACLE\PRODUCT\10.2.0\ORADATA\SUJI\USERS01.DBF
    5242880 USERS
    C:\ORACLE\PRODUCT\10.2.0\ORADATA\SUJI\SYSAUX01.DBF
    272629760 SYSAUX
    C:\ORACLE\PRODUCT\10.2.0\ORADATA\SUJI\UNDOTBS01.DB F
    26214400 UNDOTBS1
    C:\ORACLE\PRODUCT\10.2.0\ORADATA\SUJI\SYSTEM01.DBF
    503316480 SYSTEM
    C:\ORACLE\PRODUCT\10.2.0\ORADATA\SUJI\SUJI\DATAFIL E\O1_MF_KING_55GKD94D_.DBF
    104857600 KING


    Also the minimum size of a datafile is 81k.

    by
    suji.
    Have a nice day anacedent

  6. #6
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Is it not curious how some people insist on reading manuals for this kind of information, when all you need to do is to spend two days running random SQL statements and OS commands...
    ---
    "It does not work" is not a valid problem statement.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •