Database Administration (MOSC)

MOSC Banner

What happens behind the scenes in an alter table?

edited Apr 17, 2018 8:12AM in Database Administration (MOSC) 9 commentsAnswered ✓

I have a large-ish table (600,000 rows) with a column defined as "varchar2(2500 byte)" when it should be "varchar2(2500 char)".  This is (fairly obviously) causing problems when inserting non-ASCII characters in some situations.

I'm trying to work out what happens behind the scenes when the command is executed:

  alter table tab_name modify     column_name varchar2(2500 char)

In my underpowered VM test database, this command returns pretty much instantly, which isn't what I expected at all. 

I expected this to require a full table scan and an exclusive lock to ensure the data doesn't change during the DDL operation, but in testing on my Virtualbox DB with a similar number of rows of synthetically generated data, the query returns within under 1/10 of a second, which seems far too quick for a FTS on a table of this size.

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center