everythingOracle.bizhat.com

 

'Everything you wanted
to know about Oracle'

Training References Syntax Tell A Friend Contact Us

 

 

DBA Fundamentals

01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27
28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57
<< Previous

Chapter # 25

Next >>


 

Organizing tables and indexes

 

Introduction

ALTER or MOVE statement

Normally, to re-organize a table, you should use ALTER command with MOVE options.

SQL> alter table mytable move;

Check previous hands-on for more information.

 

dbms_stats.gather_schema_stats procedure

You can use GATHER_SCHEMA_STATS procedure in the DBMS_STATS package to analyze all tables and indexes in a specific schema.

SQL> EXECUTE dbms_stats.gather_schema_stats

('your_schema_name',cascade=>true);

- OR -

SQL> EXECUTE dbms_stats.gather_schema_stats ('your_schema_name');

 

LONG datatype in a table

Unfortunately, you can�t do this is you have a long datatype. If your table contains a LONG datatype you get the following error

message: ORA-00997: illegal use of LONG datatype

For example:

SQL> alter table table_with_long move;

alter table table_with_long move

*

ERROR at line 1:

ORA-00997: illegal use of LONG datatype

To re-organize a table with a LONG datatype do the following steps:

STEP #1:

Assuming this is your table that can not be re-organized.

SQL> ed

Wrote file afiedt.buf

1 CREATE TABLE table_with_long

2 (c1 VARCHAR2(100) PRIMARY KEY,

3 c2 number(5),

4* c3 LONG)

SQL> /

Table created.

Write a PL/SQL procedure to insert 100 records.

SQL> ed

Wrote file afiedt.buf

1 BEGIN

2 FOR this IN 1..100 LOOP

3 INSERT INTO table_with_long

4 VALUES

('PK_' || this, this, 'This is a very long long long data...');

5 -- save transaction

6 COMMIT;

7 END LOOP;

8* END;

SQL> /

PL/SQL procedure successfully completed.

Query the table...

SQL> COL c1 FORMAT a10

SQL> COL c3 FORMAT a50

SQL> SET PAGESIZE 1000

SQL> SELECT * FROM table_with_long;

C1 C2 C3

---------- ---------- --------------------------------------------------

PK_1 1 This is a very long long long data...

PK_2 2 This is a very long long long data...

PK_3 3 This is a very long long long data...

PK_4 4 This is a very long long long data...

PK_5 5 This is a very long long long data...

PK_6 6 This is a very long long long data...

PK_7 7 This is a very long long long data...

PK_8 8 This is a very long long long data...

PK_9 9 This is a very long long long data...

PK_10 10 This is a very long long long data...

PK_11 11 This is a very long long long data...

PK_12 12 This is a very long long long data...

PK_13 13 This is a very long long long data...

PK_14 14 This is a very long long long data...

PK_15 15 This is a very long long long data...

PK_16 16 This is a very long long long data...

PK_17 17 This is a very long long long data...

PK_18 18 This is a very long long long data...

PK_19 19 This is a very long long long data...

PK_20 20 This is a very long long long data...

...

...

100 rows selected.

Assuming that there is lots of fragmentation and you need to re-organize the table ("table_with_long"). Create a table with the same columns name and datatypes...

SQL> DESC table_with_long

Name Null? Type

----------------------------------------- -------- ----------------------------

C1 NOT NULL VARCHAR2(100)

C2 NUMBER(5)

C3 LONG

SQL> CREATE TABLE table_with_long$$recovery

2 (c1 VARCHAR2(100) PRIMARY KEY,

3 c2 NUMBER(5),

4 c3 LONG);

 

Table created.

Write a PL/SQL procedure to copy all data from table_with_long to table_with_long$$recovery. Notice that you can not use insert DML statement to move data. You will get the following error message: ORA-00997: illegal use of LONG datatype

SQL> INSERT INTO table_with_long$$recovery

2 SELECT * FROM table_with_long;

SELECT * FROM table_with_long

*

ERROR at line 2:

ORA-00997: illegal use of LONG datatype

This procedure will perform the copy process.

SQL> DECLARE

2 CURSOR c_table_with_long

3 IS SELECT * FROM table_with_long;

4

5 BEGIN

6 FOR this IN c_table_with_long LOOP

7 INSERT INTO table_with_long$$recovery

8 VALUES (this.c1, this.c2, this.c3);

9 COMMIT;

10 END LOOP;

11 END;

12 /

 

PL/SQL procedure successfully completed.

Query table_with_long$$recovery to see the data.

SQL> SELECT * FROM table_with_long$$recovery;

C1 C2 C3

---------- ---------- --------------------------------------------------

PK_1 1 This is a very long long long data...

PK_2 2 This is a very long long long data...

PK_3 3 This is a very long long long data...

PK_4 4 This is a very long long long data...

PK_5 5 This is a very long long long data...

PK_6 6 This is a very long long long data...

PK_7 7 This is a very long long long data...

PK_8 8 This is a very long long long data...

PK_9 9 This is a very long long long data...

...

...

...

100 rows selected.

Truncate table_with_long;

SQL> TRUNCATE TABLE table_with_long;

Table truncated.

Check to make sure there are no data in that table.

SQL> SELECT * FROM table_with_long;

no rows selected

Now, write a PL/SQL procedure to copy all data from table_with_long$$recovery to the truncated table.

SQL> ed

Wrote file afiedt.buf

1 DECLARE

2 CURSOR c_table_with_long$$recovery

3 IS SELECT * FROM table_with_long$$recovery;

4 BEGIN

5 FOR this IN c_table_with_long$$recovery LOOP

6 INSERT INTO table_with_long

7 VALUES (this.c1, this.c2, this.c3);

8 COMMIT;

9 END LOOP;

10* END;

11 /

 

PL/SQL procedure successfully completed.

Now, check to see the data is back to its table.

SQL> SELECT * FROM table_with_long;

C1 C2 C3

---------- ---------- --------------------------------------------------

PK_1 1 This is a very long long long data...

PK_2 2 This is a very long long long data...

PK_3 3 This is a very long long long data...

PK_4 4 This is a very long long long data...

PK_5 5 This is a very long long long data...

PK_6 6 This is a very long long long data...

PK_7 7 This is a very long long long data...

PK_8 8 This is a very long long long data...

PK_9 9 This is a very long long long data...

...

...

...

100 rows selected.

This is one way to re-organize table with a long datatype.

 

Questions:

Q: How do you re-organize a table?

Q: How do you re-organize a table when the table contains a LONG datatype?

Q: Describe the DBMS_STATS package/

Q: What does the GATHER_SCHEMA_STATS procedure in the DBMS_STATS package?

Q: What do the following SQL and PL/SQL statements do?

SQL> EXECUTE dbms_stats.gather_schema_stats

('your_schema_name',cascade=>true);

SQL>

1 BEGIN

2 FOR this IN 1..100 LOOP

3 INSERT INTO table_with_long

4 VALUES

('PK_' || this, this, 'This is a very long long long data...');

5 -- save transaction

6 COMMIT;

7 END LOOP;

8* END;

1 DECLARE

2 CURSOR c_table_with_long$$recovery

3 IS SELECT * FROM table_with_long$$recovery;

4 BEGIN

5 FOR this IN c_table_with_long$$recovery LOOP

6 INSERT INTO table_with_long

7 VALUES (this.c1, this.c2, this.c3);

8 COMMIT;

9 END LOOP;

10* END;

     Reviews and Templates for FrontPage
     

Copyright � everythingOracle.bizhat.com 2006 All Rights Reserved.