Results 1 to 10 of 10

Thread: Oracle SQL Statement Question ?

  1. #1
    Senior Member
    Join Date
    Mar 2003
    Location
    Singapore (SengKang)
    Posts
    2,789

    Default Oracle SQL Statement Question ?

    Wonder if there any oracle sql expert around ?
    Like to ask one query regarding adding in a new column in a table
    I know the command is something like
    eg. ALTER TABLE <table_name> ADD <column_name> NUMBER(5) NULL;
    When i perform a
    SELECT * FROM USER_TAB_COLUMNS WHERE TABLE_NAME LIKE '<table_name>'
    I noticed there are quite a number of other fields (in bold below) for that new column which is not filled up.
    Wonder how do I go about modifying them ? Thanks in advance !

    TABLE_NAME COLUMN_NAME DATA_TYPE DAT DATA_TYPE_OWNER DATA_LENGTH DATA_PRECISION DATA_SCALE N COLUMN_ID DEFAULT_LENGTH DATA_DEFAULT NUM_DISTINCT LOW_VALUE HIGH_VALUE DENSITY NUM_NULLS NUM_BUCKETS LAST_ANAL SAMPLE_SIZE CHARACTER_SET_NAME CHAR_COL_DECL_LENGTH GLO USE AVG_COL_LEN CHAR_LENGTH C V80 DAT
    ------------------------------ ------------------------------ ---------------------------------------------------------------------------------------------------------- --- ------------------------------ ----------- -------------- ---------- - ---------- -------------- -------------------------------------------------------------------------------- ------------ ---------------------------------------------------------------- ---------------------------------------------------------------- ---------- ---------- ----------- --------- ----------- -------------------------------------------- -------------------- --- --- ----------- ----------- - --- ---
    -Express yourself not in words-
    http://www.majere2sg.com

  2. #2

    Default Re: Oracle SQL Statement Question ?

    1st qns:
    http://www.techonthenet.com/sql/tables/alter_table.php


    sorry... dun understand the 2nd part of your question ... you want to insert data ? alter the table ? drop columns ? or what...

    my general advice is to download some GUI sql tool. Oracle SQLDeveloper is free for download on their website...

    if not u can try TOAD from quest software
    Last edited by darrelchia; 30th August 2006 at 04:16 PM.

  3. #3
    Senior Member
    Join Date
    Mar 2003
    Location
    Singapore (SengKang)
    Posts
    2,789

    Default Re: Oracle SQL Statement Question ?

    Quote Originally Posted by darrelchia
    sorry... dun understand the 2nd part of your question ... you want to insert data ? alter the table ? drop columns ? or what...

    my general advice is to download some GUI sql tool. Oracle SQLDeveloper is free for download on their website...

    if not u can try TOAD from quest software
    No no.. I want to add column..
    I only have one question, is when adding column, how do I modify other fields.. besides data type and data length.
    ALTER TABLE <table_name> ADD <column_name> NUMBER(5) NULL; sets the data type to Number, data length as 5. I want to know how can I modify other fields such as
    DEFAULT_LENGTH DATA_DEFAULT NUM_DISTINCT LOW_VALUE HIGH_VALUE DENSITY NUM_NULLS NUM_BUCKETS LAST_ANAL SAMPLE_SIZE CHARACTER_SET_NAME CHAR_COL_DECL_LENGTH GLO USE AVG_COL_LEN CHAR_LENGTH C V80 DAT
    Will need to put them in a script to run..Thanks.
    -Express yourself not in words-
    http://www.majere2sg.com

  4. #4
    Senior Member
    Join Date
    Mar 2002
    Location
    Some equitorial, tropical isle
    Posts
    2,398

    Default Re: Oracle SQL Statement Question ?

    Thats coz when you alter the table by adding the new column, you did not specify any other parameters for that column... so its empty.

  5. #5

    Default Re: Oracle SQL Statement Question ?

    Your SELECT statement looks like querying a ORACLE system table.

    Maybe u wanted to DESC <table_name> instead?
    Great photography is about depth of feeling, not depth of field.
    Portfolio | Gallery

  6. #6

    Default Re: Oracle SQL Statement Question ?

    ok ... i understand what u're asking liao

    yes, the alter statement SQL syntax is only limited to changing the column type/length. You can't change any other stuff there. The other stuff are part of the data dictionary, which is maintained by the database agent itself, and I don't recommend manually changing it.
    Last edited by darrelchia; 30th August 2006 at 05:41 PM.

  7. #7
    Senior Member
    Join Date
    Mar 2003
    Location
    Singapore (SengKang)
    Posts
    2,789

    Default Re: Oracle SQL Statement Question ?

    Quote Originally Posted by darrelchia
    ok ... i understand what u're asking liao

    yes, the alter statement SQL syntax is only limited to changing the column type/length. You can't change any other stuff there. The other stuff are part of the data dictionary, which is maintained by the database agent itself, and I don't recommend manually changing it.
    Thanks.. Thats what I am trying to find out. Because I noticed the fields for the other columns are not the same as the new one I added, so just trying to figure out if I can make them the same. Not sure if there will be any problems if they are not the same..
    -Express yourself not in words-
    http://www.majere2sg.com

  8. #8

    Default Re: Oracle SQL Statement Question ?

    http://www.lc.leidenuniv.nl/awcourse...2a.htm#2054899

    or use toad/sql developer to alter the table
    Last edited by quekky; 31st August 2006 at 10:40 PM.

  9. #9

    Default Re: Oracle SQL Statement Question ?

    not recommended to go around messing with the other system table columns unless you are SURE of what you are doing.

    since the alter table command already achieve your objective, leave the other stuff alone, else we might see another thread here in CS kopitiam asking for help in recovering some Oracle table

  10. #10
    Senior Member
    Join Date
    Mar 2003
    Location
    Singapore (SengKang)
    Posts
    2,789

    Default Re: Oracle SQL Statement Question ?

    Quote Originally Posted by nyxx88
    not recommended to go around messing with the other system table columns unless you are SURE of what you are doing.

    since the alter table command already achieve your objective, leave the other stuff alone, else we might see another thread here in CS kopitiam asking for help in recovering some Oracle table
    Thanks.. Think will seek the dba advice before doing anything..
    -Express yourself not in words-
    http://www.majere2sg.com

Bookmarks

Posting Permissions

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