Archive for the ‘ORACLE Database’ Category
Oracle Database – how to create a ‚Public Database Link‘ or a „Private Database Link‘
Samstag, Januar 20th, 2024Oracle Database – how to get information with ChatGPT about the size of Oracle CLOB or BLOB columns can be obtained using the DBMS_LOB.GETLENGTH function the result is the length of a LOB (Large Object) value in bytes
Mittwoch, Januar 17th, 2024SELECT DBMS_LOB.GETLENGTH(your_clob_column) AS clob_size
FROM your_table
WHERE your_condition;
SELECT DBMS_LOB.GETLENGTH(your_blob_column) AS blob_size
FROM your_table
WHERE your_condition;
Oracle Database – Partitioning
Donnerstag, Januar 4th, 2024Oracle Database – Advanced Compression
Donnerstag, Januar 4th, 2024Oracle Database – Security
Donnerstag, Januar 4th, 2024Oracle Database – Active Data Guard
Donnerstag, Januar 4th, 2024HPE GreenLake – Block Storage Provisioning for Oracle RAC
Dienstag, November 28th, 2023Oracle Database – find the total size of all of your CLOB & BLOB columns in all tables
Dienstag, Oktober 31st, 2023The size of all the CLOB and BLOB columns in all the non-system tables so that they have a good idea of the size involved for a migration
Tables with BLOB/CLOB columns
SELECT *
FROM DBA_TAB_COLUMNS
WHERE DATA_TYPE LIKE ‚_LOB‘
AND OWNER LIKE ‚%MYSCHEMA%‘;
Find the size of BLOB/CLOB columns
SELECT DBMS_LOB.GetLength(„CLOB_DATA“)/1024/1024 AS SizeMB
FROM MY_SCHEMA.MY_TABLE_WITH_BLOB_CLOB
SELECT SUM(DBMS_LOB.GetLength(„CLOB_DATA“))/1024/1024 AS SizeMB
FROM MY_SCHEMA.MY_TABLE_WITH_BLOB_CLOB
Calculating the size for all tables with BLOB/CLOB columns
DECLARE
v_TableCol VARCHAR2(100) := “;
v_Size NUMBER := 0;
v_TotalSize NUMBER := 0;
BEGIN
FOR v_Rec IN (
SELECT OWNER || ‚.‘ || TABLE_NAME || ‚.‘ || COLUMN_NAME AS TableAndColumn,
‚SELECT SUM(DBMS_LOB.GetLength(„‚ || COLUMN_NAME || ‚“))/1024/1024 AS SizeMB FROM ‚ || OWNER || ‚.‘ || TABLE_NAME AS sqlstmt
FROM DBA_TAB_COLUMNS
WHERE DATA_TYPE LIKE ‚_LOB‘
AND OWNER LIKE ‚%MYSCHEMA%‘)
LOOP
DBMS_OUTPUT.PUT_LINE (v_Rec.sqlstmt);
EXECUTE IMMEDIATE v_Rec.sqlstmt INTO v_Size;
DBMS_OUTPUT.PUT_LINE (v_Rec.TableAndColumn || ‚ size in MB is ‚ || ROUND(NVL(v_Size,0),2));
v_TotalSize := v_TotalSize + NVL(v_Size,0);
END LOOP;
DBMS_OUTPUT.PUT_LINE (‚Total size in MB is ‚ || ROUND(v_TotalSize,2));
END;
MY_SCHEMA_02.DOC_AR_DOC_DOCS.DOC_DATA size in MB is 19.6
MY_SCHEMA_02.DOC_DOCS_HISTORY.VALUE size in MB is 6.52
MY_SCHEMA_02.DOC_ARS_SIMS.ARS_SIM_BODY size in MB is 3.62
MY_SCHEMA_02.DOC_ARS_SIMS.ARS_REMINDER_BODY size in MB is 3.9
MY_SCHEMA_02.DOC_DOC_DOCS.DOC_DATA size in MB is 3.06
MY_SCHEMA_02.DOC_IMD_ERROR_RESULT.STACK_TRACE size in MB is 0
MY_SCHEMA_02.DOC_IMD_ERROR_RESULT.EXCEPTION_OBJECT size in MB is 0
MY_SCHEMA_02.DOC_IMD_JBYTE_ARRAY.VALUE size in MB is 0
MY_SCHEMA_02.DOC_IMD_PROCESS_LOG.LOG size in MB is 0
MY_SCHEMA_02.DOC_PL_DOC_DOCS.DOC_DATA size in MB is .27
MY_SCHEMA_02.DOC_WF_STEP_DOCS.DOC_DATA size in MB is 0
MY_SCHEMA.DOC_AR_DOC_DOCS.DOC_DATA size in MB is 1.52
MY_SCHEMA.DOC_DOCS_HISTORY.VALUE size in MB is .43
MY_SCHEMA.DOC_ARS_SIMS.ARS_SIM_BODY size in MB is .49
MY_SCHEMA.DOC_ARS_SIMS.ARS_REMINDER_BODY size in MB is .33
MY_SCHEMA.DOC_DOC_DOCS.DOC_DATA size in MB is .22
MY_SCHEMA.DOC_IMD_ERROR_RESULT.STACK_TRACE size in MB is 0
MY_SCHEMA.DOC_IMD_ERROR_RESULT.EXCEPTION_OBJECT size in MB is 0
MY_SCHEMA.DOC_IMD_JBYTE_ARRAY.VALUE size in MB is 0
MY_SCHEMA.DOC_IMD_PROCESS_LOG.LOG size in MB is 0
MY_SCHEMA.DOC_PL_DOC_DOCS.DOC_DATA size in MB is 0
MY_SCHEMA.DOC_WF_STEP_DOCS.DOC_DATA size in MB is 0
MY_SCHEMA_02.DOC_VIEW_DOC_DOCS.DOC_DATA size in MB is 22.65
MY_SCHEMA.DOC_VIEW_DOC_DOCS.DOC_DATA size in MB is 1.74
Total size in MB is 64.35
Oracle CloudWorld 2023 – Database directions
Samstag, Oktober 28th, 2023Oracle Licensing – on VMware Hybrid Multi-Clouds (On-premises and VMware Clouds)
Freitag, Oktober 6th, 2023Oracle licensing DOES NOT change from a licensing perspective whether you run Oracle workloads on a Classic vSphere environment connected to a NAS / SAN / iSCSI / NVMe Hyper-Converged Infrastructure solution vSAN Oracle workloads on VMware Cloud e.g. VMware Cloud on AWS