Oracle SQL Statement Question ?


Status
Not open for further replies.
#1
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
------------------------------ ------------------------------ ---------------------------------------------------------------------------------------------------------- --- ------------------------------ ----------- -------------- ---------- - ---------- -------------- -------------------------------------------------------------------------------- ------------ ---------------------------------------------------------------- ---------------------------------------------------------------- ---------- ---------- ----------- --------- ----------- -------------------------------------------- -------------------- --- --- ----------- ----------- - --- ---
 

Jul 22, 2003
436
0
0
41
Visit site
#2
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
 

#3
darrelchia said:
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

Senior Member
Mar 16, 2002
2,397
0
0
19
Some equitorial, tropical isle
#4
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.;)
 

Jul 22, 2003
436
0
0
41
Visit site
#6
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.
 

#7
darrelchia said:
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:
 

nyxx88

Senior Member
Nov 17, 2004
2,092
0
0
#9
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:
 

#10
nyxx88 said:
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:
 

Status
Not open for further replies.
Top Bottom