Archive for the ‘ORACLE Database’ Category

Oracle Database – how to create a ‚Public Database Link‘ or a „Private Database Link‘

Samstag, Januar 20th, 2024

Oracle 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, 2024
SELECT 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, 2024

Oracle Database – Advanced Compression

Donnerstag, Januar 4th, 2024

Oracle Database – Security

Donnerstag, Januar 4th, 2024

Oracle Database – Active Data Guard

Donnerstag, Januar 4th, 2024

HPE GreenLake – Block Storage Provisioning for Oracle RAC

Dienstag, November 28th, 2023

Oracle Database – find the total size of all of your CLOB & BLOB columns in all tables

Dienstag, Oktober 31st, 2023

The 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, 2023

Oracle Licensing – on VMware Hybrid Multi-Clouds (On-premises and VMware Clouds)

Freitag, Oktober 6th, 2023

Oracle 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

On Demand hot-extend online Oracle RAC clustered disks using VMware Virtual Volumes (vVols) on Pure Storage X50 AFF – no downtime

Freitag, Oktober 6th, 2023

Oracle Database 23c – New Security Features

Dienstag, September 26th, 2023

Oracle Database 23c – the new version offers a range of features to help developers analysts and database administrators build and deploy their business-critical high-performance next-generation operational and analytical applications

Freitag, September 22nd, 2023

Oracle Database – Ransomware is an existential threat to organizations across all industries

Montag, Juli 31st, 2023

Oracle Database 19c Enterprise Edition – Recovery Manager (RMAN) Compression

Freitag, Juli 28th, 2023

Oracle Database 19c Enterprise Edition Recovery Manager (RMAN) Compression – the continuous growth in enterprise databases creates an enormous challenge to database administrators


Example: