delete all data

How do you delete all data? The simplistic approach would be to truncate all tables SQL> select table_name from user_tables; TABLE_NAME ———- T1 SQL> truncate table t1; Table truncated. You cannot truncate if you have referential integrity constraints. SQL> truncate table t2; ORA-02266: unique/primary keys in table referenced by enabled foreign keys Ok, let’s disable… Continue reading delete all data

Unusable index

After table maintenance, like move or split partition, underlying indexes are marked as unusable. This boils down to segment reorganisation, not dictionary change. For instance : CREATE TABLE t(x NUMBER) PARTITION BY RANGE(x) (PARTITION p1 VALUES LESS THAN (MAXVALUE)); CREATE INDEX i ON t (x); INSERT INTO t VALUES (1); ALTER TABLE T SPLIT PARTITION… Continue reading Unusable index

last partition

if you really need to quickly find the latest partition per table, I have written this little gem WITH FUNCTION d (b BLOB, len number) RETURN DATE IS d DATE; BEGIN IF DBMS_LOB.SUBSTR (b, 1, 1) = hextoraw(’07’) and len=83 THEN DBMS_STATS.convert_raw_value (DBMS_LOB.SUBSTR (b, 12, 2), d); ELSE d := NULL; END IF; RETURN d;… Continue reading last partition

Do you have a partition in 2015

You need to check the high_value from dba_tab_partitions. Or you you could metadata. With metadata, it is not a long, it either clob or clob-xml. SELECT t.table_name, MAX ( TO_DATE ( REGEXP_SUBSTR ( EXTRACT ( (VALUE (x)), ‘HIBOUNDVAL’).getStringVal (), ‘ \d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}’), ‘YYYY-MM-DD HH24:MI;:SS’)) high_value FROM user_tables t, TABLE ( XMLSEQUENCE ( EXTRACT ( xmltype… Continue reading Do you have a partition in 2015

make count(*) faster

I just install Oracle Enterprise Linux on my new notebook. I wanted to check how far could I improve the performance of a count(*) SQL> drop table big_emp; table big_emp dropped. 258ms elapsed SQL> create table big_emp as with l as(select 1 from dual connect by level alter table big_emp add primary key(empno) table big_emp… Continue reading make count(*) faster