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 […]

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 […]

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; […]

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 […]

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<=3000)   select rownum empno,ename,job,mgr,hiredate,sal,comm,deptno from emp,l,l table big_emp created. […]