Estimate Tablespace growth

Some time it is very helpful to plan disk space/ tablespace management if you estimate the growth of your tablespace. Here is a select query which can be helpful :

SELECT TO_CHAR (sp.begin_interval_time,'DD-MM-YYYY') days
, ts.tsname
, max(round((tsu.tablespace_size* dt.block_size )/(1024*1024),2) ) cur_size_MB
, max(round((tsu.tablespace_usedsize* dt.block_size )/(1024*1024),2)) usedsize_MB
FROM DBA_HIST_TBSPC_SPACE_USAGE tsu
, DBA_HIST_TABLESPACE_STAT ts
, DBA_HIST_SNAPSHOT sp
, DBA_TABLESPACES dt
WHERE tsu.tablespace_id= ts.ts#
AND tsu.snap_id = sp.snap_id
AND ts.tsname = dt.tablespace_name
AND ts.tsname NOT IN ('SYSAUX','SYSTEM')
GROUP BY TO_CHAR (sp.begin_interval_time,'DD-MM-YYYY'), ts.tsname
ORDER BY ts.tsname, days;

Add Unique key in a table that contains duplicate row

Requirement : A table contains some duplicate data. Now we want to add a unique constraint that skip existing duplicate values but check newly inserted duplicate values.

SQL> create table t2 (id number(10), t varchar2(20));

Table created.


SQL> insert into t2 values (1,'A');

1 row created.

SQL> insert into t2 values (1,'A');

1 row created.

SQL> insert into t2 values (1,'A');

1 row created.

SQL> commit;

Commit complete.

SQL> alter table t2 add constraint uk_t2 unique(id) ENABLE NOVALIDATE;
alter table t2 add constraint uk_t2 unique(id) ENABLE NOVALIDATE
*
ERROR at line 1:
ORA-02299: cannot validate (HASAN.UK_T2) - duplicate keys found


SQL> select * from t2;

ID T
---------- ------------------------------------------------------------
1 A
1 A
1 A

So normal method does not work !



Case 1: New Table That means initially the table does not have any data

SQL> create table t3 (id number(10), t varchar2(20));

Table created.

SQL> alter table t3 add constraint gpu unique (id) deferrable initially deferred;

Table altered.

SQL> alter table t3 disable constraint gpu;

Table altered.


SQL> insert into t3 values(1,'A');

1 row created.

SQL> insert into t3 values(1,'A');

1 row created.

SQL> insert into t3 values(1,'A');

1 row created.

SQL> commit;

Commit complete.

SQL>
SQL>
SQL>
SQL> select * from t3;

ID T
---------- ------------------------------------------------------------
1 A
1 A
1 A



SQL> alter table t3 enable novalidate constraint gpu;

Table altered.

SQL> insert into t3 values(2,'A');

1 row created.

SQL> commit;

Commit complete.

SQL> insert into t3 values(2,'A');

1 row created.

SQL> commit;
commit
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-00001: unique constraint (HASAN.GPU) violated

SQL> alter table t3 modify constraint gpu INITIALLY IMMEDIATE;

Table altered.

SQL> insert into t3 values(1,'A');
insert into t3 values(1,'A')
*
ERROR at line 1:
ORA-00001: unique constraint (HASAN.GPU) violated



Case 2: Existing Table that contains duplicate data


SQL> create table t2 (id number(1),a varchar2(10));

Table created.


SQL> insert into t2 values(1,'A');

1 row created.

SQL> insert into t2 values(1,'A');

1 row created.

SQL> commit;

Commit complete.

SQL> alter table t2 add constraint uk_t2 unique(id) DEFERRABLE INITIALLY DEFERRED disable;

Table altered.

SQL> alter table t2 enable novalidate constraint uk_t2;

Table altered.

SQL> insert into t2 values(1,'A');

1 row created.

SQL> commit;
commit
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-00001: unique constraint (HASAN.UK_T2) violated


SQL> alter table t2 modify constraint uk_t2 INITIALLY IMMEDIATE;

Table altered.

SQL> insert into t2 values(1,'A');
insert into t2 values(1,'A')
*
ERROR at line 1:
ORA-00001: unique constraint (HASAN.UK_T2) violated


OR


SQL> alter table t2 add constraint uk_t2 unique(id) disable;

Table altered.


SQL> alter table t2 enable novalidate constraint uk_t2;

Table altered.

SQL> insert into t2 values(1,'A');
insert into t2 values(1,'A')
*
ERROR at line 1:
ORA-00001: unique constraint (HASAN.UK_T2) violated