Re: How to change field size using SQL against an oracle Database

BookerT wrote:
> I open up Oracle SQL*Plus and i want to change the field size for one
> of my columns in one of my databases. I have a generic SQL book, but
> when I try some of the commands, they don't seem to work in SQL*PLUS
>
> How can I perform this procedure on a table called INQUIRIES within my
> database. I want to change the field size from 10 , to say , 60

Generic SQL books will offer you a good start, but you will soon recognize that SQL statements and commands are not fully portable from one database platform to another.

The command to alter a table's column width (field size, number of characters that can be stored) looks like this: ALTER TABLE
  MY_TABLE
MODIFY (
  MY_COLUMN VARCHAR2(60)); In the above, replace MY_TABLE with the appropriate table name, and MY_COLUMN with the appropriate column (field) name.

> I even wanted to jus ttry the EXPLAIN command to view the table, but
> when I type EXPLAIN INQUIRIES, i get a missing keyword error.

DESC MY_TABLE The above command lists the columns in the table MY_TABLE and the maximum column sizes and numeric precision.

SELECT
  *
FROM
  USER_TABLES
WHERE
  TABLE_NAME='MY_TABLE'; The above lists information specific to the table: owner, number of rows, approximate row length, etc.

SELECT
  *
FROM
  USER_TAB_COLUMNS
WHERE
  TABLE_NAME='MY_TABLE'; The above lists all columns in the table, column order, and the maximum column sizes and numeric precision. Additionally, if statistics are collected, the minimum and maximum values for the column, density, and various other parameters.

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Thu Dec 14 2006 - 20:36:26 CST