SQL Statement is truncating result..why?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: SQL Statement is truncating result..why?

  1. #1
    Join Date
    Oct 2000
    Posts
    26
    i run this sql as part of a recovery script...

    select 'create tablespace ' || T.tablespace_name || chr(10) ||
    'datafile ''' || F.file_name || ''' size ' || to_char(F.bytes/1048576)
    || 'M' || chr(10) ||
    'default storage (Initial ' || to_char(T.initial_extent) ||
    ' next ' || to_char(T.next_extent) || ' minextents ' ||
    to_char(T.min_extents) || chr(10) ||
    ' maxextents ' || to_char(T.max_extents) || ' pctincrease ' ||
    to_char(T.pct_increase) || ') online;'
    from
    sys.dba_data_files F,
    sys.dba_tablespaces T
    where
    T.tablespace_name = F.tablespace_name
    and T.tablespace_name != 'SYSTEM'
    and F.file_id = ( select min(file_id)
    from sys.dba_data_files
    where tablespace_name = T.tablespace_name );

    I get this output.....

    datafile 'H:\USERS' size 500M
    default storage (Initial
    create tablespace RBS_1
    datafile 'G:\RBS_1' size 500M
    default storage (Initial
    create tablespace LIVE_DATA
    datafile 'F:\LIVE_DATA_1' size 800M
    default storage
    create tablespace LIVE_INDEX
    datafile 'I:\LIVE_INDEX_1' size 800M
    default stora
    create tablespace RBS_2
    datafile 'G:\RBS_2' size 500M
    default storage (Initial
    create tablespace TEMPB
    datafile 'H:\TEMPB' size 200M
    default storage (Initial


    see how it is truncating the storage parameters....can anyone see the flaw in the sql statement?


    thank you in advance...

    John

  2. #2
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    John,

    I don't know ! what to tell you. I don't have NT database handy to try.. I just ran it on 734/solaris 2.6. It workd fine. here is output...

    'CREATETABLESPACE'||T.TABLESPACE_NAME||CHR(10)||'DATAFILE'''||F.FILE_NAME||'''SIZE'||TO_C
    ----------------------------------------------------------------------------------------------------
    create tablespace HR11
    datafile '/oracle2/hrts/data/hr11_01.dbf' size 150M
    default storage (Initial 524288 next 524288 minextents 1
    maxextents 249 pctincrease 0) online;

    create tablespace HR11INDX
    datafile '/oracle3/hrts/data/hr11indx_01.dbf' size 65M
    default storage (Initial 262144 next 262144 minextents 1
    maxextents 249 pctincrease 0) online;

    create tablespace HR1CSI
    datafile '/oracle3/hrts/data/hr1csi_01.dbf' size 6M
    default storage (Initial 131072 next 131072 minextents 1
    maxextents 249 pctincrease 0) online;
    -----
    -----
    -----

  3. #3
    Join Date
    Nov 2000
    Location
    Atlanta, GA, USA
    Posts
    85
    I also run this script against an 8.1.6 NT Database while connected from a win98 machine with an 8.0.4 client software.
    The results are as follows.

    When run from SQLWorkSheet, the result were being truncated on many lines but not all.

    When run from SQLPlus, the result was OK.

    John: see if you can the same script in SQLPlus if you run it in another tools the first time.
    Also, the SQLPlus environement settings might have an effect.

  4. #4
    Join Date
    Jan 2000
    Location
    Silver Spring MD USA
    Posts
    105
    If you're using SQL*Plus try to setting the linesize until you see what you expect.
    The command is simply

    set linesize 120

    Try running the script after setting the linesize to different numbers to see if it makes a difference.

  5. #5
    Join Date
    Oct 2000
    Posts
    26

    it is plus vs worksheet

    strange but true....tahnks for the help :0

Posting Permissions

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



Click Here to Expand Forum to Full Width