Tablespace Space report in a single SQL query

Use this query to get the tablespace report. The total space may not be exactly equal to free space plus occupied space as the total space taking header info space too.

select a.tablespace_name,
a.bytes/1024/1024 as total_space_MB,
b.bytes/1024/1024 as free_space_MB,
c.bytes/1024/1024 as occpied_space_MB
from sm$ts_avail a ,
sm$ts_free b,
sm$ts_used c
where a.tablespace_name=b.tablespace_name
and a.tablespace_name=c.tablespace_name;

Tracking the removed datafiles

Hi,
In general we DBAs Create datafiles ,drop datafiles too. But we may want to know when I created the datafile and when I dropped it and what was its size . But once you drop you will not have those information in any of the system tables such as DBA_DATA_FILES (or) V$datafile etc.
The alert.log will have this but it is always good to know the following

Will Oracle Keep track of the history of datafiles created and dropped?
If Yes , then where can I find?

To answer this , I will explain how oracle works on this internally.

Let us assume we create a new database with 100 datafiles comprising of various tablespaces.

Oracle allocates a file# to each of the files using its internal sequence created.

So for 100 datafiles we will be having file# starting from 1 to 100. Now let us assume I create a new tablespace with 1 datafile then my counter becomes 101.

Now if I drop a tablespace having 5 datafiles then my counter still is 101 but I have 5 slots empty for file#. Assume 91,92,93,94 and 95. Now if I again create 2 tablespaces one with 2 datafiles and other with 3 datafiles then , Oracle will reuse this slot of 91 to 95.

All these changes are dyanmically captured in a X$ table called X$KCCFE.

Check the below query

select a.fenum,
a.fecrc_tim as created_time,
a.festt as dropped_time,
(a.fecsz*b.value)/1024/1024 as file_size
from sys.X$kccfe a,V$parameter b
where name='db_block_size'
and a.feflg!=0
and a.fenum not in (select file# from V$datafile);

This query will only have dynamic data.

regards,
Vijay Kaushik

Using PL/SQL to generate Create controlfile command

Create control file script for the database is generated by the following command

SQL>alter database backup controlfile to trace;

But let us use a small PL/SQL code to generate the output of the above command . By doing this we can run the sql script from client machine and get the output without logging into the unix server.

<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
declare
cursor c1 is
select file_name from dba_data_files;
cursor c2 is
select a.group# grp,a.member mem,b.bytes/1024/1024 byt
from V$logfile a ,V$log b
where a.group#=b.group#
order by a.group#;
cursor c3 is
select tablespace_name,file_name,bytes/1024/1024 as tbyt
from dba_temp_files;
max_grp pls_integer;
cnt PLS_INTEGER;
val1 PLS_INTEGER;
val2 PLS_INTEGER;
val3 PLS_INTEGER;
pval varchar2(100);
name varchar2(50);
log_mode varchar2(12);
begin
select name,log_mode into name,log_mode from V$database;
select PROPERTY_VALUE into pval from database_properties
where PROPERTY_NAME='NLS_CHARACTERSET';
DBMS_OUTPUT.PUT_LINE('CREATE CONTROLFILE SET DATABASE 'name' RESETLOGS 'log_mode);
select records_total into val1 from V$controlfile_record_section where type='REDO LOG';
select records_total into val2 from V$controlfile_record_section where type='DATAFILE';
select records_total into val3 from V$controlfile_record_section where type='LOG HISTORY';
DBMS_OUTPUT.PUT_LINE('MAXLOGFILES 'val1);
DBMS_OUTPUT.PUT_LINE('MAXDATAFILES 'val2);
DBMS_OUTPUT.PUT_LINE('MAXLOGHISTORY 'val3);
DBMS_OUTPUT.PUT_LINE('LOGFILE');
select max(group#) into max_grp from V$logfile;
select count(1) into cnt from DBA_DATA_FILES;
for rec1 in c2
loop
if rec1.grp!=max_grp then
DBMS_OUTPUT.PUT_LINE('GROUP 'rec1.grp' '''rec1.mem''''' size 'rec1.byt'M,');
else
DBMS_OUTPUT.PUT_LINE('GROUP 'rec1.grp' '''rec1.mem''''' size 'rec1.byt'M');
DBMS_OUTPUT.PUT_LINE('DATAFILE');end if;end loop;
for rec in c1
loop
if c1%rowcount!=cnt then
DBMS_OUTPUT.PUT_LINE(''''rec.file_name''',');
else
DBMS_OUTPUT.PUT_LINE(''''rec.file_name'''');
end if;
end loop;
DBMS_OUTPUT.PUT_LINE('CHARACTER SET 'pval';');
for rec2 in c3
loop
dbms_output.put_line('alter tablespace ' rec2.tablespace_name' add tempfile ');
DBMS_OUTPUT.PUT_LINE(''''rec2.file_name''''' SIZE 'rec2.tbyt'M REUSE AUTOEXTEND OFF;');
end loop;
end;
/
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

Playing with Numeric datatypes

Check this fun with numeric datatypes

Create table num_test
(
c1 number,
c2 numeric,
c3 decimal,
c4 integer,
c5 smallint,
c6 number(9),
c7 numeric(9),
c8 decimal(9),
c9 number(9,2),
c10 numeric(9,2),
c11 decimal(9,2),
c12 number(*,2),
c13 numeric(*,2),
c14 decimal(*,2),
c15 float,
c16 float(30),
c17 real,
c18 double precision);


Now lets try this

SQL> alter table num_test add c19 number(*);
Table altered.
Elapsed: 00:00:00.16

SQL> alter table num_test add c20 number(*,10);
Table altered.
Elapsed: 00:00:00.02
SQL> alter table num_test add c21 number(10,*);
alter table num_test add c21 number(10,*) *

ERROR at line 1:ORA-01728: numeric scale specifier is out of range (-84 to 127)


SQL> desc num_test
Name Null? Type
--------------------------------------------------- -------- -------------------------------
C1 NUMBER
C2 NUMBER(38)
C3 NUMBER(38)
C4 NUMBER(38)
C5 NUMBER(38)
C6 NUMBER(9)
C7 NUMBER(9)
C8 NUMBER(9)
C9 NUMBER(9,2)
C10 NUMBER(9,2)
C11 NUMBER(9,2)
C12 NUMBER(38,2)
C13 NUMBER(38,2)
C14 NUMBER(38,2)
C15 FLOAT(126)
C16 FLOAT(30)
C17 FLOAT(63)
C18 FLOAT(126)
C19 NUMBER
C20 NUMBER(38,10)

Note that the length of NUMBER datatype is 126 max. (ie) It can store upto 126 digits as its maximum.

Do Auditing without using Audit command

This below is an easy way of implementing audit for DDL's without using Audit command

This compromises of 2 steps
1)Create a tablespace called audit_tbs
2)Create a table named MONITOR inside AUDIT_TBS
3)Create a trigger.

CREATE TABLE SYS.MONITOR
(OBJ_OWNER VARCHAR2(20),
OBJ_NAME VARCHAR2(30),
OBJ_TYPE VARCHAR2(30),
OBJ_EVENT VARCHAR2(30),
OBJ_DATE TIMESTAMP (6))
TABLESPACE AUDIT_TBS ;

create or replace trigger monitor_trig
before create or drop or alter or truncate on database
declare
cursor c1 is
select dbms_standard.DICTIONARY_OBJ_OWNER owner,
dbms_standard.SYSEVENT event ,
dbms_standard.DICTIONARY_OBJ_NAME name,
dbms_standard.DICTIONARY_OBJ_TYPE type
from dual;
BEGIN
for rec in c1
loop
Insert into monitor values (rec.owner,rec.name,rec.type,rec.event,sysdate);
end loop;
end;
/

You can cutomize the trigger code as per your requirement.