CONNECT / AS SYSDBA;
SET ECHO ON; 

CREATE USER TEST IDENTIFIED BY TEST1234
QUOTA UNLIMITED ON USERS;

GRANT CREATE TABLE TO TEST;
GRANT CREATE SESSION TO TEST;
GRANT CREATE VIEW TO TEST;
GRANT SELECT_CATALOG_ROLE TO TEST;
GRANT SELECT ON sys.v_$mystat TO TEST;
GRANT SELECT ON sys.v_$statname TO TEST;

CONNECT TEST/TEST1234;
SET ECHO ON;

CREATE VIEW v_lobstats
AS
SELECT  SUBSTR(n.name,1,20) name,
        m.value             
FROM    v$mystat    m,
        v$statname  n
WHERE   m.statistic# = n.statistic#
    AND n.name LIKE 'lob%';

CREATE TABLE t_test (i NUMBER, c CLOB)
    lob(c) STORE AS (DISABLE STORAGE IN ROW);


SELECT * FROM v_lobstats;
INSERT INTO t_test VALUES (1, 'a');
INSERT INTO t_test VALUES (2, rpad('a',4000,'a'));
COMMIT;
SELECT * FROM v_lobstats;


SELECT * FROM v_lobstats;
SELECT LENGTH(c) FROM t_test;
SELECT * FROM v_lobstats;


SELECT * FROM v_lobstats;
SELECT * FROM t_test;
SELECT * FROM v_lobstats;

SELECT * FROM v_lobstats;
SELECT substr(c, length(c), 1) FROM t_test;
SELECT substr(c, 1, 1) FROM t_test;
SELECT * FROM v_lobstats;

SELECT * FROM v_lobstats;
DECLARE
    loc     CLOB;
    buf     LONG;
    chunk   NUMBER;
BEGIN
    SELECT c INTO loc FROM t_test WHERE i = 1
        FOR UPDATE;

    chunk := DBMS_LOB.GETCHUNKSIZE(loc);
    buf   := rpad('b', chunk, 'b');

    -- aligned buffer length and offset
    DBMS_LOB.WRITE(loc, chunk, 1, buf);
    DBMS_LOB.WRITE(loc, chunk, 1+chunk, buf);
    COMMIT;
END;
/
SELECT * FROM v_lobstats;

SELECT * FROM v_lobstats;
DECLARE
    loc CLOB;
    buf LONG;
BEGIN
    SELECT c INTO loc FROM t_test WHERE i = 1
        FOR UPDATE;

    buf := rpad('b', DBMS_LOB.GETCHUNKSIZE(loc), 'b');

    -- unaligned buffer length
    DBMS_LOB.WRITE(loc, DBMS_LOB.GETCHUNKSIZE(loc)-1, 1, buf);

    -- unaligned start offset
    DBMS_LOB.WRITE(loc, DBMS_LOB.GETCHUNKSIZE(loc), 2, buf);

    -- unaligned buffer length and start offset
    DBMS_LOB.WRITE(loc, DBMS_LOB.GETCHUNKSIZE(loc)-1, 2, buf);

    COMMIT;
END;
/
SELECT * FROM v_lobstats;


-- Cleanup
CONN / AS SYSDBA

DROP USER test CASCADE;