12c导入数据时data空间不足---解决办法
一、最直接的解决办法1：（删除其他不用的用户，给data腾出多余的空间）
Xshell 6 (Build 0095)
Copyright (c) 2002 NetSarang Computer, Inc. All rights reserved.
Type `help' to learn how to use Xshell prompt.
[C:\~]$
Connecting to 192.168.1.90:22...
Connection established.
To escape to local shell, press 'Ctrl+Alt+]'.
Last login: Tue Oct 23 16:16:07 2018 from 192.168.1.21
[root@oracle12c ~]# su - oracle
[oracle@oracle12c ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Tue Oct 23 16:42:29 2018
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> alter tablespace data add datafile '/home/oradata/data01.dbf ' size 2G autoextend on next 64k maxsize unlimited;
alter tablespace data add datafile '/home/oradata/data01.dbf ' size 2G autoextend on next 64k maxsize unlimited
*
ERROR at line 1:
ORA-01537: cannot add file '/home/oradata/data01.dbf ' - file already part of
database
SQL> ^C
SQL> alter tablespace data add datafile '/home/oradata/data02.dbf ' size 1G autoextend on next 64k maxsize unlimited;
Tablespace altered.
SQL> alter database datafile '/home/oradata/data02.dbf' resize 4G;
alter database datafile '/home/oradata/data02.dbf' resize 4G
*
ERROR at line 1:
ORA-01516: nonexistent log file, data file, or temporary file
"/home/oradata/data02.dbf" in the current container
SQL> alter database datafile '/home/oradata/data01.dbf' resize 4G;
Database altered.
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
[oracle@oracle12c ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Tue Oct 23 16:46:53 2018
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 CDB READ WRITE NO
4 AJHDB READ WRITE NO
SQL> alter session set container=ajhdb;
Session altered.
SQL> show con_name;
CON_NAME
------------------------------
AJHDB
SQL> alter session set container=ajhdb;
Session altered.
删除其他不用的用户 （hthse101501、1022、1023、0913、0927）
SQL> drop user hthse1022 cascade;
User dropped.
SQL> drop user hthse1023 cascade;
User dropped.
--创建 hthse1022 用户，并赋予密码
SQL> create user hthse1022 identified by hthse;
User created.
SQL> grant dba,connect,resource to hthse1022;
Grant succeeded.
SQL> alter user hthse1022 default tablespace data;
User altered.
二、解决办法2：（删除不用的数据文件，给data腾出多余的空间）
Last login: Tue Oct 23 10:34:51 2018 from 192.168.1.21
[root@oracle12c ~]# su - oracle
[oracle@oracle12c ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Tue Oct 23 14:32:35 2018
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> drop user hthse0927 cascade;
drop user hthse0927 cascade
*
ERROR at line 1:
ORA-01918: user 'HTHSE0927' does not exist
SQL> drop user hthse0913 cascade;
drop user hthse0913 cascade
*
ERROR at line 1:
ORA-01918: user 'HTHSE0913' does not exist
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
[oracle@oracle12c ~]$ su - root
密码：000000
[root@oracle12c ~]# cd /
[root@oracle12c /]# ls
bin dev home lib64 media mnt opt root selinux sys usr
boot etc lib lost+found misc net proc sbin srv tmp var
[root@oracle12c /]# ll
总用量 98
dr-xr-xr-x. 2 root root 4096 8月 23 09:14 bin
dr-xr-xr-x. 5 root root 1024 8月 22 07:46 boot
drwxr-xr-x. 20 root root 3780 10月 23 09:30 dev
drwxr-xr-x. 119 root root 12288 10月 23 10:10 etc
drwxr-xr-x. 7 root root 4096 8月 22 09:26 home
dr-xr-xr-x. 13 root root 4096 8月 23 09:14 lib
dr-xr-xr-x. 9 root root 12288 8月 23 09:14 lib64
drwx------. 2 root root 16384 8月 22 07:29 lost+found
drwxr-xr-x. 2 root root 4096 9月 23 2011 media
drwxr-xr-x. 2 root root 0 10月 23 09:30 misc
drwxr-xr-x. 2 root root 4096 9月 23 2011 mnt
drwxr-xr-x. 2 root root 0 10月 23 09:30 net
drwxr-xr-x. 4 root root 4096 8月 22 09:10 opt
dr-xr-xr-x. 416 root root 0 10月 23 09:30 proc
dr-xr-x---. 27 root root 4096 10月 23 14:03 root
dr-xr-xr-x. 2 root root 12288 8月 23 09:14 sbin
drwxr-xr-x. 7 root root 0 10月 23 09:30 selinux
drwxr-xr-x. 2 root root 4096 9月 23 2011 srv
drwxr-xr-x 13 root root 0 10月 23 09:30 sys
drwxrwxrwt. 9 root root 4096 10月 23 10:35 tmp
drwxr-xr-x. 13 root root 4096 8月 22 07:32 usr
drwxr-xr-x. 22 root root 4096 8月 22 07:40 var
[root@oracle12c /]# cd /oradata/
-bash: cd: /oradata/: 没有那个文件或目录
[root@oracle12c /]# cd /home
[root@oracle12c home]# ls
lost+found oracle oradata software u01
[root@oracle12c home]# cd oradata
[root@oracle12c oradata]# ls
data01.dbf data02.dbf data20181017.dbf dir
[root@oracle12c oradata]# cd dir
[root@oracle12c dir]# ls
123.dmp Desktop0927.zip hthse0826_sys.sql hthse0927.log hthse1015_sys.sql
123.log Desktop.rar hthse0910.dmp hthse0927_sys.sql hthse1015.zip
20180910.zip hthse0806.dmp hthse0910.log hthse1009.dmp hthse1022.dmp
20180912.zip hthse0806.log hthse0910_sys.sql hthse1009.log hthse1022.log
20181016.zip hthse0806_sys.sql hthse0912.dmp hthse1009_sys.sql hthse1022_sys.sql
2018.9.10 hthse0806.zip hthse0912.log hthse1009.zip hthse1022.zip
2018.9.10.zip hthse0826.dmp hthse0912_sys.sql hthse1015.dmp orabak.sh
Desktop0927 hthse0826.log hthse0927.dmp hthse1015.log
[root@oracle12c dir]#
删除所以文件：rm
备注：rm hthse.log ：删除所有以hthse.log结尾的文件
[root@oracle12c dir]# rm hthse09*.log
rm：是否删除普通文件 "hthse0910.log"？y
rm：是否删除普通文件 "hthse0912.log"？y
rm：是否删除普通文件 "hthse0927.log"？y
[root@oracle12c dir]# ls
123.dmp Desktop0927.zip hthse0826_sys.sql hthse1009.log hthse1022.log
123.log Desktop.rar hthse0910.dmp hthse1009_sys.sql hthse1022_sys.sql
20180910.zip hthse0806.dmp hthse0910_sys.sql hthse1009.zip hthse1022.zip
20180912.zip hthse0806.log hthse0912.dmp hthse1015.dmp orabak.sh
20181016.zip hthse0806_sys.sql hthse0912_sys.sql hthse1015.log
2018.9.10 hthse0806.zip hthse0927.dmp hthse1015_sys.sql
2018.9.10.zip hthse0826.dmp hthse0927_sys.sql hthse1015.zip
Desktop0927 hthse0826.log hthse1009.dmp hthse1022.dmp
[root@oracle12c dir]#
删除普通文件：rm *
[root@oracle12c dir]# ls
123.dmp Desktop0927.zip hthse0826_sys.sql hthse1009.log hthse1022.log
123.log Desktop.rar hthse0910.dmp hthse1009_sys.sql hthse1022_sys.sql
20180910.zip hthse0806.dmp hthse0910_sys.sql hthse1009.zip hthse1022.zip
20180912.zip hthse0806.log hthse0912.dmp hthse1015.dmp orabak.sh
20181016.zip hthse0806_sys.sql hthse0912_sys.sql hthse1015.log
2018.9.10 hthse0806.zip hthse0927.dmp hthse1015_sys.sql
2018.9.10.zip hthse0826.dmp hthse0927_sys.sql hthse1015.zip
Desktop0927 hthse0826.log hthse1009.dmp hthse1022.dmp
[root@oracle12c dir]# rm hthse09*.dmp
rm：是否删除普通文件 "hthse0910.dmp"？y
rm：是否删除普通文件 "hthse0912.dmp"？y
rm：是否删除普通文件 "hthse0927.dmp"？y
[root@oracle12c dir]# ls
123.dmp Desktop0927 hthse0826.dmp hthse1009.log hthse1022.dmp
123.log Desktop0927.zip hthse0826.log hthse1009_sys.sql hthse1022.log
20180910.zip Desktop.rar hthse0826_sys.sql hthse1009.zip hthse1022_sys.sql
20180912.zip hthse0806.dmp hthse0910_sys.sql hthse1015.dmp hthse1022.zip
20181016.zip hthse0806.log hthse0912_sys.sql hthse1015.log orabak.sh
2018.9.10 hthse0806_sys.sql hthse0927_sys.sql hthse1015_sys.sql
2018.9.10.zip hthse0806.zip hthse1009.dmp hthse1015.zip
[root@oracle12c dir]# rm 201809*.zip
rm：是否删除普通文件 "20180910.zip"？y
rm：是否删除普通文件 "20180912.zip"？y
[root@oracle12c dir]#
[root@oracle12c dir]# ls
123.dmp Desktop0927.zip hthse0826.dmp hthse1009.dmp hthse1015_sys.sql orabak.sh
123.log Desktop.rar hthse0826.log hthse1009.log hthse1015.zip
20181016.zip hthse0806.dmp hthse0826_sys.sql hthse1009_sys.sql hthse1022.dmp
2018.9.10 hthse0806.log hthse0910_sys.sql hthse1009.zip hthse1022.log
2018.9.10.zip hthse0806_sys.sql hthse0912_sys.sql hthse1015.dmp hthse1022_sys.sql
Desktop0927 hthse0806.zip hthse0927_sys.sql hthse1015.log hthse1022.zip
[root@oracle12c dir]# rm hthse1009*
rm：是否删除普通文件 "hthse1009.dmp"？y
rm：是否删除普通文件 "hthse1009.log"？y
rm：是否删除普通空文件 "hthse1009_sys.sql"？y
rm：是否删除普通文件 "hthse1009.zip"？y
[root@oracle12c dir]# ls
123.dmp Desktop0927 hthse0806_sys.sql hthse0910_sys.sql hthse1015_sys.sql hthse1022.zip
123.log Desktop0927.zip hthse0806.zip hthse0912_sys.sql hthse1015.zip orabak.sh
20181016.zip Desktop.rar hthse0826.dmp hthse0927_sys.sql hthse1022.dmp
2018.9.10 hthse0806.dmp hthse0826.log hthse1015.dmp hthse1022.log
2018.9.10.zip hthse0806.log hthse0826_sys.sql hthse1015.log hthse1022_sys.sql
[root@oracle12c dir]# rm Desktop0927*
rm: 无法删除"Desktop0927": 是一个目录
rm：是否删除普通文件 "Desktop0927.zip"？y
[root@oracle12c dir]# ls
123.dmp Desktop0927 hthse0806.zip hthse0912_sys.sql hthse1015.zip orabak.sh
123.log Desktop.rar hthse0826.dmp hthse0927_sys.sql hthse1022.dmp
20181016.zip hthse0806.dmp hthse0826.log hthse1015.dmp hthse1022.log
2018.9.10 hthse0806.log hthse0826_sys.sql hthse1015.log hthse1022_sys.sql
2018.9.10.zip hthse0806_sys.sql hthse0910_sys.sql hthse1015_sys.sql hthse1022.zip
[root@oracle12c dir]# rm hthse1015*
rm：是否删除普通文件 "hthse1015.dmp"？y
rm：是否删除普通文件 "hthse1015.log"？y
rm：是否删除普通空文件 "hthse1015_sys.sql"？y
rm：是否删除普通文件 "hthse1015.zip"？n ---因为用户名hthse1016的数据文件时hthse1015的数据文件；
[root@oracle12c dir]# ls
123.dmp 2018.9.10.zip hthse0806.log hthse0826.log hthse0927_sys.sql hthse1022_sys.sql
123.log Desktop0927 hthse0806_sys.sql hthse0826_sys.sql hthse1015.zip hthse1022.zip
20181016.zip Desktop.rar hthse0806.zip hthse0910_sys.sql hthse1022.dmp orabak.sh
2018.9.10 hthse0806.dmp hthse0826.dmp hthse0912_sys.sql hthse1022.log
[root@oracle12c dir]# ls
123.dmp 2018.9.10.zip hthse0806.log hthse0826.log hthse0927_sys.sql hthse1022_sys.sql
123.log Desktop0927 hthse0806_sys.sql hthse0826_sys.sql hthse1015.zip hthse1022.zip
20181016.zip Desktop.rar hthse0806.zip hthse0910_sys.sql hthse1022.dmp orabak.sh
2018.9.10 hthse0806.dmp hthse0826.dmp hthse0912_sys.sql hthse1022.log
[root@oracle12c dir]#
[root@oracle12c dir]# ls
123.dmp 2018.9.10.zip hthse0806.log hthse0826.log hthse0927_sys.sql hthse1022_sys.sql
123.log Desktop0927 hthse0806_sys.sql hthse0826_sys.sql hthse1015.zip hthse1022.zip
20181016.zip Desktop.rar hthse0806.zip hthse0910_sys.sql hthse1022.dmp orabak.sh
2018.9.10 hthse0806.dmp hthse0826.dmp hthse0912_sys.sql hthse1022.log
[root@oracle12c dir]# ls
123.dmp 2018.9.10.zip hthse0806.log hthse0826.log hthse0927_sys.sql hthse1022_sys.sql
123.log Desktop0927 hthse0806_sys.sql hthse0826_sys.sql hthse1015.zip hthse1022.zip
20181016.zip Desktop.rar hthse0806.zip hthse0910_sys.sql hthse1022.dmp orabak.sh
2018.9.10 hthse0806.dmp hthse0826.dmp hthse0912_sys.sql hthse1022.log
[root@oracle12c dir]# rm Desktop09*
rm: 无法删除"Desktop0927": 是一个目录
[root@oracle12c dir]# rm hthse09*
rm：是否删除普通空文件 "hthse0910_sys.sql"？y
rm：是否删除普通空文件 "hthse0912_sys.sql"？y
rm：是否删除普通空文件 "hthse0927_sys.sql"？y
[root@oracle12c dir]# ls
123.dmp 2018.9.10.zip hthse0806.log hthse0826.log hthse1022.log
123.log Desktop0927 hthse0806_sys.sql hthse0826_sys.sql hthse1022_sys.sql
20181016.zip Desktop.rar hthse0806.zip hthse1015.zip hthse1022.zip
2018.9.10 hthse0806.dmp hthse0826.dmp hthse1022.dmp orabak.sh
[root@oracle12c dir]# rm hthse1022*
rm：是否删除普通文件 "hthse1022.dmp"？y
rm：是否删除普通文件 "hthse1022.log"？y
rm：是否删除普通空文件 "hthse1022_sys.sql"？y
rm：是否删除普通文件 "hthse1022.zip"？y
[root@oracle12c dir]# ls
123.dmp 2018.9.10 Desktop.rar hthse0806_sys.sql hthse0826.log orabak.sh
123.log 2018.9.10.zip hthse0806.dmp hthse0806.zip hthse0826_sys.sql
20181016.zip Desktop0927 hthse0806.log hthse0826.dmp hthse1015.zip
[root@oracle12c dir]#
[root@oracle12c dir]# rm hthse0826*
rm：是否删除普通文件 "hthse0826.dmp"？y
rm：是否删除普通文件 "hthse0826.log"？y
rm：是否删除普通空文件 "hthse0826_sys.sql"？y
[root@oracle12c dir]# ls
123.dmp 20181016.zip 2018.9.10.zip Desktop.rar hthse0806.log hthse0806.zip orabak.sh
123.log 2018.9.10 Desktop0927 hthse0806.dmp hthse0806_sys.sql hthse1015.zip
[root@oracle12c dir]#
二、重新上传10.22号的数据文件，解压、授权、导入
（1）上传数据文件
[root@oracle12c dir]# ls
[root@oracle12c dir]#
[root@oracle12c dir]# chown oracle:oinstall hthse1022.zip
[root@oracle12c dir]# chmod 775 hthse1022.zip
[root@oracle12c dir]# unzip hthse1022.zip
Archive: hthse1022.zip
inflating: hthse1022.dmp
inflating: hthse1022.log
[root@oracle12c dir]# ll
[root@oracle12c dir]# chown oracle:oinstall hthse1022*
[root@oracle12c dir]# chmod 775 hthse1022*
[root@oracle12c dir]# ll
（2）
[root@oracle12c dir]# su - oracle
[oracle@oracle12c ~]$ export NLS_LANG=american_america.ZHS16GBK
[oracle@oracle12c ~]$ imp hthse1022/hthse@192.168.1.90:1521/ajhdb file=/home/oradata/dir/hthse1022.dmp fromuser=hthse touser=hthse1022 log=/home/oradata/dir/hthse1022.log
三、使用新的用户导入（hthse1022的用户导入有问题，）
Xshell 6 (Build 0095)
Copyright (c) 2002 NetSarang Computer, Inc. All rights reserved.
Type `help' to learn how to use Xshell prompt.
[C:\~]$
Connecting to 192.168.1.90:22...
Connection established.
To escape to local shell, press 'Ctrl+Alt+]'.
Last login: Tue Oct 23 14:03:52 2018 from 192.168.1.21
[root@oracle12c ~]# su - oracle
[oracle@oracle12c ~]$ cd /oradata/
-bash: cd: /oradata/: 没有那个文件或目录
[oracle@oracle12c ~]$ cd /oradata/
-bash: cd: /oradata/: 没有那个文件或目录
[oracle@oracle12c ~]$ lsnrctl status
LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 23-OCT-2018 15:43:35
Copyright (c) 1991, 2016, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle12c)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 12.2.0.1.0 - Production
Start Date 23-OCT-2018 10:36:29
Uptime 0 days 5 hr. 7 min. 9 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /home/u01/app/oracle/product/12.2.0/db_1/network/admin/listener.ora
Listener Log File /home/u01/app/oracle/diag/tnslsnr/oracle12c/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle12c)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "73fd43c6319f2e8ee0535a01a8c09536" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Service "73fe79106d53333ae0535a01a8c0477f" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Service "ajhdb" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Service "cdb" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orcl" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orclXDB" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@oracle12c ~]$ cd / ordata/
[oracle@oracle12c /]$ ls
bin dev home lib64 media mnt opt root selinux sys usr
boot etc lib lost+found misc net proc sbin srv tmp var
[oracle@oracle12c /]$ su - root
密码：000000
[root@oracle12c ~]# cd ..
[root@oracle12c /]# ls
bin dev home lib64 media mnt opt root selinux sys usr
boot etc lib lost+found misc net proc sbin srv tmp var
[root@oracle12c /]# cd /home
[root@oracle12c home]# ls
lost+found oracle oradata software u01
[root@oracle12c home]# cd oradata
[root@oracle12c oradata]# ls
data01.dbf data02.dbf data20181017.dbf dir
[root@oracle12c oradata]# cd dir
[root@oracle12c dir]# ls
123.dmp 2018.9.10 Desktop.rar hthse0806_sys.sql hthse1022.dmp hthse1022.zip
123.log 2018.9.10.zip hthse0806.dmp hthse0806.zip hthse1022.log orabak.sh
20181016.zip Desktop0927 hthse0806.log hthse1015.zip hthse1022_sys.sql
[root@oracle12c dir]# chown oracle:oinstall hthse1022.zip
[root@oracle12c dir]# chmod 775 hthse1022.zip
[root@oracle12c dir]# unzip hthse1022.zip
Archive: hthse1022.zip
replace hthse1022.dmp? [y]es, [n]o, [A]ll, [N]one, [r]ename: y
inflating: hthse1022.dmp
replace hthse1022.log? [y]es, [n]o, [A]ll, [N]one, [r]ename: n
[root@oracle12c dir]# ll
总用量 6019816
-rw-r--r--. 1 root root 1485414400 9月 7 18:45 123.dmp
-rw-r--r--. 1 root root 103628 9月 7 18:45 123.log
-rwxrwxr-x. 1 oracle oinstall 346044546 10月 16 10:54 20181016.zip
drwxr-xr-x. 2 root root 4096 9月 10 17:14 2018.9.10
-rw-r--r--. 1 root root 10828928 9月 10 17:03 2018.9.10.zip
drwxr-xr-x. 2 root root 4096 9月 28 14:25 Desktop0927
-rw-r--r--. 1 root root 286156188 8月 26 18:31 Desktop.rar
-rwxrwxr-x. 1 oracle oinstall 1483939840 8月 6 19:21 hthse0806.dmp
-rwxrwxr-x. 1 oracle oinstall 105366 8月 22 12:14 hthse0806.log
-rw-r--r--. 1 oracle oinstall 0 8月 22 11:25 hthse0806_sys.sql
-rwxrwxr-x. 1 oracle oinstall 334248322 8月 22 11:20 hthse0806.zip
-rwxrwxr-x. 1 oracle oinstall 346044546 10月 15 14:18 hthse1015.zip
-rw-r--r--. 1 root root 1518010368 10月 22 12:23 hthse1022.dmp
-rwxrwxr-x. 1 oracle oinstall 6003021 10月 23 15:32 hthse1022.log
-rw-r--r--. 1 oracle oinstall 0 10月 23 15:30 hthse1022_sys.sql
-rwxrwxr-x. 1 oracle oinstall 347346047 10月 23 15:14 hthse1022.zip
-rwxr-xr-x. 1 root root 750 9月 7 18:43 orabak.sh
[root@oracle12c dir]# chown oracle:oinstall hthse1022*
[root@oracle12c dir]# chmod 775 hthse1022*
[root@oracle12c dir]# su - oracle
[oracle@oracle12c ~]$ export NLS_LANG=american_america.ZHS16GBK
[oracle@oracle12c ~]$
[oracle@oracle12c ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Tue Oct 23 15:48:05 2018
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 CDB READ WRITE NO
4 AJHDB READ WRITE NO
SQL> alter session set container=ajhdb;
Session altered.
SQL> show con_name;
CON_NAME
------------------------------
AJHDB
SQL> alter session set container=ajhdb;
Session altered.
SQL> create user
2
SQL> create user hthse1023 identified by hthse;
User created.
SQL> grant dba,connect,resource to hthse1023;
Grant succeeded.
SQL> alter user hthse1023 default tablespace data;
User altered.
SQL> quit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
[oracle@oracle12c ~]$ lsnrctl stuts
LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 23-OCT-2018 15:50:46
Copyright (c) 1991, 2016, Oracle. All rights reserved.
NL-00853: undefined command "stuts". Try "help"
[oracle@oracle12c ~]$ lsnrctl status
LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 23-OCT-2018 15:50:55
Copyright (c) 1991, 2016, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle12c)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 12.2.0.1.0 - Production
Start Date 23-OCT-2018 10:36:29
Uptime 0 days 5 hr. 14 min. 28 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /home/u01/app/oracle/product/12.2.0/db_1/network/admin/listener.ora
Listener Log File /home/u01/app/oracle/diag/tnslsnr/oracle12c/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle12c)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "73fd43c6319f2e8ee0535a01a8c09536" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Service "73fe79106d53333ae0535a01a8c0477f" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Service "ajhdb" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Service "cdb" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orcl" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orclXDB" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@oracle12c ~]$ sqlplus / as sysdba-
SQL*Plus: Release 12.2.0.1.0 Production on Tue Oct 23 15:51:04 2018
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL>
SQL> show con_name;
CON_NAME
------------------------------
CDB$ROOT
SQL> alter session set container=ajhdb;
Session altered.
SQL> show con_name;
CON_NAME
------------------------------
AJHDB
SQL> create or replace directory dir as '/oradata/dir';
Directory created.
SQL> grant read ,write on directory dir to hthse1023;
Grant succeeded.
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
[oracle@oracle12c ~]$ lsnrctl status
LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 23-OCT-2018 15:52:41
Copyright (c) 1991, 2016, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle12c)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 12.2.0.1.0 - Production
Start Date 23-OCT-2018 10:36:29
Uptime 0 days 5 hr. 16 min. 15 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /home/u01/app/oracle/product/12.2.0/db_1/network/admin/listener.ora
Listener Log File /home/u01/app/oracle/diag/tnslsnr/oracle12c/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle12c)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "73fd43c6319f2e8ee0535a01a8c09536" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Service "73fe79106d53333ae0535a01a8c0477f" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Service "ajhdb" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Service "cdb" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orcl" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orclXDB" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@oracle12c ~]$ cd / oradata/
[oracle@oracle12c /]$ ls
bin dev home lib64 media mnt opt root selinux sys usr
boot etc lib lost+found misc net proc sbin srv tmp var
[oracle@oracle12c /]$ su - root
密码：000000
[root@oracle12c ~]# cd /home
[root@oracle12c home]# ls
lost+found oracle oradata software u01
[root@oracle12c home]# cd oradata
[root@oracle12c oradata]# ls
data01.dbf data02.dbf data20181017.dbf dir
[root@oracle12c oradata]# cd dir
[root@oracle12c dir]# ls
123.dmp 2018.9.10 Desktop.rar hthse0806_sys.sql hthse1022.dmp hthse1022.zip
123.log 2018.9.10.zip hthse0806.dmp hthse0806.zip hthse1022.log orabak.sh
20181016.zip Desktop0927 hthse0806.log hthse1015.zip hthse1022_sys.sql
[root@oracle12c dir]# rm hthse1022.dmp
rm：是否删除普通文件 "hthse1022.dmp"？y
[root@oracle12c dir]# rm hthse1022.log
rm：是否删除普通文件 "hthse1022.log"？y
[root@oracle12c dir]# rm hthse1022_sys.sql
rm：是否删除普通空文件 "hthse1022_sys.sql"？y
[root@oracle12c dir]# ls
123.dmp 20181016.zip 2018.9.10.zip Desktop.rar hthse0806.log hthse0806.zip hthse1022.zip
123.log 2018.9.10 Desktop0927 hthse0806.dmp hthse0806_sys.sql hthse1015.zip orabak.sh
[root@oracle12c dir]# chown oracle:oinstall hthse1022.zip
[root@oracle12c dir]# chmod 775 hthse1022.zip
[root@oracle12c dir]# unzip hthse1022.zip
Archive: hthse1022.zip
inflating: hthse1022.dmp
inflating: hthse1022.log
[root@oracle12c dir]# ll
总用量 6014056
-rw-r--r--. 1 root root 1485414400 9月 7 18:45 123.dmp
-rw-r--r--. 1 root root 103628 9月 7 18:45 123.log
-rwxrwxr-x. 1 oracle oinstall 346044546 10月 16 10:54 20181016.zip
drwxr-xr-x. 2 root root 4096 9月 10 17:14 2018.9.10
-rw-r--r--. 1 root root 10828928 9月 10 17:03 2018.9.10.zip
drwxr-xr-x. 2 root root 4096 9月 28 14:25 Desktop0927
-rw-r--r--. 1 root root 286156188 8月 26 18:31 Desktop.rar
-rwxrwxr-x. 1 oracle oinstall 1483939840 8月 6 19:21 hthse0806.dmp
-rwxrwxr-x. 1 oracle oinstall 105366 8月 22 12:14 hthse0806.log
-rw-r--r--. 1 oracle oinstall 0 8月 22 11:25 hthse0806_sys.sql
-rwxrwxr-x. 1 oracle oinstall 334248322 8月 22 11:20 hthse0806.zip
-rwxrwxr-x. 1 oracle oinstall 346044546 10月 15 14:18 hthse1015.zip
-rw-r--r--. 1 root root 1518010368 10月 22 12:23 hthse1022.dmp
-rw-r--r--. 1 root root 103699 10月 22 12:17 hthse1022.log
-rwxrwxr-x. 1 oracle oinstall 347346047 10月 23 15:14 hthse1022.zip
-rwxr-xr-x. 1 root root 750 9月 7 18:43 orabak.sh
[root@oracle12c dir]# chown oracle:oinstall hthse1022*
[root@oracle12c dir]# chmod 775 hthse1022*
[root@oracle12c dir]# ll
总用量 6014056
-rw-r--r--. 1 root root 1485414400 9月 7 18:45 123.dmp
-rw-r--r--. 1 root root 103628 9月 7 18:45 123.log
-rwxrwxr-x. 1 oracle oinstall 346044546 10月 16 10:54 20181016.zip
drwxr-xr-x. 2 root root 4096 9月 10 17:14 2018.9.10
-rw-r--r--. 1 root root 10828928 9月 10 17:03 2018.9.10.zip
drwxr-xr-x. 2 root root 4096 9月 28 14:25 Desktop0927
-rw-r--r--. 1 root root 286156188 8月 26 18:31 Desktop.rar
-rwxrwxr-x. 1 oracle oinstall 1483939840 8月 6 19:21 hthse0806.dmp
-rwxrwxr-x. 1 oracle oinstall 105366 8月 22 12:14 hthse0806.log
-rw-r--r--. 1 oracle oinstall 0 8月 22 11:25 hthse0806_sys.sql
-rwxrwxr-x. 1 oracle oinstall 334248322 8月 22 11:20 hthse0806.zip
-rwxrwxr-x. 1 oracle oinstall 346044546 10月 15 14:18 hthse1015.zip
-rwxrwxr-x. 1 oracle oinstall 1518010368 10月 22 12:23 hthse1022.dmp
-rwxrwxr-x. 1 oracle oinstall 103699 10月 22 12:17 hthse1022.log
-rwxrwxr-x. 1 oracle oinstall 347346047 10月 23 15:14 hthse1022.zip
-rwxr-xr-x. 1 root root 750 9月 7 18:43 orabak.sh
[root@oracle12c dir]# su - oracle
[oracle@oracle12c ~]$ export NLS_LANG=american_america.ZHS16GBK
[oracle@oracle12c ~]$ imp hthse1023/hthse@192.168.1.90:1521/ajhdb file=/home/oradata/dir/hthse1022.dmp fromuser=hthse touser=hthse1023 log=/home/oradata/dir/hthse1022.log
Import: Release 12.2.0.1.0 - Production on Tue Oct 23 15:57:20 2018
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Export file created by EXPORT:V12.02.00 via conventional path
Warning: the objects were exported by HTHSE, not by you
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
export server uses UTF8 NCHAR character set (possible ncharset conversion)
IMP-00403:
Warning: This import generated a separate SQL file "/home/oradata/dir/hthse1022_sys.sql" which contains DDL that failed due to a privilege issue.
IMP-00017: following statement failed with ORACLE error 2304:
"CREATE TYPE "WM_CONCAT_IMPL" TIMESTAMP '2018-09-11:23:03:01' OID '759AC527E"
"9DEBF0BE053716B0B0A5071' AS OBJECT"
"-- AUTHID CURRENT_USER AS OBJECT"
"("
"CURR_STR VARCHAR2(32767),"
"STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT WM_CONCAT_IMPL) RETURN "
"NUMBER,"
"MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT WM_CONCAT_IMPL,"
"P1 IN VARCHAR2) RETURN NUMBER,"
"MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN WM_CONCAT_IMPL,"
"RETURNVALUE OUT VARCHAR2,"
"FLAGS IN NUMBER)"
"RETURN NUMBER,"
"MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT WM_CONCAT_IMPL,"
"SCTX2 IN WM_CONCAT_IMPL) RETURN NUMBER"
")"
IMP-00003: ORACLE error 2304 encountered
ORA-02304: invalid object identifier literal
四、扩容
Disconnected from remote host(AJH0910) at 16:19:19.
Type `help' to learn how to use Xshell prompt.
[C:\~]$
Connecting to 192.168.1.90:22...
Connection established.
To escape to local shell, press 'Ctrl+Alt+]'.
Last login: Tue Oct 23 15:42:23 2018 from 192.168.1.21
[root@oracle12c ~]# su - oracle
[oracle@oracle12c ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Tue Oct 23 16:18:20 2018
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> show paramerer sess
SP2-0158: unknown SHOW option "paramerer"
SP2-0158: unknown SHOW option "sess"
SQL> show parameter sess
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
java_max_sessionspace_size integer 0
java_soft_sessionspace_limit integer 0
license_max_sessions integer 0
license_sessions_warning integer 0
session_cached_cursors integer 50
session_max_open_files integer 10
sessions integer 772
shared_server_sessions integer
SQL> show parameter pro
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
approx_for_aggregation boolean FALSE
approx_for_count_distinct boolean FALSE
approx_for_percentile string none
aq_tm_processes integer 1
asm_io_processes integer 20
cell_offload_processing boolean TRUE
db_lost_write_protect string NONE
db_performance_profile string
db_writer_processes integer 1
gcs_server_processes integer 0
global_txn_processes integer 1
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes integer 4000
log_archive_max_processes integer 4
outbound_dblink_protocols string ALL
processes integer 500
processor_group_name string
sec_protocol_error_further_action string (DROP,3)
sec_protocol_error_trace_action string TRACE
SQL> alter system set processes=900 scope=spfile;
System altered.
SQL> alter system set sessions=955 scope=spfile;
System altered.
SQL> show parameter sess
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
java_max_sessionspace_size integer 0
java_soft_sessionspace_limit integer 0
license_max_sessions integer 0
license_sessions_warning integer 0
session_cached_cursors integer 50
session_max_open_files integer 10
sessions integer 772
shared_server_sessions integer
SQL> select f.tablespace_name,
a.total,
f.free,
round((f.free/a.total)*100) "% Free"
from (select tablespace_name,
sum(bytes/(1024*1024)) total
from dba_data_files
group by tablespace_name) a,
(select tablespace_name,
round(sum(bytes/(1024*1024))) free
from dba_free_space
group by tablespace_name) f
WHERE a.tablespace_name = f.tablespace_name(+)
order by "% Free"; 2 3 4 5 6 7 8 9 10 11 12 13 14
TABLESPACE_NAME TOTAL FREE % Free
------------------------------ ---------- ---------- ----------
SYSTEM 830 0 0
SYSAUX 1120 142 13
UNDOTBS1 100 73 73
USERS 5 4 80
DATA 30720 30719 100
SQL> select a.file_name,a.tablespace_name,a.bytes/1024/1024||'M' from dba_data_files a ;
FILE_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME A.BYTES/1024/1024||'M'
------------------------------ -----------------------------------------
/home/u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_fqsh68f7_.dbf
SYSTEM 830M
/home/u01/app/oracle/oradata/ORCL/datafile/o1_mf_sysaux_fqsh7d5g_.dbf
SYSAUX 1120M
/home/u01/app/oracle/oradata/ORCL/datafile/o1_mf_undotbs1_fqsh85md_.dbf
UNDOTBS1 100M
FILE_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME A.BYTES/1024/1024||'M'
------------------------------ -----------------------------------------
/home/u01/app/oracle/oradata/ORCL/datafile/o1_mf_users_fqsh86rs_.dbf
USERS 5M
/home/oradata/data01.dbf
DATA 30720M
Linux/windows扩容方式:
①添加数据文件
SQL> alter tablespace data add datafile '/home/oradata/data01.dbf ' size 2G autoextend on next 64k maxsize unlimited;
alter tablespace data add datafile '/home/oradata/data02.dbf ' size 1G autoextend on next 64k maxsize unlimited;
Tablespace altered.
②修改数据文件大小
SQL> alter database datafile '/home/oradata/data01.dbf' resize 4G;
浙公网安备 33010602011771号