Ok, the logging/nologging parameter has two meanings depending on its context.

In the context you used it above, you requested that the move be done in a nologging fashion -- if the object allowed nologging.

If you "alter table t nologging", then you would have changed the logging/nologging attribute.

As always -- an example might help here.  We'll construct a table, move it and see how much redo that would take and go through various scenario's:

ops$tkyte@ORA8I.WORLD> create table t tablespace UTILS
  2  as
  3  select * from all_objects;

Table created.


<b>so there is the table we'll test with.  It started in the UTILS tablespace and:</b>

ops$tkyte@ORA8I.WORLD> 
ops$tkyte@ORA8I.WORLD> select table_name, logging from user_tables where table_name = 'T';

TABLE_NAME                     LOG
------------------------------ ---
T                              YES

<b>its logging mode is YES (on).  Now, we'll see how much redo our session has generated thus for and save it in a substitution variable V</b>

ops$tkyte@ORA8I.WORLD> 
ops$tkyte@ORA8I.WORLD> column value new_val V
ops$tkyte@ORA8I.WORLD> 
ops$tkyte@ORA8I.WORLD> select a.name, b.value
  2  from v$statname a, v$mystat b
  3  where a.statistic# = b.statistic#
  4  and a.name = 'redo size'
  5  and b.value > 0
  6  /

NAME                                VALUE
------------------------------ ----------
redo size                         3904956


<b>and we'll do your command. This command should be read in english as "move the table T from where it is to the users tablespace, and oh by the way -- if it is allowed, I would like this operation to be done without LOGGING".  Specifically, this command does NOT say "move the table and change the logging mode"</b>

ops$tkyte@ORA8I.WORLD> alter table t move tablespace users NOLOGGING;
Table altered.

ops$tkyte@ORA8I.WORLD> select a.name, b.value, b.value-&V diff
  2  from v$statname a, v$mystat b
  3  where a.statistic# = b.statistic#
  4  and a.name = 'redo size'
  5  and b.value > 0
  6  /
old   1: select a.name, b.value, b.value-&V diff
new   1: select a.name, b.value, b.value-   3904956 diff

NAME                                VALUE       DIFF
------------------------------ ---------- ----------
redo size                         7790212    3885256

<b>Now, here we can see this generated almost 4m of redo -- it would appear this operation was in fact logged, we can further confirm that by CHANGING the logging mode:
</b>

ops$tkyte@ORA8I.WORLD> 
ops$tkyte@ORA8I.WORLD> select table_name, logging from user_tables where table_name = 'T';

TABLE_NAME                     LOG
------------------------------ ---
T                              YES

ops$tkyte@ORA8I.WORLD> 
ops$tkyte@ORA8I.WORLD> alter table t nologging;

Table altered.

ops$tkyte@ORA8I.WORLD> 
ops$tkyte@ORA8I.WORLD> select table_name, logging from user_tables where table_name = 'T';

TABLE_NAME                     LOG
------------------------------ ---
T                              NO


#and moving that table again:</b>

ops$tkyte@ORA8I.WORLD> alter table t move tablespace UTILS NOLOGGING;
Table altered.

ops$tkyte@ORA8I.WORLD> 
ops$tkyte@ORA8I.WORLD> select a.name, b.value, b.value-&V diff
  2  from v$statname a, v$mystat b
  3  where a.statistic# = b.statistic#
  4  and a.name = 'redo size'
  5  and b.value > 0
  6  /
old   1: select a.name, b.value, b.value-&V diff
new   1: select a.name, b.value, b.value-   7790212 diff

NAME                                VALUE       DIFF
------------------------------ ---------- ----------
redo size                         7816656      26444

<b>Now we generate a measely 26k of redo -- just enough to log the data dictionary changes, not the move itself.  Here we moved the object without LOGGING all of the changes.


In fact, we'll find that you cannot simultaneously MOVE an object and alter it in any other fashion (it is a mutually exclusive option, you either MOVE it or you are altering it in some other fashion, but not both)</b>



ops$tkyte@ORA8I.WORLD> 
ops$tkyte@ORA8I.WORLD> alter table t logging move tablespace utils;
alter table t logging move tablespace utils
                      *
ERROR at line 1:
ORA-14133: ALTER TABLE MOVE cannot be combined with other operations

 

depends on the crash and circumstances.

After performaing a NON-LOGGED operation in an ARCHIVELOG mode database (as pretty much EVERY production database should be running in) it is advisable to do a hot backup of the affected tablespaces. This will allow you to perform media recovery on this tablespace.

If you do not -- AND you suffer a media failure (not a simple instance crash) after you do the nonlogged operation -- you will have lost data. We cannot recover it from the archives since it is not in the archives. You must take care when performing these non-logged operations and ensure you have the cooperation and OK of the guys responsible for backing UP the database. Otherwise you can easily lose data.

In a noarchive log mode database, since we can only restore to the point in time of the last full cold backup when you have media failure (and is the exact reason why you need to be in archivelog mode!), this concern is not a concern.

Here is a very very short excerpt from my book on this topic, for all of the details and lots more -- get the book:


Heap Organized Table

A heap organized table is probably used 99% (or more) of the time in applications, although that might change over time with the advent of index organized tables, now that index organized tables can themselves be indexed. A heap organized table is the type of table you get by default when you issue the CREATE TABLE statement. If you want any other type of table structure, you would need to specify that in the CREATE statement itself.

A heap is a classic data structure studied in computer science. It is basically a big area of space, disk or memory (disk in the case of a database table of course), which is managed in an apparently random fashion. Data will be placed where it fits best, not in any sort of order. Many people expect data to come back out of a table in the same order it was put into it, but with a heap this is definitely not assured. In fact, rather the opposite is guaranteed ? the rows will come out in a wholly unpredictable order. This is quite easy to demonstrate. I will set up a table such that in my database I can fit one full row per block (I am using an 8k blocksize). You do not need to have the case where you only have one row per block ? I am just taking advantage of that to demonstrate a predictable sequence of events. The following behavior will be observed on tables of all sizes, in databases with any blocksize:
.......

because the blocks that have NEVER had any data will be above the high water mark, not on the freelists.

Freelists only get blocks put on them after they are used, if they've never been used, they won't be on the freelist.

After a fresh rebuild like you just did, it is 100% natural to have very very FEW blocks on the freelist, if any.  It just means that all of the existing blocks with data are "packed" -- cannot accept any new inserts.  When you start updating/deleting data, some blocks will start ending up on the free lists.

Consider (system is a dictionary MT and users is a locally MT)

ops$tkyte@ORA817DEV.US.ORACLE.COM> drop table t;
Table dropped.

ops$tkyte@ORA817DEV.US.ORACLE.COM> create table t tablespace system as select * from all_objects;
Table created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> analyze table t compute statistics;
Table analyzed.

ops$tkyte@ORA817DEV.US.ORACLE.COM> select num_freelist_blocks from dba_tables
  2  where owner = user and table_name = 'T';
NUM_FREELIST_BLOCKS
-------------------
                  0

<b>fully packed table, no blocks on freelist right now...
</b>

ops$tkyte@ORA817DEV.US.ORACLE.COM> delete from t where rownum < 100;

99 rows deleted.

ops$tkyte@ORA817DEV.US.ORACLE.COM> analyze table t compute statistics;

Table analyzed.

ops$tkyte@ORA817DEV.US.ORACLE.COM> select num_freelist_blocks from dba_tables
  2  where owner = user and table_name = 'T';

NUM_FREELIST_BLOCKS
-------------------
                  1

<b>and now there are- we put some blocks on the freelist by deleting from them</b>

ops$tkyte@ORA817DEV.US.ORACLE.COM> alter table t move tablespace users;
Table altered.

ops$tkyte@ORA817DEV.US.ORACLE.COM> analyze table t compute statistics;
Table analyzed.

ops$tkyte@ORA817DEV.US.ORACLE.COM> select num_freelist_blocks from dba_tables
  2  where owner = user and table_name = 'T';

NUM_FREELIST_BLOCKS
-------------------
                  0

<b>now, they are gone, all free blocks are above the HWM, not in the freelists</b>

ops$tkyte@ORA817DEV.US.ORACLE.COM> delete from t where rownum < 100;
99 rows deleted.

ops$tkyte@ORA817DEV.US.ORACLE.COM> analyze table t compute statistics;
Table analyzed.

ops$tkyte@ORA817DEV.US.ORACLE.COM> select num_freelist_blocks from dba_tables
  2  where owner = user and table_name = 'T';

NUM_FREELIST_BLOCKS
-------------------
                  1
<b>and here come some back, the table is not "packed" anymore, the delete freed up some room</b>
 

The method you used is just fine. You could do a TABLE level export, instead of a user level export and that would work but what you did is just fine (as long as you got all of the constraints, triggers, grants, etc -- something EXP would get for you)

After renaming the table, you need do nothing. Views will fix themselves, as will any stored procedures.

As for the "size" difference -- the newly created table is freshly "compacted". It is not unusual for them to be "smaller". As for "is this a good way to save space", not in my opinion. Come back in a couple of weeks/months and you'll find the table grows back to this size again. Its like going on a diet, we lose the weight for a little bit but usually end up going back to our "comfortable" size. Re-orging tables on a recurring basis is

a) something I do not encourage
b) something I discourage (many "opps, i lost some data" errors happen when doing
this stuff)
c) only "saves" space for a day or two, everything wants to get back to its
steady state size over time anyway.

No, nologging only affects MEDIA recovery -- not instance failure.

Whilst moving the table in nologging, the table is copied from its permanent segment to a TEMPORARY segment. At the very end of the operation, the temporary segment is converted into a permanent segment -- that is when the new copy becomes the real table.

If during the move, we crash - SMON will simply clean up the temp segment and it will be as if we never touched the table(the permanent segment is in place)

If after the move, we crash -- it is OK, the data was written directly to disk and there is no need for instance recovery on it.

If after the move we update some data, commit and then crash -- it is OK, the redo for the updates is available and we can recover.

If after the move and before we BACKUP the files we just did the nologging operation into we have a DISK CRASH -- then, then "we have a problem houston". That is why in a production environment you might consider not using NOLOGGING or at least you will:

o backup the objects first
o perform the nologging operation
o backup the objects again

Yes, during an alter table move, you can read the data.

Only if the operation is done "online" (alter index rebuild online for example, alter table move online -- only for indexed organized tables) can you also do DML.

In Oracle9i, there is a dbms_redefinition package for doing online rebuilds of most any object as well (allowing for DML whilst doing the move)

You are in noarchivelog mode.

There is no need to generate redo in that mode for this operation so it doesn't -- regardless of the logging/nologging setting..

parallel operations are a feature of EE and PE.

http://docs.oracle.com/docs/cd/A87860_01/doc/server.817/a76962/ch4.htm#52939

so, in SE, no parallel for you.


but why did you get the impression that insert /*+ append */ would be best?

i would just make the table T nologging and then move it:


ops$tkyte@ORA920> alter table t move nologging parallel 4;
Table altered.

easier then insert append, don't lose grants, don't lose indexes.

(you can see if something is running in parallel by querying v$px_processes whilst it is running) 

I would say the difference between 137 and 125 (wall clock seconds) is not relevant. 12 seconds isn't relevant -- especially on a computer where other things are going on.

But -- your one test shows -- alter table move parallel 2 -- is 2 times as fast (your first parallel move was penalized probably by the fact that it started the PQ slaves -- also, since 4 took that much longer, looks like disk contention on the source or target disks)...

I would not conclude from this at all that insert append is faster. I would say it is much harder, less functional and not the right way to do it though.

it has to hit the table to get the rowid info

export it, import it.

longs have limits, clobs are better.

you can specify it on the move, or it'll pick it up from the table def itself:

ops$tkyte@ORA920LAP> select pct_free from user_tables where table_name = 'EMP';

  PCT_FREE
----------
        10

ops$tkyte@ORA920LAP>
ops$tkyte@ORA920LAP> alter table emp move pctfree 50 ;

Table altered.

ops$tkyte@ORA920LAP> select pct_free from user_tables where table_name = 'EMP';

  PCT_FREE
----------
        50

ops$tkyte@ORA920LAP> alter table emp pctfree 20;

Table altered.

ops$tkyte@ORA920LAP> alter table emp move;

Table altered.

ops$tkyte@ORA920LAP> select pct_free from user_tables where table_name = 'EMP';

  PCT_FREE
----------
        20

ops$tkyte@ORA920LAP> 

I'm confused cause your example did not include an alter table MOVE at all?

alter table MOVE will redistribute data.  "alter table" without MOVE will not (that is what your example shows)

this shows the data is redistributed with an alter table move:


ops$tkyte@ORA9IR2> create table t (id number, pad char(50) default 'x') pctfree 10 pctused 40;
 
Table created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select table_name ,tablespace_name ,pct_free ,pct_used
  2    from user_tables
  3   where table_name ='T'
  4  /
 
TABLE_NAME                     TABLESPACE_NAME                  PCT_FREE
------------------------------ ------------------------------ ----------
  PCT_USED
----------
T                              USERS                                  10
 
 
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> insert into t(id) select rownum from all_objects where rownum < 1001;
 
1000 rows created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select min(cnt), max(cnt), avg(cnt)
  2    from (
  3  select count(*) cnt
  4    from t
  5   group by  dbms_rowid.rowid_block_number(rowid)
  6         )
  7  /
 
  MIN(CNT)   MAX(CNT)   AVG(CNT)
---------- ---------- ----------
        31        122 111.111111
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> alter table t move pctfree 3 pctused 30;
 
Table altered.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select table_name ,tablespace_name ,pct_free ,pct_used
  2    from user_tables
  3   where table_name ='T'
  4  /
 
TABLE_NAME                     TABLESPACE_NAME                  PCT_FREE
------------------------------ ------------------------------ ----------
  PCT_USED
----------
T                              USERS                                   3
 
 
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select min(cnt), max(cnt), avg(cnt)
  2    from (
  3  select count(*) cnt
  4    from t
  5   group by  dbms_rowid.rowid_block_number(rowid)
  6         )
  7  /
 
  MIN(CNT)   MAX(CNT)   AVG(CNT)
---------- ---------- ----------
        89        131        125
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> alter table t move pctfree 50 pctused 30;
 
Table altered.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select table_name ,tablespace_name ,pct_free ,pct_used
  2    from user_tables
  3   where table_name ='T'
  4  /
 
TABLE_NAME                     TABLESPACE_NAME                  PCT_FREE
------------------------------ ------------------------------ ----------
  PCT_USED
----------
T                              USERS                                  50
 
 
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select min(cnt), max(cnt), avg(cnt)
  2    from (
  3  select count(*) cnt
  4    from t
  5   group by  dbms_rowid.rowid_block_number(rowid)
  6         )
  7  /
 
  MIN(CNT)   MAX(CNT)   AVG(CNT)
---------- ---------- ----------
         7         68       62.5
 

only the blocks in the affected table will be "not recovered".

so, you don't really even need to put it in a special tablespace -- but, if you did, you could even skip backing up this tablespace alltogether, like temp.

sorry -- it doesn't work that way.

it copies rows - it would "compact" space (putting pctfree back)

if the table is the same size after moving, it didn't have any free space to reclaim.

think of it like a create table as select

it reads the data and rebuilds the table completely. It is a "reorg"


How does it compare to exp/imp or sqlldr?

o it is SAFE (anything that takes my data out of the database scares me)
o it doesn't lose grants
o it doesn't lose anything in fact
o it is easy
o it is fast

I would say none of the above apply to the other methods you mentioned, I would not even consider them.

WE NEED TO FIND THAT CAPSLOCK KEY AND HIT IT :)

disable constraints - no, it knows the data is OK, the data alread exists after all...

recompile the objects - no, you never need to, anything that goes invalid will take care of itself all by itself.

what is the difference between export and import and move? - exp/imp would be wrong, this would be right. It is faster, safer, smarter. exp/imp is a logical data copy tool - you'll stand a good chance of "losing stuff" and going to backups. just don't do it.

stop using exp/imp, that is so 1990. Alter table move, tastes great, less filling. Superior, smarter, makes you look good in the morning.



immediately - huh, can I bill you for this immediately?


I've answered - just use move, stop thinking about exp/imp.

And - start TESTING, you'd be able to answer all of your own questions as you test this, because you are going to test this before inflicting this on your end users right?

so many #1's in the list..

alter table move is valid with 8i and above.


1) yes, it can compress
2) yes, it rebuilds the entire thing, of course the "hwm" is set anew.
3) an object at a time ,schemas are not tied to a single tablespace, so there is not any "move this schema", there is only "move this object"
4) laughing out loud. define fragmentation - hope you don't mean "more than one extent" because my advice will ON PURPOSE cause many extents to be allocated ( system allocated locally managed tablespace, initial 1k, next 1k, pctincrease 0). Row chaining - maybe, if the rows are MIGRATED, yes, if the row is chained because it is bigger than a block - no.
5) probably
6) it is transactional, the move either entirely succeeds or not. that is the perfection of move over exp/imp. tastes great, less filling. IT IS SAFE, this is what I meant by IT IS SAFE.

I doubt you would want shared server, you have connection pools already, shared server is "connection pooling for client server applications"

would you "cache a cache"?
why would you "pool a pool" :)

you tell me where those numbers come from first. I cannot imagine your single application server really has 1,400 simultaneous users hitting "enter" at the same time.

"SHOW ME"

I don't see how your single middle tier machine would support 1,400 concurrent users hitting "enter"

what are you doing in the middle tier?
are you NOT using a connection pool??

umm, what pray tell do you mean by "obviously not"

The layout you just described almost ALWAYS uses a connection pool. Are you sure you have your facts here.

I do not believe you have 1400 active sessions, I do not believe you are relating the true "architecture" of this system.

sorry, but we don't know the size/scope of your machines (especially your ONE application server with 1400 concurrent active users!!!)

we don't know the size/scope of your database machine (other than "2 nodes")

And I don't believe you are not using connection pooling??? what is this application, what is it written in, how could it not be using connection pooling.

connection pooling cuts out the "connect" step.

you don't need to login, logout.

how can a table lead to serious memory fragmentation?

describe.

because the renaming of a column doesn't really change anything for the already running query.

Let me ask you this - why do you think it shouldn't/wouldn't work?

nope - such minutiae is not documented in any fashion, it just works as it should work.

yes, if you move the table, you have to rebuild the indexes and the indexes have to be rebuilt from the table data - not from the existing index.

That way, rebuilding an index after a table move is almost similar to dropping
and recreating index.

IT IS NOT.

If you drop and recreate the index you

a) stand of chance of having the drop succeed and the create fail and having someone maybe not notice until they go to query the table and performance is horrible.

b) have to remember all of the settings for the index - tablespace, sizing, whatever

If you just leave them be - you

a) cannot lose them
b) do not have to do much to get them back in shape, a simple "alter index i rebuild" is all you need.



It would NOT be advisable to drop an index and recreate it over just rebuilding it after a move operation.

those did not come from the alter table t move commands.

those came from people dropping tables, query dba_recyclebin and you'll see what tables they were before being dropped.

they exist do you can "undrop" a table

They are harmless

They are not wasting any space (their extents are in dba_free_space, they will be "recycled" as the space is needed by some other segment)

You can read about the PURGE command to clear out the recycle bin - but it is probably best to just leave them be, they are normal, natural and nothing to be cleared out on a regular basis.

The only time you might want to get rid of them is if you wanted to shrink a datafile and one of them occupied the "last extent" allocated in the file (preventing it from shrinking)