iSelfSchooling.com  Since 1999     References  |  Search more  | Oracle Syntax  | Free Online Oracle Problem Solver

    .Home      .Subscribe     .Login      .Start Learning      .Certification      .Cancel      .Password Reminder      .Password Change     .Sign out

 

. Online Accounting        .Copyright & User Agreement   |
    .Vision      .Biography     .Acknowledgement

.Contact Us      .Comments/Suggestions       .Email2aFriend    |

 

Online Oracle Training for beginners and advanced - The most comprehensive Oracle tutorial

The authors do not guarantee or take any responsibility for the accuracy, or completeness of the information.

DBAs - Fundamentals

 

Lesson 01 | Lesson 02 | Lesson 03 | Lesson 04 | Lesson 05 | Lesson 06 | Lesson 07 | Lesson 08 | Lesson 09 | Lesson 10 | Lesson 11 | Lesson 12 | Lesson 13 | Lesson 14 | Lesson 15 | Lesson 16 | Lesson 17 | Lesson 18 | Lesson 19 | Lesson 20 | Lesson 21 | Lesson 22 | Lesson 23 | Lesson 24 | Lesson 25 | Lesson 26 |

 

Lesson 25

"When ideas fail, words come in very handy." - Goethe (1749-1832)

 

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:

 

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

SQL> ed

Wrote file afiedt.buf

CREATE TABLE table_with_long

              (c1 VARCHAR2(100) PRIMARY KEY,

              c2 number(5),

              c3 LONG)

SQL> /

Table created.

 

Write a PL/SQL procedure to insert 100 records.

SQL> ed

Wrote file afiedt.buf

BEGIN

              FOR this IN 1..100 LOOP

                            INSERT INTO table_with_long

                            VALUES

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

                            -- save transaction

                            COMMIT;

              END LOOP;

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

              (c1 VARCHAR2(100) PRIMARY KEY,

              c2 NUMBER(5),

              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

              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

              CURSOR c_table_with_long

                            IS SELECT * FROM table_with_long;

 

BEGIN

              FOR this IN c_table_with_long LOOP

                            INSERT INTO table_with_long$$recovery

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

                            COMMIT;

              END LOOP;

END;

/

 

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

DECLARE

              CURSOR c_table_with_long$$recovery

                            IS SELECT * FROM table_with_long$$recovery;

BEGIN

              FOR this IN c_table_with_long$$recovery LOOP

                            INSERT INTO table_with_long

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

                            COMMIT;

              END LOOP;

END;

/

 

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.

 

"The nice thing about egotists is that they don't talk about other people." - Lucille S. Harper

 

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?