Oracle: How do you resize a data file?

Question

How do I can quickly open a 8Gb dsv file with 40 million rows with oracle sql developer?
I did it with a best laptop with high cpu speed but it took one day, I'm looking for a faster way...

Answer 1

With a few exceptions, you need to use an ALTER DATABASE RESIZE DATAFILE command to resize a datafile.

Another exception is that you can use the ALTER TABLESPACE ADD DATAFILE AUTOEXTEND ON syntax to allow a database to resize itself, by growing larger as needed, with this syntax:

alter tablespace
   fred
add datafile
   /u01/oracle/oradata/booktst_users_02.dbf' 
size 150M
autoextend on;

However to resize a datafile directly, we must use the ALTER DATABASE DATAFILE RESIZE command once again, as seen in this example:

alter database 
datafile
   /u01/oracle/oradata/booktst_users_02.dbf'
resize 150M;