Delete a logile member:

We cannot delete the current logfile member.

alter database drop logfile group 2;

See the sequence of the archived log.

select name, sequence# from v#archived_log;

Sequence number ... is current. That means upto to sequence number ...-1 the logs are archived.

OS Data block is the lowest unit in physical. Its in hard disk. Only OS is responsible for writting in it. Without PS permission that data will not written into OS data block.

Now for oracle they created their own Oracle data block to create their own sources and records. Oracle data block is logical not physical.

Combination of multiple oracle data block forms a extend. Combination of multiple extends forms a segment. Combination of multiple segments forms a tablespace. They are all logical

Inside tablespace there are data files. All data files are inside a tablespace. Tablespace is logical. And multiple tablespace form a database. So database is logical. Only what is physical is data file.

select file_id, file_name, tablespace_name, bytes/1024/1024 'MB", status, online_status from dba_data_files;

According to Oracle there could be maximum of 1022 datafile inside a tablespace.

Data file size:

The maximum size of a datafile depends on the datablock size.

8 KB datablock soze : 32 GB data file 4 KB datablock size: 16 GB data file

Big data file : No limit for maximum size.

Tablespace and Data file creation:

To create a data file we either have to store it in a already created table space or create a new tablespace and storr it there.

Create tablespace dammy1 datafile 'path of the datafile' size 10M autoextend on maxsize 200M;

This will create a tablespace with size of 10 MB. Autoextend will increase the size of the table space upto the maxsize which is 200 MB.

If the size of the tablespace 10 MB becomes full it will increase in size. Now how much do you think will increase after the tablespace becomes full. Is it increase by remaining 190 MB full. Or will it increases by 10MB.

To answer this question, if the command has nothing specified then it will increase by the original size of the table space which is 10 MB. But we can also do that.

Create tablespace dammy2 datafile 'path to the datafile' size 2M autoextend on next 1M maxsize 10M;

This will set the size increment by 1 MB.

Now we can add a datafile to an already created tablespace.

Alter tablespace dammy add datafile 'path to the datafile' size 20M;

This will add a data file to it. This datafile will not be autoextendable.

By the way as tablespace is a logical entity, the size on which the autoextend setting is set is the datafile not the tablespace.

We can do this.

Alter tablespace dammy add datafile 'path to the datafile' size 2M autoextend on next 1M maxsize 10M;

Alter tablespace dammy add datafile 'path to the datafile' size 2M autoextend on maxsize 10M;

We can also add a datafile in the same tablespace with both autoextend on and off.

Size of the database:

As database is a logical entity the sum of datafiles is the size of database. Control files, parameter files they are also physical. They are the support files for the database. So they are not counted. Main data are in data file.

There is a field in the view which can show us which is autoextendable and which is not.

Select file_id, file_name, tablespace_name, bytes/1024/1024 "MB", autoextensible from dba_data_files;

Desc v$datafile

We can see the same thing from this dynamic view. Same records but different field name.

To resize a tablespace manually without autoextend:

**If the datafile is full then users won't be able to log in to the database. And they won't be able to save anything in the database. **

In that case dba has to resize the database manually.

Alter database datafile 'path to the datafile' resize 200M;