Oracle SQL Statement Question ?

[majere2sg]

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 ? :dunno: Thanks in advance ! :lovegrin:

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

 

[darrelchia]

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

 

[majere2sg]

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.

 

[Zplus]

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.

 

[silver.wolf]

Your SELECT statement looks like querying a ORACLE system table.

Maybe u wanted to DESC <table_name> instead?

 

[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.

 

[majere2sg]

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.. :sweat:

 

[quekky]

http://www.lc.leidenuniv.nl/awcourse/oracle/server.920/a96540/statements_32a.htm#2054899

or use toad/sql developer to alter the table

 

[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 :bsmilie:

 

[majere2sg]

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 :bsmilie:

Thanks.. Think will seek the dba advice before doing anything.. :bsmilie: