How can I re-organize a table that contains a LONG datatype and also how can I analyze all my Oracle tables and indexes?

How can I analyze all my Oracle tables and indexes?

Use GATHER_SCHEMA_STATS procedure in the DBMS_STATS package to analyze

all tables and indexes in a specific schema.

EXECUTE dbms_stats.gather_schema_stats('your_schema_name',cascade=>true);

How can I re-organize a table that contains a LONG datatype?

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

option.

But 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:

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.

SQL> -- 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.

SQL> -- Query 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.

SQL> -- Assuming that there are lots of fragmentation and you need

SQL> -- to re-organize the table ("table_with_long").

SQL>

SQL> -- 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.

SQL> -- Write a PL/SQL procedure to copy all data from

SQL> -- table_with_long to table_with_long$$recovery.

SQL>

SQL> -- Notice that you can not use insert DML statement

SQL> -- to move data.

SQL> -- 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

SQL> -- This procedure will perform the copy process.

SQL>

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.

SQL> -- 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.

SQL> -- Truncate table_with_long;

SQL>

SQL> TRUNCATE TABLE table_with_long;

Table truncated.

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

SQL> SELECT * FROM table_with_long;

no rows selected

SQL> -- Now, write a PL/SQL procedure to copy all data from

SQL> -- table_with_long$$recovery to the truncated table.

SQL>

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.

SQL> -- 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.

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