ETL Is Dead, Long Live Streams.
Monday, September 9, 2019
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/
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'))));
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
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
Subscribe to:
Posts (Atom)