Oracle Space

Oracle Space

1-Show Free Space in each tablespace
1.1 Reside your tablespace (Add Datafile to your tablespace)
1.2 remove File from Tablespace
2-Show Size of your table in GB
3-Remove all object in Recycle Bin
4-Log File
4.1 What is Log File?
4.2 Show Status of logfile
4.3 Switch Log File 
4.4 log file group
5-Resize Datafile Size 
6-Show Free-Used Size in each datafile for a specific tablespace 
7-How your data store in multiple data file? 
8-Save space in SYSTEM Tablespace 
9-How to change object from one schema to other schema
10-drop tablespace and content and data file 
11-Show total size of your database 
12-set default tablespace 

---

1-Show Free Space in each tablespace

This code will display Freespace in each tablespace

SELECT
   a.tablespace_name,
--  a.file_name,
   sum(a.bytes/(1024*1024)) allocated_MB,

   sum(b.free_bytes/(1024*1024)) FreeSpace_MB,sum(b.free_bytes/(1024*1024))/sum(a.bytes/(1024*1024)) Free_space_percentage
FROM
   dba_data_files a,
   (SELECT file_id, SUM(bytes) free_bytes
    FROM dba_free_space b GROUP BY file_id) b
WHERE
   a.file_id=b.file_id
group by  a.tablespace_name
ORDER BY
Free_space_percentage

1.1 Reside your tablespace

Alter tablespace temp add datafile 'u01/.../temp1.dbf' size 20M

ALTER TABLESPACE "X_IDX"
ADD DATAFILE '/X_idx_15.dbf' SIZE 5G

1.2 remove File from Tablespace

Alter tablespace Temp Drop Tempfile 'u01/.../temp01.dbf'



2-Show Size of your table in GB

Select Sum(Bytes/1024/1024/1024) from dba_segments where segment_name like 'X%'

3-Remove all object in Recycle Bin

sql> Purge recyclebin

4.2 Show Status of logfile
There are 3 status of logfile: Active, Current, InActive
Status of redo log group:

current-write data to it,
active need for instnace recovery ,
inactive no need for instance recovery

In order to show status of your logfile, type the below script

select Group#, Archived, Status from V$log;

4.3 Switch Log File
alter system switch logfile; 

4.4 log file group

redo log file parameter
1-maxlogfiles: determines the max number of redo log group
2-maxlogmembers: determines the max number of file in each redo log group

what is the number of redo log group needed?
normally oracle require only two group, but in other situation it also need more group.
why?
to know how many redo log group we need, first check LGWR Trace file or alert log file contents
if you found that :
lgwr has to wait because checkpoint is not complete or a group has not beed archived,
so add groups.

How many file in each redo log group?

normally , there are 2 files in each group and each file is store in difference loaction
each file in the group has the same file size.
each file in the group has the same data.

-when LGWR cannot write to a member of a group the database mark that member as INVALID and
write an error message to LGWR trace file and alert log file.

1-LGWR can successfully write to at least one member in a group: writing proceed as normal, LGWR write to the available member of the group and ignore the unavailable group.

log switch: a log switch occurs when the current redo log file is completely filled and the
writer need to continue to next redo log group.
How ever you can switch redo log by issue command or set a regular interval to switch the log

redo log status:
1-Current: the redo log file that LGWR is currently writing to
2-Active: redo log file that are require for instance recover.
3-Inactive: redo log file that are not require for instance recover.

 

5-Resize datafile size

alter database datafile
'/u03/../xx_01.dbf'
resize 100M;
6-Show Free-Used Size in each datafile for a specific tablespace



select A.Tablespace_name,file_name,
sum(B.Bytes/1024/1024) AllocateMB,
sum(B.Bytes/1024/1024-A.BYTES/1024/1024) UsedMB,
sum(A.BYTES/1024/1024) FreeMB
 from dba_free_space A
inner join dba_data_files B
on A.FILE_ID=B.File_Id
where B.TABLESPACE_NAME='DM_FACT_200906'
group by file_name,A.Tablespace_name


7-How your data store in multiple data file?

 8-Save space in SYSTEM Tablespace
truncate table sys.aud$


9-How to change object from one schema to other schema 

1-use create table as select

2-use export and import

export data, after that import using remap_schema

10-drop tablespace and content and data file
drop tablespace tbs2 including contents and datafiles;

11-show total size of your database

SELECT SUM (bytes) / 1024 / 1024 / 1024 AS GB FROM dba_data_files;

 12-set default tablespace 

alter database default tablespace tbs2;