PowerShell: Prevent Accidental Runs of Scripts April 28, 2022.
Segment_size_bytes => l_segment_size_bytes,ĭBMS_OUTPUT.put_line(‘segment_size_blocks:’ || l_segment_size_blocks) ĭBMS_OUTPUT.put_line(‘segment_size_bytes :’ || l_segment_size_bytes) ĭBMS_OUTPUT.put_line(‘used_blocks :’ || l_used_blocks) ĭBMS_OUTPUT.put_line(‘used_bytes :’ || l_used_bytes) ĭBMS_OUTPUT.put_line(‘expired_blocks :’ || l_expired_blocks) ĭBMS_OUTPUT.put_line(‘expired_bytes :’ || l_expired_bytes) ĭBMS_OUTPUT.put_line(‘unexpired_blocks :’ || l_unexpired_blocks) ĭBMS_OUTPUT.put_line(‘unexpired_bytes :’ || l_unexpired_bytes) Įnter your email address to follow this blog and receive notifications of new posts by email.įollow Follow SQL Jana on Tags Active Directory AD Add AD Group Backup Backups Capture Code Column CommandLine COMMIT Compare COPY Database DateTime Days DBA dbatools DDL Delete DML Duration Dynamic SQL Error Errors Example Examples Fast File Files Filter FULL TABLE SCAN hangs Indexes INSERT Install ISE Linked Server Login LOOP Match Oracle Oracle Tip Output Parallel Performance Permissions PIVOT PLSQL PowerShell Query Replace ROLLBACK Run Search SELECT Size SQL SQL*Plus SQLPS SQLServer SQL Server SQL Server Tip SSMS Stored Procedure Table Tables Tip Transaction TSQL UNDO Update Usage USER Windows Recent Posts
Segment_size_blocks => l_segment_size_blocks, There is nothing fancy about the simple code above which has no comments! Hope someone will find this useful!Īs a faster alternative to DBMS_LOB when using SecureFile, you can use DBMS_SPACE – MY_SCHEMA.DOC_VIEW_DOC_DOCS.DOC_DATA size in MB is 1.74 MY_SCHEMA_02.DOC_VIEW_DOC_DOCS.DOC_DATA size in MB is 22.65 MY_SCHEMA.DOC_WF_STEP_DOCS.DOC_DATA size in MB is 0 MY_SCHEMA.DOC_PL_DOC_DOCS.DOC_DATA size in MB is 0 MY_SCHEMA.DOC_IMD_PROCESS_LOG.LOG size in MB is 0 MY_SCHEMA.DOC_IMD_JBYTE_ARRAY.VALUE size in MB is 0 MY_SCHEMA.DOC_IMD_ERROR_RESULT.EXCEPTION_OBJECT size in MB is 0 MY_SCHEMA.DOC_IMD_ERROR_RESULT.STACK_TRACE size in MB is 0 MY_SCHEMA.DOC_DOC_DOCS.DOC_DATA size in MB is. MY_SCHEMA.DOC_ARS_SIMS.ARS_REMINDER_BODY size in MB is. MY_SCHEMA.DOC_ARS_SIMS.ARS_SIM_BODY size in MB is. MY_SCHEMA.DOC_DOCS_HISTORY.VALUE size in MB is. MY_SCHEMA.DOC_AR_DOC_DOCS.DOC_DATA size in MB is 1.52 MY_SCHEMA_02.DOC_WF_STEP_DOCS.DOC_DATA size in MB is 0 MY_SCHEMA_02.DOC_PL_DOC_DOCS.DOC_DATA size in MB is. MY_SCHEMA_02.DOC_IMD_PROCESS_LOG.LOG size in MB is 0 MY_SCHEMA_02.DOC_IMD_JBYTE_ARRAY.VALUE size in MB is 0 MY_SCHEMA_02.DOC_IMD_ERROR_RESULT.EXCEPTION_OBJECT size in MB is 0 MY_SCHEMA_02.DOC_IMD_ERROR_RESULT.STACK_TRACE size in MB is 0 MY_SCHEMA_02.DOC_DOC_DOCS.DOC_DATA size in MB is 3.06 MY_SCHEMA_02.DOC_ARS_SIMS.ARS_REMINDER_BODY size in MB is 3.9 MY_SCHEMA_02.DOC_ARS_SIMS.ARS_SIM_BODY size in MB is 3.62 MY_SCHEMA_02.DOC_DOCS_HISTORY.VALUE size in MB is 6.52 MY_SCHEMA_02.DOC_AR_DOC_DOCS.DOC_DATA size in MB is 19.6
SELECT SUM(DBMS_LOB.GetLength("CLOB_DATA"))/1024/1024 AS SizeMBĬalculating the size for all tables with BLOB/CLOB columns While the above SQL will give you the detail level size for each row, the below SQL will give you the overall size SELECT DBMS_LOB.GetLength("CLOB_DATA")/1024/1024 AS SizeMB We convert that value to MB by dividing it by 1024 twice. DBMS_LOB.GetLength returns the number of characters (bytes) in the CLOB/BLOB column.
This is simply how to find the size of individual rows with the CLOB/BLOB column. This matches all columns that have CLOB or BLOB data typeĪs I looked at the number of tables that had CLOB and BLOB columns, it was more than a handful and I was not going to add them up by hand! DBMS_LOB.GetLength – Find the size of BLOB/CLOB column You can alter the condition to suit your case In my case, I had the schemas in which I needed to look. The SQL to find the tables with the BLOB/CLOB columns is quite simple. Today I received a request from an application owner who wanted to know 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.