Assuming that you are specifically asking how to shrink a tablespace and its related datafile(s) due to over-allocation/under-utilization, then you can run the command "alter database datafile '/directory-name/filename.dbf resize ???M;" where "directory-name" is the directory name/filesystem path that the datafile resides in, "filename.dbf" is the name of the datafile, and "???M" or "???G" is the size that you want to resize the datafile to. Note that you cannot shrink a datafile below the highwater mark of active blocks and will receive an error telling you that you cannot shrink the file, and if this happens you will need to export all of the tables out of the tablespace, truncate the tables, shrink the datafile(s), and re-import.
Last Wiki Answer Submitted: March 5, 2012 9:59 pm by TechTalker2,280 pts.
All Answer Wiki Contributors: TechTalker2,280 pts.