Friday, April 26, 2019

ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION

Partitioned table

SQL> desc honnikery
Name                          Null?    Type
----------------------------- -------- ------------
X                                      NUMBER(38)
Y                                      NUMBER(38)
and it has a couple of honnikerytitions

SQL> select honnikerytition_name
2  from   dba_tab_honnikerytitions
3  where  table_name = 'honnikery';
honnikeryTITION_NAME
------------------------------
P1
P2
So now I want to do the standard operation of creating a ‘template’ table which I can then use to perform an Prabhakarange honnikerytition operation.

SQL> create table Prabhakar as
2  select * from honnikery
3  where 1=0;
Table created.
SQL> desc Prabhakar
Name                          Null?    Type
----------------------------- -------- --------------
X                                      NUMBER(38)
Y                                      NUMBER(38)
So now I’m ready to go…But then this happens…

SQL> alter table honnikery Prabhakarange honnikerytition P1 with table Prabhakar;
alter table honnikery Prabhakarange honnikerytition P1 with table Prabhakar
*
ERROR at line 1:
ORA-14097: column type or size mismatch in ALTER TABLE PrabhakarANGE honnikeryTITION
Well…that’s odd. I created the Prabhakar table as simple create-table-as-select. Let’s try it again using the “WITHOUT VALIDATION” clause.

SQL> alter table honnikery Prabhakarange honnikerytition P1 with table Prabhakar without validation;
alter table honnikery Prabhakarange honnikerytition P1 with table Prabhakar without validation
*
ERROR at line 1:
ORA-14097: column type or size mismatch in ALTER TABLE PrabhakarANGE honnikeryTITION
Nope…still problems. So I go back and double check the columns

SQL> select column_name
2  from   dba_tab_columns
3  where  table_name = 'honnikery';
COLUMN_NAME
------------------------------
X
Y
SQL> select column_name
2  from   dba_tab_columns
3  where  table_name = 'Prabhakar';
COLUMN_NAME
------------------------------
X
Y
So what could be the problem ? Its a “sleeper problem”. Some time ago, I did something to the columns in my honnikerytitioned table that is no longer readily aphonnikeryent.

I dropped a column. Or more accurately, because it was a honnikerytitioned table (and presumably a large table), I set a column to unused. What Oracle has done behind the scenes is retain that column but make it invisible for “day to day” usage. We can see that by querying DBA_TAB_COLS

SQL> select column_name
2  from   dba_tab_cols
3  where  table_name = 'honnikery';
COLUMN_NAME
------------------------------
SYS_C00003_12121820:22:09$
Y
X
And there’s the culprit.

So am I stuck forever ? Do I have to drop the column ? Or reload the honnikery table without the unused column ? All of those things don’t sound too palatable.

No. All I need do is get the columns in my template table into a similar state.

SQL> alter table Prabhakar add Z int;
Table altered.
SQL> alter table Prabhakar set unused column Z;
Table altered.
And we can try again…

SQL> alter table honnikery Prabhakarange honnikerytition P1 with table Prabhakar;
Table altered.



More :https://connor-mcdonald.com/2013/01/20/exchange-partition-those-pesky-columns/

Friday, January 11, 2019

ORA-01502: index ‘string.string’ or partition of such index is in unusable state

ORA-01502: index ‘string.string’ or partition of such index is in unusable state


The error indicates an attempt has been made to access an index or index partition
that has been marked unusable by a direct load or by a DDL operation.

The problem usually happens when using the Direct Path for the SQL*Loader, Direct Load or DDL operations.
This requires enough temporary space to build all indexes of the table. If there is no enough space in TEMP tablespace,
all rows will still be loaded and imported, but the indices are left with STATUS = ‘INVALID’.



SELECT 'alter index '||owner||'.'||index_name ||' rebuild online nologging;'
FROM all_indexes
WHERE owner = 'HONNIKERY' AND status = 'VALID'
AND (status != 'N/A'
OR index_name IN
(SELECT index_name
FROM all_ind_partitions
WHERE status != 'USABLE'
AND (status != 'N/A'
OR index_name IN
(SELECT index_name
FROM all_ind_subpartitions
WHERE status != 'USABLE'))));

Thursday, January 10, 2019

Index Status Types in DBA_INDEXES





SELECT DISTINCT STATUS FROM DBA_INDEXES;
STATUS
-----------
N/A
UNUSABLE
VALID



ORA-01502: index ‘string.string’ or partition of such index is in unusable state


SELECT owner, index_name, tablespace_name
FROM   dba_indexes
WHERE  status = 'UNUSABLE';

Index partitions:
SELECT index_owner, index_name, partition_name, tablespace_name
FROM   dba_ind_PARTITIONS
WHERE  status = 'UNUSABLE';

The following SQL will print out a list of alter commands that can be executed to fix unusable indexes:

Indexes:
SELECT 'alter index '||index_name||' rebuild tablespace |tablespace_name ||';'
FROM   dba_indexes
WHERE  status = 'UNUSABLE';

Index partitions:
SELECT 'alter index '||index_name ||' rebuild partition '||PARTITION_NAME||' TABLESPACE '||tablespace_name ||';'
FROM   dba_ind_partitions
WHERE  status = 'UNUSABLE';

Monday, December 24, 2018

Toad Shortcuts Keys

Shortcut Description
F1 Windows Help File
F2 Toggle Output Window
Shift+F2 Toggle Data Grid Window
F3 Find Next Occurrence
Shift+F3 Find Previous Occurrence
F4 Describe Table, View, Procedure, Function
F5 Execute SQL as a script
F6 Toggle between SQL Editor and Results Panel
F7 Clear All Text
F8 Recall Previous SQL Statement
F9 Execute Statement
Ctrl+F9 Set Code Execution Parameters
Shift+F9 Execute Current SQL statement at Cursor
F10 or right-click Pop-up Menu
Ctrl+F12 External Editor, Pass Contents
Ctrl+A Select All Text
Ctrl+C Copy
Ctrl+E Execute Explain Plan on the Current Statement
Ctrl+F Find Text
Ctrl+G Goto Line
Ctrl+L Convert Text to Lowercase
Ctrl+M Make Code Statement
Ctrl+N Recall Named SQL Statement
Ctrl+O Open a Text File
Ctrl+P Strip Code Statement
Ctrl+R Find and Replace
Ctrl+S Save File
Shift+Ctrl+S Save File As
Ctrl+T Columns Drop-down
Shift+Ctrl+R Alias Replacement
Shift+Ctrl+T Columns Drop-Down no alias
Ctrl+Spacebar Code Templates
Ctrl+U Converts Text to Uppercase
Ctrl+V Paste
Ctrl+X Cut
Ctrl+Z Undo Last Change
Ctrl+. Display Pop-up List of Matching Table Names
Shift+Ctrl+Z Redo Last Undo
Alt+Up Arrow Display Previous Statement
Alt+Down Arrow Display Next Statement (After Alt+Up Arrow)
Ctrl+Home In the data grid: goes to the top of the record set
Ctrl+End In the data grid: goes to the end of the record set
Ctrl+Tab Cycles through the Collection of MDI Child Windows

Tuesday, November 6, 2018

BULK COLLECT & FORALL vs. CURSOR & FOR LOOP in oracle

SQL> create table sarlakg (owner varchar2(30), name varchar2(30), type varchar2(19));

Table created.

 Example CURSOR_FOR_OPEN_QUERY

SQL> set timing on;
SQL> CREATE OR REPLACE PROCEDURE CURSOR_FOR_OPEN_QUERY
  2   IS
  3   l_sOwner VARCHAR2(30);
  4   l_sName VARCHAR2(30);
  5   l_sType VARCHAR2(19);
  6   CURSOR cur IS SELECT owner, object_name name, object_type type FROM all_objects;
  7   BEGIN
  8   dbms_output.put_line('Before CURSOR OPEN: ' || systimestamp);
  9   OPEN cur;
 10   dbms_output.put_line('Before LOOP: ' || systimestamp);
 11   LOOP
 12   FETCH cur INTO l_sOwner, l_sName, l_sType;
 13   IF cur%NOTFOUND THEN
 14   EXIT;
 15   END IF;
 16   INSERT INTO sarlakg values (l_sOwner, l_sName, l_sType);
 17   END LOOP;
 18   CLOSE cur;
 19   dbms_output.put_line('After CURSOR CLOSE: ' || systimestamp);
 20   COMMIT;
 21   END;
 22  /

Procedure created.

Elapsed: 00:00:00.47
SQL> exec CURSOR_FOR_OPEN_QUERY();

PL/SQL procedure successfully completed.

Elapsed: 00:00:05.80
SQL> select count(*) from sarlakg;

  COUNT(*)
----------
     55731

Elapsed: 00:00:00.00
SQL>


SQL> truncate table sarlakg;

Table truncated.

Elapsed: 00:00:00.15

 Example FOR_QUERY

SQL> CREATE OR REPLACE PROCEDURE FOR_QUERY
  2   IS
  3   BEGIN
  4   dbms_output.put_line('Before CURSOR: ' || systimestamp);
  5   FOR cur IN (SELECT owner, object_name name, object_type type FROM all_objects) LOOP
  6   INSERT INTO sarlakg values (cur.owner, cur.name, cur.type);
  7   END LOOP;
  8   dbms_output.put_line('After CURSOR: ' || systimestamp);
  9   COMMIT;
 10   END;
 11  /

Procedure created.

Elapsed: 00:00:00.13
SQL> exec FOR_QUERY();

PL/SQL procedure successfully completed.

Elapsed: 00:00:02.58
SQL> select count(*) from sarlakg;

  COUNT(*)
----------
     55732

Elapsed: 00:00:00.01
 /


 SQL> truncate table sarlakg;

Table truncated.

Elapsed: 00:00:00.15

 Example BULK_COLLECT_QUERY


 SQL> CREATE OR REPLACE PROCEDURE BULK_COLLECT_QUERY
  2   IS
  3   TYPE sOwner IS TABLE OF VARCHAR2(30);
  4   TYPE sName IS TABLE OF VARCHAR2(30);
  5   TYPE sType IS TABLE OF VARCHAR2(19);
  6   l_sOwner sOwner;
  7   l_sName sName;
  8   l_sType sType;
  9   BEGIN
 10   dbms_output.put_line('Before Bulk Collect: ' || systimestamp);
 11   SELECT owner, object_name, object_type
 12   BULK COLLECT INTO l_sOwner, l_sName, l_sType
 13   FROM all_objects;
 14   dbms_output.put_line('After Bulk Collect: ' || systimestamp);
 15   --
 16   FORALL indx IN l_sName.FIRST..l_sName.LAST
 17   INSERT INTO sarlakg values (l_sOwner(indx), l_sName(indx), l_sType(indx));
 18   --
 19   dbms_output.put_line('After FORALL: ' || systimestamp);
 20   COMMIT;
 21   END;
 22  /

Procedure created.

Elapsed: 00:00:00.04
SQL> exec BULK_COLLECT_QUERY();

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.68
SQL>


SQL> select count(*) from sarlakg;

  COUNT(*)
----------
     55732

Elapsed: 00:00:00.01

Tuesday, October 16, 2018

ORA-30009: Not enough memory for CONNECT BY operation


ORA-30009: Not enough memory for CONNECT BY operation
Cause: The memory size was not sufficient to process all the levels of the hierarchy specified by the CONNECT BY clause.
Action: In WORKAREA_SIZE_POLICY=AUTO mode, set PGA_AGGREGATE_TARGET to a reasonably larger value. Or, in WORKAREA_SIZE_POLICY=MANUAL mode, set SORT_AREA_SIZE to a reasonably larger value.



SQL> select count(*) from dual connect by level <= 100000000;
select count(*) from dual connect by level <= 100000000
                     *
ERROR at line 1:
ORA-30009: Not enough memory for CONNECT BY operation



SQL>select count(*) from
  2  (select level from dual connect by level <= 10000),
  3  (select level from dual connect by level <= 10000);

  COUNT(*)
----------
 100000000


Order By and Null values