rpmdb: Thread/process 4330/140521027696384 failed: Thread died in Berkeley DB library

 

YUM FAILED

 

[root@ranesh yum.repos.d]# yum update

rpmdb: Thread/process 4330/140521027696384 failed: Thread died in Berkeley DB library

error: db3 error(-30974) from dbenv->failchk: DB_RUNRECOVERY: Fatal error, run database recovery

error: cannot open Packages index using db3 -  (-30974)

error: cannot open Packages database in /var/lib/rpm

CRITICAL:yum.main:

Error: rpmdb open failed

SOLUTION:

remove this  db* from this location /var/lib/rpm

[root@ranesh yum.repos.d]# rm /var/lib/rpm/__db*

rm: remove regular file `/var/lib/rpm/__db.001'? y

rm: remove regular file `/var/lib/rpm/__db.002'? y

rm: remove regular file `/var/lib/rpm/__db.003'? y

rm: remove regular file `/var/lib/rpm/__db.004'? y

[root@ranesh yum.repos.d]# yum update

Loaded plugins: aliases, changelog, kabi, presto, refresh-packagekit, security,

              : tmprepo, ulninfo, verify, versionlock

Loading support for kernel ABI

Setting up Update Process

ol6_UEK_latest                                           | 2.5 kB     00:00

Recover Oracle using an Offline Backup with RMAN

 

Recover Oracle using an Offline Backup with RMAN

 

Before you can restore your database using RMAN, the following needs to be properly in place (this assumes a full system loss):

* The operating system must be installed

* The Oracle software must be installed

* The RMAN backup files must be available

* You must create the file systems that the database files will be restored to.

Once these pieces are in place you can restore your database. In the event of a full system loss you will need to replace:

* The SPFILE

* The Control File

* The database datafiles

If the SPFILE or control file is intact, you will not need to replace that component.

Restoring the Oracle SPFILE

Restoring the SPFILE is an automated process in many cases with RMAN. You simply need to configure RMAN with the proper database DBID (which you should have recorded when you created the database) and you can then restore the SPFILE from any auto backup.

To restore the spfile, you still need to start the database, but you can do so with a minimal database parameter file. All you need to do is start your database instance is set the db_name parameter in a manual parameter file. Then you can start the instance using the startup command with the pfile parameter as seen in this

example:

Startup nomount pfile=c:\u01\app\oracle\product\12.1.0.2\database\initstand.ora

 

Once the instance is started, you can start RMAN and restore the SPFILE as seen in this example:

RMAN>restore spfile from autobackup;

 

Restoring the Oracle Control File

Restoring the control file is much like restoring the SPFILE. Again, you simply need to configure RMAN with the proper database DBID and you can then restore the control file from any auto backup.

To restore the control file, you still need to start the database, but you can do so with a minimal database parameter file. All you need to do is start your database instance is set the db_name parameter in a manual parameter file. Then you can start the instance using the startup command with the pfile parameter as seen in this example:

Startup nomount pfile=c:\u01\app\oracle\product\12.1.0.2\database\initstand.ora

 

Once the instance is started, you can start RMAN and restore the CONTROL FILE as seen in this example:

RMAN>restore controlfile from autobackup;

 

Restoring and Recovering the Database

Once you have recovered the SPFILE and the control file, restoring the database can be pretty easy. Again, many things can occur to make the process more complex, but assuming that you have restored your RMAN backup sets in the same place, and that all of your file systems are built correctly, and named the same as before, your restore the database with the following command:

RMAN>recover database noredo;

If the online archived redo logs are available, then change the command to the following:

RMAN>restore database;

Once the database is restored, you can then recover the database. Simply use the RMAN recover database command as seen here:

RMAN>recover database;

Once you have issued this command, your database should be recovered to the point of the backup.

Recover your Database using an Online Backup with RMAN

If you backed up your ARCHIVELOG mode database with RMAN then you will be able to recover to the point of the last available archived redo log (or online redo log if those are still available). The cool thing is that the RMAN procedure is almost the same regardless of if the backup was in ARCHIVELOG mode or NOARCHIVELOG mode.

First, follow the directions above for restores of the SPFILE and the control file. Now, simply use the restore database and recover database commands as seen here:

RMAN>restore database;

RMAN>recover database;

Again, Oracle will restore your database. It will also automatically recover the archived redo logs for you, so you don't have to worry about it.

 

 

 

RMAN OVERVIEW

 

                                                     RMAN

 

[oracle@ranesh ~]$ rman target /

Recovery Manager: Release 12.1.0.2.0 - Production on Thu Sep 17 20:00:45 2020

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

connected to target database: PRIME (DBID=2122042046)

 

RMAN> show all;         

using target database control file instead of recovery catalog

RMAN configuration parameters for database with db_unique_name STAND are:

CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default

CONFIGURE BACKUP OPTIMIZATION OFF; # default

CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default

CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default

CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default

CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE MAXSETSIZE TO UNLIMITED; # default

CONFIGURE ENCRYPTION FOR DATABASE OFF; # default

CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default

CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default

CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # default

CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default

 

To perforn a full database backup use:

 

RMAN> backup database;

Starting backup at 17-SEP-20

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=37 device type=DISK

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00001 name=/u01/app/oracle/oradata/stand/system01.dbf

input datafile file number=00003 name=/u01/app/oracle/oradata/stand/sysaux01.dbf

input datafile file number=00004 name=/u01/app/oracle/oradata/stand/undotbs01.dbf

input datafile file number=00006 name=/u01/app/oracle/oradata/stand/users01.dbf

channel ORA_DISK_1: starting piece 1 at 17-SEP-20

channel ORA_DISK_1: finished piece 1 at 17-SEP-20

piece handle=/u01/app/oracle/fast_recovery_area/STAND/backupset/2020_09_17/o1_mf_nnndf_TAG20200917T200915_hp6x8mmp_.bkp tag=TAG20200917T200915 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:56

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

including current control file in backup set

including current SPFILE in backup set

channel ORA_DISK_1: starting piece 1 at 17-SEP-20

channel ORA_DISK_1: finished piece 1 at 17-SEP-20

piece handle=/u01/app/oracle/fast_recovery_area/STAND/backupset/2020_09_17/o1_mf_ncsnf_TAG20200917T200915_hp6xbddz_.bkp tag=TAG20200917T200915 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

Finished backup at 17-SEP-20

 

To backup all archive logs use:

RMAN> BACKUP ARCHIVELOG ALL;

 

Starting backup at 17-SEP-20

using channel ORA_DISK_1

channel ORA_DISK_1: starting archived log backup set

channel ORA_DISK_1: specifying archived log(s) in backup set

input archived log thread=1 sequence=77 RECID=1 STAMP=1044910854

input archived log thread=1 sequence=78 RECID=2 STAMP=1044911049

input archived log thread=1 sequence=79 RECID=3 STAMP=1044911087

input archived log thread=1 sequence=80 RECID=4 STAMP=1044911458

input archived log thread=1 sequence=81 RECID=5 STAMP=1044911462

input archived log thread=1 sequence=82 RECID=6 STAMP=1044911473

input archived log thread=1 sequence=83 RECID=9 STAMP=1044911706

input archived log thread=1 sequence=84 RECID=11 STAMP=1044913561

channel ORA_DISK_1: starting piece 1 at 17-SEP-20

channel ORA_DISK_1: finished piece 1 at 17-SEP-20

piece handle=/u01/app/oracle/fast_recovery_area/STAND/backupset/2020_09_17/o1_mf_annnn_TAG20200917T201607_hp6xoho7_.bkp tag=TAG20200917T201607 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

channel ORA_DISK_1: starting archived log backup set

channel ORA_DISK_1: specifying archived log(s) in backup set

input archived log thread=1 sequence=1 RECID=12 STAMP=1044913749

input archived log thread=1 sequence=2 RECID=13 STAMP=1044914249

input archived log thread=1 sequence=3 RECID=14 STAMP=1044914459

input archived log thread=1 sequence=4 RECID=15 STAMP=1044914492

input archived log thread=1 sequence=5 RECID=16 STAMP=1044914513

input archived log thread=1 sequence=6 RECID=17 STAMP=1044915866

input archived log thread=1 sequence=7 RECID=18 STAMP=1044915890

input archived log thread=1 sequence=8 RECID=19 STAMP=1044916659

channel ORA_DISK_1: starting piece 1 at 17-SEP-20

channel ORA_DISK_1: finished piece 1 at 17-SEP-20

piece handle=/u01/app/oracle/fast_recovery_area/STAND/backupset/2020_09_17/o1_mf_annnn_TAG20200917T201607_hp6xok21_.bkp tag=TAG20200917T201607 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

channel ORA_DISK_1: starting archived log backup set

channel ORA_DISK_1: specifying archived log(s) in backup set

input archived log thread=1 sequence=1 RECID=20 STAMP=1044917218

input archived log thread=1 sequence=2 RECID=23 STAMP=1044917219

input archived log thread=1 sequence=3 RECID=26 STAMP=1044917224

input archived log thread=1 sequence=4 RECID=28 STAMP=1044917226

input archived log thread=1 sequence=5 RECID=30 STAMP=1044917228

channel ORA_DISK_1: starting piece 1 at 17-SEP-20

channel ORA_DISK_1: finished piece 1 at 17-SEP-20

piece handle=/u01/app/oracle/fast_recovery_area/STAND/backupset/2020_09_17/o1_mf_annnn_TAG20200917T201607_hp6xol7l_.bkp tag=TAG20200917T201607 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

Finished backup at 17-SEP-20

 

To backup the database and all archive log files use:

RMAN> BACKUP DATABASE PLUS ARCHIVELOG;

 

To backup the current controlfile :

RMAN> BACKUP CURRENT CONTROLFILE;

 

Starting backup at 17-SEP-20

using channel ORA_DISK_1

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

including current control file in backup set

channel ORA_DISK_1: starting piece 1 at 17-SEP-20

channel ORA_DISK_1: finished piece 1 at 17-SEP-20

piece handle=/u01/app/oracle/fast_recovery_area/STAND/backupset/2020_09_17/o1_mf_ncnnf_TAG20200917T202643_hp6y9dkk_.bkp tag=TAG20200917T202643 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

Finished backup at 17-SEP-20

 

SPFILE BACKUP

 

RMAN> BACKUP SPFILE;

Starting backup at 17-SEP-20

using channel ORA_DISK_1

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

including current SPFILE in backup set

channel ORA_DISK_1: starting piece 1 at 17-SEP-20

channel ORA_DISK_1: finished piece 1 at 17-SEP-20

piece handle=/u01/app/oracle/fast_recovery_area/STAND/backupset/2020_09_17/o1_mf_nnsnf_TAG20200917T202821_hp6ydf9r_.bkp tag=TAG20200917T202821 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

Finished backup at 17-SEP-20

 

Datafile backups

 

RMAN> backup datafile '/u01/app/oracle/oradata/stand/system01.dbf';

 

Starting backup at 17-SEP-20

using channel ORA_DISK_1

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00001 name=/u01/app/oracle/oradata/stand/system01.dbf

channel ORA_DISK_1: starting piece 1 at 17-SEP-20

channel ORA_DISK_1: finished piece 1 at 17-SEP-20

piece handle=/u01/app/oracle/fast_recovery_area/STAND/backupset/2020_09_17/o1_mf_nnndf_TAG20200917T202950_hp6yh6pv_.bkp tag=TAG20200917T202950 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:26

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

including current control file in backup set

including current SPFILE in backup set

channel ORA_DISK_1: starting piece 1 at 17-SEP-20

channel ORA_DISK_1: finished piece 1 at 17-SEP-20

piece handle=/u01/app/oracle/fast_recovery_area/STAND/backupset/2020_09_17/o1_mf_ncsnf_TAG20200917T202950_hp6yj19f_.bkp tag=TAG20200917T202950 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

Finished backup at 17-SEP-20

 

 

 

 

 

 

 

 

 

Alternatively specify datafile no

 

RMAN> backup datafile 1;

 

The data file number can be obtained from V$DATAFILE

 

RMAN> SELECT file#, name FROM v$datafile;

     FILE#                                                                                   NAME                                                                           

--------------------------------------------------------------------------------

         1                                                              /u01/app/oracle/oradata/stand/system01.dbf

 

         3                                                                 /u01/app/oracle/oradata/stand/sysaux01.dbf

 

         4                                                                  /u01/app/oracle/oradata/stand/undotbs01.dbf

 

         6                                                                /u01/app/oracle/oradata/stand/users01.dbf

 

 

 

 

......when the RMAN backup is running, if you want to see the proress, you can query the V$RMAN_STATUS

 

output

 

RMAN> SELECT OPERATION, STATUS, MBYTES_PROCESSED, START_TIME, END_TIME from V$RMAN_STATUS;

 

OPERATION                         STATUS                  MBYTES_PROCESSED

--------------------------------- ----------------------- ----------------

START_TIM END_TIME

--------- ---------

RMAN                              RUNNING                                0

17-SEP-20 17-SEP-20

 

DUPLICATE DB FROM ACTIVE USING B  FAILED                                 0

03-JUL-20 03-JUL-20

 

RMAN                              COMPLETED WITH ERRORS                  0

03-JUL-20 03-JUL-20

 

RMAN                              COMPLETED                              0

 

Incremental Backups

 

By default backups are full (level 0). Backups can also be incremental (level 1).

Incremental backups can be:

Differential - includes all changes since the last full or incremental backup

Cumulative - includes all changes since the last full backup

Differential backups require less space. Cumulative backups are faster to restore

 

RMAN> BACKUP INCREMENTAL LEVEL 1 DATABASE;

 

Starting backup at 17-SEP-20

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=39 device type=DISK

channel ORA_DISK_1: starting incremental level 0 datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00001 name=/u01/app/oracle/oradata/stand/system01.dbf

input datafile file number=00003 name=/u01/app/oracle/oradata/stand/sysaux01.dbf

input datafile file number=00004 name=/u01/app/oracle/oradata/stand/undotbs01.dbf

input datafile file number=00006 name=/u01/app/oracle/oradata/stand/users01.dbf

channel ORA_DISK_1: starting piece 1 at 17-SEP-20

channel ORA_DISK_1: finished piece 1 at 17-SEP-20

piece handle=/u01/app/oracle/fast_recovery_area/STAND/backupset/2020_09_17/o1_mf_nnnd0_TAG20200917T204810_hp6zkm6v_.bkp tag=TAG20200917T204810 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:55

channel ORA_DISK_1: starting incremental level 1 datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

including current control file in backup set

including current SPFILE in backup set

channel ORA_DISK_1: starting piece 1 at 17-SEP-20

channel ORA_DISK_1: finished piece 1 at 17-SEP-20

piece handle=/u01/app/oracle/fast_recovery_area/STAND/backupset/2020_09_17/o1_mf_ncsn1_TAG20200917T204810_hp6zmcvs_.bkp tag=TAG20200917T204810 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

Finished backup at 17-SEP-20

 

RMAN> BACKUP INCREMENTAL LEVEL 1 CUMULATIVE DATABASE;

 

Starting backup at 17-SEP-20

using channel ORA_DISK_1

channel ORA_DISK_1: starting incremental level 1 datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00001 name=/u01/app/oracle/oradata/stand/system01.dbf

skipping datafile 00001 because it has not changed

input datafile file number=00003 name=/u01/app/oracle/oradata/stand/sysaux01.dbf

skipping datafile 00003 because it has not changed

input datafile file number=00004 name=/u01/app/oracle/oradata/stand/undotbs01.dbf

skipping datafile 00004 because it has not changed

input datafile file number=00006 name=/u01/app/oracle/oradata/stand/users01.dbf

skipping datafile 00006 because it has not changed

channel ORA_DISK_1: backup cancelled because all files were skipped

channel ORA_DISK_1: starting incremental level 1 datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

including current control file in backup set

including current SPFILE in backup set

channel ORA_DISK_1: starting piece 1 at 17-SEP-20

channel ORA_DISK_1: finished piece 1 at 17-SEP-20

piece handle=/u01/app/oracle/fast_recovery_area/STAND/backupset/2020_09_17/o1_mf_ncsn1_TAG20200917T204949_hp6znpyt_.bkp tag=TAG20200917T204949 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

Finished backup at 17-SEP-20

 

Note that in order to take a level 1 backup, a level 0 backup must already exist.

A full backup using BACKUP DATABASE is not the same as a level 0 backup. - the LV column of the LIST BACKUP output is NULL after a full backup.

 

 level 0 backup

 

RMAN> BACKUP INCREMENTAL LEVEL 0 DATABASE;

 

Starting backup at 17-SEP-20

using channel ORA_DISK_1

channel ORA_DISK_1: starting incremental level 0 datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00001 name=/u01/app/oracle/oradata/stand/system01.dbf

input datafile file number=00003 name=/u01/app/oracle/oradata/stand/sysaux01.dbf

input datafile file number=00004 name=/u01/app/oracle/oradata/stand/undotbs01.dbf

input datafile file number=00006 name=/u01/app/oracle/oradata/stand/users01.dbf

channel ORA_DISK_1: starting piece 1 at 17-SEP-20

channel ORA_DISK_1: finished piece 1 at 17-SEP-20

piece handle=/u01/app/oracle/fast_recovery_area/STAND/backupset/2020_09_17/o1_mf_nnnd0_TAG20200917T205106_hp6zq2vb_.bkp tag=TAG20200917T205106 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:55

channel ORA_DISK_1: starting incremental level 0 datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

including current control file in backup set

including current SPFILE in backup set

channel ORA_DISK_1: starting piece 1 at 17-SEP-20

channel ORA_DISK_1: finished piece 1 at 17-SEP-20

piece handle=/u01/app/oracle/fast_recovery_area/STAND/backupset/2020_09_17/o1_mf_ncsn0_TAG20200917T205106_hp6zrv2p_.bkp tag=TAG20200917T205106 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

Finished backup at 17-SEP-20

 

 

 

 

Managing Tablespaces And Datafiles

 

Managing Tablespaces And Datafiles

You can create Locally Managed or Dictionary Managed Tablespaces. In prior versions of Oracle only Dictionary managed Tablespaces were available but from Oracle ver. 8i you can also create Locally Managed tablespaces.

To create a locally managed tablespace

SQL> create tablespace  Ranesh2 datafile '/u01/app/oracle/oradata/stand/ranesh2.dbf' size 50m extent management dictionary local autoallocate;

AUTOALLOCATE causes the tablespace to be system managed with a minimum extent size of 64K, You can specify that size in the SIZE clause of UNIFORM. If you omit SIZE, then the default size is 1M

To Create Dictionary Managed Tablespace

SQL> create tablespace  Ranesh2 datafile '/u01/app/oracle/oradata/stand/ranesh2.dbf' size 50m extent management dictionary;

Bigfile Tablespaces(10g)

A bigfile tablespace is a tablespace with a single, but very large (up to 4G blocks) datafile

To create a bigfile tablespace

SQL> create bigfile tablespace  Ranesh3 datafile '/u01/app/oracle/oradata/stand/ranesh3.dbf' size 50g:

 

To Extend the Size of a tablespace

Option 1

You can extend the size of a tablespace by increasing the size of an existing datafile

SQL> alter  database Ranesh2 datafile '/u01/app/oracle/oradata/stand/ranesh2.dbf' resize 100m;

Option 2

You can also extend the size of a tablespace by adding a new datafile to a tablespace

SQL> alter tablespace add datafile '/u01/app/oracle/oradata/stand/ranesh3.dbf' size 50m;

 

Option 3

SQL> alter database datafile '/u01/app/oracle/oradata/stand/ranesh3.dbf' auto extend ON next 5M maxsize 500M;

To decrease the size of a tablespace

SQL> alter  database Ranesh2 datafile '/u01/app/oracle/oradata/stand/ranesh2.dbf' resize 30m;

 

Taking tablespaces Offline or Online

To Take a Tablespace Offline

SQL>alter tablespace ranesh offline;

To again bring it back online .

SQL>alter tablespace ranesh online;

To take individual datafile offline type the following command

SQL>alter database datafile ‘/u01/app/oracle/oradata/stand/ranesh2.dbf’ offline;

Again to bring it back online give the following command

SQL> alter database datafile ‘/u01/app/oracle/oradata/stand/ranesh2.dbf’ online;

Note: You can’t take individual datafiles offline it the database is running in NOARCHIVELOG mode.  If the datafile has become corrupt or missing when the database is running in NOARCHIVELOG mode then you can only drop it by giving the following command

SQL>alter database datafile ‘‘/u01/app/oracle/oradata/stand/ranesh2.dbf’ offline for drop;

Making a tablespace read-only

Making a tablespace read-only prevents updates on all tables in the tablespace, regardless of a user's update privilege level.

To make a tablespace read only

SQL>alter tablespace ranesh read only;

Again to make it read write

sql> alter tablespace ranesh read write;

 

Renaming Tablespaces

alter tablespace users ranesh TO ranesh3;

Dropping Tablespaces

You can drop a tablespace and its contents (the segments contained in the tablespace) from the database if the tablespace and its contents are no longer required. You must have the DROP TABLESPACE system privilege to drop a tablespace.

To drop a tablespace give

SQL> drop tablespace ica;

This will drop the tablespace only if it is empty. If it is not empty and if you want to drop it

SQL>drop tablespace ica including contents;

This will drop the tablespace even if it is not empty. But the datafiles will not be deleted you have to use operating system command to delete the files.

But If  you include datafiles keyword then, the associated datafiles will also be deleted from the disk.

SQL>drop tablespace ica including contents and datafiles;

 Viewing Information about Tablespaces and Datafiles

Oracle has provided many Data dictionaries to view information about tablespaces and datafiles.  Some of them are:

To view information about Tablespaces in a database give the following query

SQL> select * from dba_tablespaces
SQL> select * from v$tablespace;

To view information about Datafiles

SQL> select * from dba_data_files;
SQL> select * from v$datafile;

To view information about Tempfiles

SQL> select * from dba_temp_files;
SQL> select * from v$tempfile;

To view information about free space in datafiles

SQL> select * from dba_free_space;

To view information about free space in tempfiles

SQL> select * from V$TEMP_SPACE_HEADER;

 

Renaming or Relocating Datafiles belonging to a Single Tablespace

1.       Take the tablespace offline

2.       Rename or Relocate the datafiles using operating system command

3.       Give the ALTER TABLESPACE with RENAME DATAFILE option to change the filenames within the Database.

4.       Bring the tablespace Online