Home » RDBMS Server » Performance Tuning » No more data to read from socket (No more data to read from socket (Oracle error 17410 ))
No more data to read from socket [message #671900] |
Sat, 22 September 2018 13:20 |
|
saipradyumn
Messages: 419 Registered: October 2011 Location: Hyderabad
|
Senior Member |
|
|
Hi All,
Some times I am getting the No more data to read from socket error,
when we are doing the index enabling or disabling on LIST partitioned table.
To enable or disable the index we have the PROC_INDEX_ENABLE_DISABLE procedure.
Following are the situation we need to handle .
1: All the table are LIST Partitioned on the BRANCH columns like ABC,XYZ,1BB,
(Branch value may start with Number also).
Ideally we will use same name for Partition name also.
We Oracle doesn't allow us to create the Objects start with number,
we had created different partition name when it is holding the value start with Number.
2: Multiple branches may belongs to one group id. If we pass the Group id all the branches under the
same group id corresponding branch related partitions have to be enabled/disabled
If i am running individually it is working fine.But this enabling /enabling is called in multiples procedure
with different tables,different group ids.
some times I am getting the Oracle Internal error
CallableStatementCallback; uncategorized SQLException for SQL [{call PROC_INDEX_ENABLE_DISABLE(?, ?, ?, ?)}];
SQL state [null]; error code [17410];
No more data to read from socket; nested exception is java.sql.SQLException: No more data to read from socket
CREATE TABLE GROUP_BRANCHES(BRN_GROUP_ID VARCHAR2(20), BRANCH_NAME VARCHAR2(20));
INSERT INTO "GROUP_BRANCHES" (BRN_GROUP_ID, BRANCH_NAME) VALUES ('B1', 'ABC');
INSERT INTO "GROUP_BRANCHES" (BRN_GROUP_ID, BRANCH_NAME) VALUES ('B1', 'XYZ');
INSERT INTO "GROUP_BRANCHES" (BRN_GROUP_ID, BRANCH_NAME) VALUES ('ABC', 'ABC');
INSERT INTO "GROUP_BRANCHES" (BRN_GROUP_ID, BRANCH_NAME) VALUES ('XYZ', 'XYZ');
DROP TABLE LIST_PARTITIONED_TABLE;
CREATE TABLE LIST_PARTITIONED_TABLE(BRANCH VARCHAR2(4 CHAR) NOT NULL ENABLE,REFERENCE_NUMBER VARCHAR2(20 CHAR) NOT NULL ENABLE )
PARTITION BY LIST( BRANCH)( PARTITION ABC VALUES('0ABC'));
create or replace TYPE SCHARTABLE IS TABLE OF VARCHAR2(20);
create or replace PROCEDURE PROC_INDEX_ENABLE_DISABLE(
v_table_name IN VARCHAR2,
vMode IN VARCHAR2,
vStatus IN OUT VARCHAR2,
v_brn_group_id IN VARCHAR2)
IS
v_Index_Name VARCHAR2(30);
v_Constraint_Name VARCHAR2(30);
v_Process_Time NUMBER;
v_Owner_Name VARCHAR2(10) := 'schemaname';
vErrStr VARCHAR2(4000);
v_MV_Wait_Time NUMBER;
v_isJobNameExists USER_JOBS.JOB%TYPE;
C_START_STATUS VARCHAR(2) := '-1';
C_FINAL_STATUS VARCHAR(1) := '0';
C_PRGNAME VARCHAR(200):= 'PROC_INDEX_ENABLE_DISABLE';
C_STARTED VARCHAR2(7) := 'STARTED';
C_COMPLETED VARCHAR2(10) := 'COMPLETED';
C_ERROR_STACK VARCHAR2(20) := 'Error_Stack...';
C_FAILURE VARCHAR2(10) := 'F';
C_SUCCESS VARCHAR2(10) := 'S';
list_partition_name schartable := schartable();
TYPE tbPartitioned
IS
TABLE OF USER_IND_PARTITIONS.PARTITION_NAME%TYPE;
tPartitioned tbPartitioned;
TYPE tbIndex_Name
IS
TABLE OF USER_INDEXES.INDEX_NAME%TYPE;
tIndex_Name tbIndex_Name;
PROCEDURE SP_LOAD_PARTITIONS
IS
BEGIN
IF vMode IN ('D','E') THEN
SELECT PARTITION_NAME bulk collect
INTO list_partition_name
FROM XMLTABLE('/ROWSET/ROW' PASSING DBMS_XMLGEN.GETXMLTYPE ('SELECT TABLE_NAME, HIGH_VALUE ,PARTITION_NAME FROM USER_TAB_PARTITIONS U') COLUMNS TABLE_NAME VARCHAR2(100), HIGH_VALUE VARCHAR2(200), PARTITION_NAME VARCHAR2(200)) XT ,
GROUP_BRANCHES MBI
WHERE TRIM(MBI.BRANCH_NAME) = REPLACE( TRIM(XT.HIGH_VALUE),'''',NULL)
AND trim(MBI.BRN_GROUP_ID) = trim(v_brn_group_id)
AND XT.TABLE_NAME = v_table_name;
END IF ;
END SP_LOAD_PARTITIONS;
PROCEDURE SP_DELETE_PARTITIONS
IS
BEGIN
IF vMode IN ('D','E') THEN
list_partition_name.delete;
END IF ;
END SP_DELETE_PARTITIONS;
PROCEDURE SP_NON_PARTITION_IDXS_ENABLE
IS
BEGIN
FOR echIndxNM IN
(SELECT INDEX_NAME
FROM USER_INDEXES
WHERE STATUS = 'UNUSABLE'
AND TABLE_NAME = v_table_name
AND TABLE_OWNER = v_Owner_Name
AND PARTITIONED = 'NO'
)
LOOP
--Rebuilding the Indexes
EXECUTE IMMEDIATE ' ALTER INDEX '||echIndxNM.INDEX_NAME ||' REBUILD ONLINE PARALLEL 16';
--Resetting the Parallel Degree back to 1
EXECUTE IMMEDIATE ' ALTER INDEX '||echIndxNM.INDEX_NAME ||' PARALLEL 1';
END LOOP;
END SP_NON_PARTITION_IDXS_ENABLE;
PROCEDURE SP_PARTITION_IDXS_ENABLE
IS
BEGIN
FOR rec_indx IN
( SELECT DISTINCT UIP.PARTITION_NAME ,
UI.INDEX_NAME
FROM USER_IND_PARTITIONS UIP ,
USER_INDEXES UI,
USER_PART_INDEXES UPI
WHERE UI.INDEX_NAME = UIP.INDEX_NAME
AND UI.INDEX_NAME = UPI.INDEX_NAME
AND UI.TABLE_NAME = UPI.TABLE_NAME
AND UI.TABLE_NAME = v_table_name
AND UI.TABLE_OWNER = v_Owner_Name
AND UIP.STATUS = 'UNUSABLE'
AND PARTITIONED = 'YES'
AND UIP.COMPOSITE ='NO'
AND 1 =
CASE
WHEN UPI.PARTITIONING_TYPE = 'LIST'
AND UIP.PARTITION_NAME MEMBER OF list_partition_name
THEN 1
WHEN UPI.PARTITIONING_TYPE <> 'LIST'
THEN 1
ELSE 0
END
)
LOOP
--Rebuilding the Partitions.
EXECUTE IMMEDIATE ' ALTER INDEX '||rec_indx.INDEX_NAME||' REBUILD PARTITION '||rec_indx.PARTITION_NAME|| ' PARALLEL 16';
--Resetting the Parallel Degree back to 1
EXECUTE IMMEDIATE ' ALTER INDEX '||rec_indx.INDEX_NAME ||' PARALLEL 1';
END LOOP;
END SP_PARTITION_IDXS_ENABLE;
PROCEDURE SP_SUBPARTITION_IDXS_ENABLE
IS
BEGIN
FOR rec_indx IN
( SELECT DISTINCT UIS.SUBPARTITION_NAME ,
UI.INDEX_NAME BULK COLLECT
INTO tPartitioned ,
tIndex_Name
FROM USER_INDEXES UI ,
USER_IND_PARTITIONS UIP ,
USER_IND_SUBPARTITIONS UIS,
USER_PART_INDEXES UPI
WHERE UI.INDEX_NAME = UIP.INDEX_NAME
AND UI.INDEX_NAME = UIS.INDEX_NAME
AND UI.INDEX_NAME = UPI.INDEX_NAME
AND UI.TABLE_NAME = UPI.TABLE_NAME
AND UIP.PARTITION_NAME = UIS.PARTITION_NAME
AND UI.TABLE_NAME = v_table_name
AND UI.TABLE_OWNER = v_Owner_Name
AND PARTITIONED = 'YES'
AND UIP.COMPOSITE ='YES'
AND UIS.STATUS IN ('UNUSABLE','N/A')
AND 1 =
CASE
WHEN UPI.PARTITIONING_TYPE = 'LIST'
AND UIP.PARTITION_NAME MEMBER OF list_partition_name
THEN 1
WHEN UPI.PARTITIONING_TYPE <> 'LIST'
THEN 1
ELSE 0
END
)
LOOP
--Rebuilding the Partitions.
EXECUTE IMMEDIATE ' ALTER INDEX '||rec_indx.index_name||' REBUILD SUBPARTITION '||rec_indx.SUBPARTITION_NAME|| ' PARALLEL 16';
--Resetting the Parallel Degree back to 1
EXECUTE IMMEDIATE ' ALTER INDEX '||rec_indx.index_name ||' PARALLEL 1';
END LOOP;
IF v_Constraint_Name IS NOT NULL THEN
EXECUTE IMMEDIATE 'ALTER TABLE '||v_table_name||' ENABLE PRIMARY KEY';
END IF;
END SP_SUBPARTITION_IDXS_ENABLE;
PROCEDURE SP_NON_PARTITION_IDXS_DISABLE
IS
BEGIN
IF v_Constraint_Name IS NOT NULL THEN
EXECUTE IMMEDIATE 'ALTER TABLE '||v_table_name||' DISABLE PRIMARY KEY';
END IF;
--1: All Non Partitioned index on Partitioned/Non Partitioned table
FOR n_Idx IN
(SELECT INDEX_NAME
FROM USER_INDEXES
WHERE STATUS = 'VALID'
AND TABLE_NAME = v_table_name
AND TABLE_OWNER = v_Owner_Name
AND PARTITIONED = 'NO'
)
LOOP
EXECUTE IMMEDIATE 'ALTER INDEX '||n_Idx.INDEX_NAME||' UNUSABLE';
END LOOP;
END SP_NON_PARTITION_IDXS_DISABLE;
PROCEDURE SP_PARTITION_IDXS_DISABLE
IS
BEGIN
FOR rec_idx IN
( SELECT DISTINCT UIP.PARTITION_NAME ,
UI.INDEX_NAME
FROM USER_IND_PARTITIONS UIP ,
USER_INDEXES UI,
USER_PART_INDEXES UPI
WHERE UI.INDEX_NAME = UIP.INDEX_NAME
AND UI.INDEX_NAME = UPI.INDEX_NAME
AND UI.TABLE_NAME = UPI.TABLE_NAME
AND UI.TABLE_NAME = v_table_name
AND UI.TABLE_OWNER = v_Owner_Name
AND UIP.STATUS = 'USABLE'
AND PARTITIONED = 'YES'
AND UIP.COMPOSITE ='NO'
AND 1 =
CASE
WHEN UPI.PARTITIONING_TYPE = 'LIST'
AND UIP.PARTITION_NAME MEMBER OF list_partition_name
THEN 1
WHEN UPI.PARTITIONING_TYPE <> 'LIST'
THEN 1
ELSE 0
END
)
LOOP
--Disabling the Indexes at Partitions level.
EXECUTE IMMEDIATE ' ALTER INDEX '||rec_idx.INDEX_NAME||' MODIFY PARTITION '||rec_idx.PARTITION_NAME|| ' UNUSABLE ';
END LOOP;
END SP_PARTITION_IDXS_DISABLE;
PROCEDURE SP_SUBPARTITION_IDXS_DISABLE
IS
BEGIN
FOR REC_INDX IN
( SELECT DISTINCT UIS.SUBPARTITION_NAME ,
UI.INDEX_NAME
FROM USER_INDEXES UI ,
USER_IND_PARTITIONS UIP ,
USER_IND_SUBPARTITIONS UIS,
USER_PART_INDEXES UPI
WHERE UI.INDEX_NAME = UIP.INDEX_NAME
AND UI.INDEX_NAME = UIS.INDEX_NAME
AND UI.INDEX_NAME = UPI.INDEX_NAME
AND UI.TABLE_NAME = UPI.TABLE_NAME
AND UIP.PARTITION_NAME = UIS.PARTITION_NAME
AND UI.TABLE_NAME = v_table_name
AND UI.TABLE_OWNER = v_Owner_Name
AND PARTITIONED = 'YES'
AND UIP.COMPOSITE ='YES'
AND UIS.STATUS ='USABLE'
AND 1 =
CASE
WHEN UPI.PARTITIONING_TYPE = 'LIST'
AND UIP.PARTITION_NAME MEMBER OF list_partition_name
THEN 1
WHEN UPI.PARTITIONING_TYPE <> 'LIST'
THEN 1
ELSE 0
END
)
LOOP
--Rebuilding theIndexes at SUBPartitions.
EXECUTE IMMEDIATE ' ALTER INDEX '||REC_INDX.INDEX_NAME||' MODIFY SUBPARTITION '||REC_INDX.SUBPARTITION_NAME || ' UNUSABLE ';
END LOOP;
END SP_SUBPARTITION_IDXS_DISABLE;
BEGIN
vStatus := C_START_STATUS;
SP_LOAD_PARTITIONS;
-- Gets the Constraint_Name for table passed as v_table_name.
BEGIN
SELECT CONSTRAINT_NAME
INTO v_Constraint_Name
FROM USER_CONSTRAINTS
WHERE TABLE_NAME =v_table_name
AND CONSTRAINT_TYPE='P'
AND OWNER = v_Owner_Name;
EXCEPTION
WHEN NO_DATA_FOUND THEN
v_Constraint_Name := NULL;
END;
-----------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------
/* If vMode = 'D' i.e, DISABLE, then
(1) Fetches the all Indexes of the Table and Disables the same
(2) If the Table has Primary Key Constraint then Disables it.
*/
IF vMode = 'D' THEN
SP_NON_PARTITION_IDXS_DISABLE; --To Disable the non partitioned indexes for the given table
SP_PARTITION_IDXS_DISABLE; --To Disable the partitioned indexes for the given table
SP_SUBPARTITION_IDXS_DISABLE; --To Disable the sub partitioned indexes for the given table
Elsif vMode = 'E' THEN
SP_NON_PARTITION_IDXS_ENABLE;--To Enable the non partitioned indexes for the given table
SP_PARTITION_IDXS_ENABLE; --To Enable the partitioned indexes for the given table
SP_SUBPARTITION_IDXS_ENABLE; --To Enable the sub partitioned indexes for the given table
END IF;
SP_DELETE_PARTITIONS;
EXCEPTION
WHEN OTHERS THEN
vErrStr:= SUBSTR('Error_Stack...' || DBMS_UTILITY.FORMAT_ERROR_STACK() || Chr(10) || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE(),1,3500);
vStatus := C_START_STATUS;
END PROC_INDEX_ENABLE_DISABLE;
Thanks
SaiPradyumn
|
|
|
Goto Forum:
Current Time: Sat Apr 20 10:41:47 CDT 2024
|