About the Oracle data import process encountered during the

A few days ago the teacher arranged a task to import a 50G dmp file into our Oracle server. Because I practiced this before, I thought it was a relatively simple thing.

Just try to use the Plsql directly to the server guide AH. Log in to the server with the DBA, add new users, assign permissions to the user, increase the table space (adaptive growth), and then I log in to the new user to start importing DMP files, who knows the direct encounter problems. ORA-01659: Unable to allocate more than 7 of the minextents (in the Tablespace perfstat), This is the error, because there is no time to write essays, it had to stick to the text.

The introduction of this error on the Internet is very detailed, probably means that the imported data table space is insufficient, to give several solutions, I listed below:

1, try to delete the table space to reestablish the larger.

2, modify the tablespace size, alter DATABASE datafile ' table space data file location ' size XXXM

3, set the tablespace to auto-expand, ALTER DATABASE datafile ' D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORACLE\PERFSTAT.DBF ' autoextend on NEXT 200M MAXSIZE UNLIMITED

Basically the opinion on the net is focused on the size of the table space, but I was in the creation of table space when the self-adaptive size AH. You can see that the final size of these table spaces is 32G, which is the maximum table space for a block size of 8k.

That being the case, shouldn't that be the problem?

I also thought, since this problem is due to lack of space, it will not be the space on the Oracle server is not enough, after all, is a 50G large file. And then we got a teacher. Oracle's admin account on the server glanced at it.

It can be found that Oracle is installed under U01 This folder, through the query I found that this directory belongs to the/dev/mapper/vg_hadoopdn05-lv_root partition, and this partition obviously does not fit the 50g file table.

I realized that when the teacher installed Oracle, I dug a hole, the Oracle's data storage location directly by default in the installation directory.

So I thought about dividing the rest of the partition's free space and seeing a detailed tutorial: http://blog.csdn.net/wangmuming/article/details/46777285

The writing is very detailed, but there is no egg to use. Because the elder brother on the above is on a physical volume (PV), and we have a lot of hard disk on the server, perhaps I am not enough technology, with the above method did not succeed in the free space to cut into the Oracle installation directory.

Again,,,, later, I thought, since it is the tablespace data file in the default directory, then I can not modify the location of the system table space, that is: The table space file is clipped to a large free space partition, and tell the system, table space location changes. then just do, copy the file directly to the 1T hard disk, and then use this SQL command to tell the system tablespace file location changed: Alter DATABASE rename file ' original location ' to ' new location ';

Step: Offline table space file, copy tablespace data file to destination, delete original file, modify Oracle tablespace point to location->online tablespace file.

And then I started to guide the data again.

。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。

After a night like this, the final data is done.

Haha, the problem is solved, the weekend can rest.

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

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

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

Tail:

Take a look at some of the SQL commands learned in the process, Memo.

Create tablespace name tablespace table space

DataFile ' tablespace data file name ' size 1000M autoextend on next 50M MaxSize Unlimited;

Statistics Space Details

Select UPPER (f.tablespace_name) as "tablespace name",
ROUND (D.availb_bytes, 2) as "Table space size (G)",
ROUND (d.max_bytes,2) as "final tablespace size (G)",
ROUND ((d.availb_bytes-f.used_bytes), 2) as "used Space (G)",
To_char (ROUND (d.availb_bytes-f.used_bytes)/d.availb_bytes * 100,
2), ' 999.99 ') as "use ratio",
ROUND (F.used_bytes, 6) as "free Space (G)",
F.max_bytes as "Max Block (M)"
From (
SELECT Tablespace_name,
ROUND (SUM (BYTES)/(1024x768 * 1024x768), 6) Used_bytes,
ROUND (MAX (BYTES)/(1024x768 * 1024x768), 6) max_bytes
From SYS. Dba_free_space
GROUP by Tablespace_name) F,
(SELECT DD.) Tablespace_name,
ROUND (SUM (DD). BYTES)/(1024x768 * 1024x768), 6) Availb_bytes,
ROUND (SUM (DECODE (DD). MaxBytes, 0, DD. BYTES, DD. MaxBytes)/(1024*1024*1024), 6) max_bytes
From SYS. Dba_data_files DD
GROUP by DD. Tablespace_name) D
WHERE D.tablespace_name = F.tablespace_name
ORDER by 4 DESC

Delete Table space

DROP tablespace table space name including CONTENTS and Datafiles CASCADE CONSTRAINTS;

Table space on the downline

Alter tablespace table space name Offline/online;

Querying table spatial data file locations

Select file_name, tablespace_name from Dba_data_files;

And so on, there are some Linux server commands. It's not a list. Need to check again later.

Finally, I attach the LVM structure diagram, a useful thing.

About the Oracle data import process encountered during the