参数修改成alter system set sec_case_sensitive_logon=FALSE scope=both sid=’*’;
ORA-01017: invalid username/password; logon denied
出现问题的过程及处理过程如下：
1.源端11g导出dmp文件，此时sec_case_sensitive_logon=FALSE
2.目标端19c建库，此时sec_case_sensitive_logon=TRUE
导入11g的dmp文件
3.用户要求19c环境的sec_case_sensitive_logon=FALSE，于是修改FALSE
启动会报参数废弃：ORA-32004
4.发现原用户密码无法连接
5.修改sqlnet.ora兼容
vi $ORACLE_HOME/network/admin/sqlnet.ora
SQLNET.ALLOWED_LOGON_VERSION_SERVER=8
SQLNET.ALLOWED_LOGON_VERSION_CLIENT=8
6.尝试用alter user identified by values ‘users$.spare4’;方式修改，发现还是无法登录（sec_case_sensitive_logon=true的情况下可以登录）。
生成修改密码脚本，并执行：
set pagesize 0
set line 180
set wrap on
set long 99999
select 'alter user '||name||' identified by values '''||SPARE4||''';' from sys.user$ where SPARE4 is not null and name not in('SYS','SYSTEM','OWBSYS_AUDIT','OWBSYS','APEX_PUBLIC_USER','APEX_030200','FLOWS_FILES','SPATIAL_CSW_ADMIN_USR','SPATIAL_WFS_ADMIN_USR','MDDATA','OLAPSYS','ORDDATA','ORDPLUGINS','SI_INFORMTN_SCHEMA','MDSYS','ORDSYS','XDB','ANONYMOUS','CTXSYS','EXFSYS','WMSYS','APPQOSSYS','DBSNMP','ORACLE_OCM','DIP','OUTLN','SYSMAN');
根据结果执行上述生成的脚本，修改密码，依然无法登录。
7.用alter user identified by ‘xxx’;这种方式修改密码后可以连接，但由于用户不记得原密码，所以该方法不可行。
最终处理办法：
1.已经建库、参数sec_case_sensitive_logon=FALSE并且sqlnet.ora的兼容已经修改完成。
2.删除用户drop user cascade;
3.重新导入原先11g的dmp文件
4.可连接
测试过程：
11g环境：
[oracle@11g ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Tue Dec 26 09:47:34 2023
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 1603411968 bytes
Fixed Size 2213776 bytes
Variable Size 436209776 bytes
Database Buffers 1157627904 bytes
Redo Buffers 7360512 bytes
Database mounted.
Database opened.
SQL> conn j1/j1
Connected.
SQL> conn / as sysdba
Connected.
SQL> show parameter case;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sec_case_sensitive_logon boolean TRUE
SQL> alter system set sec_case_sensitive_logon=false scope=both sid='*';
System altered.
SQL> show parameter case;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sec_case_sensitive_logon boolean FALSE
SQL> conn j1/j1
Connected.
[oracle@11g ~]$ expdp \'/ as sysdba\' directory=acfsdmp dumpfile=meta2.dmp LOGFILE=meta2.log SCHEMAS=J1 PARALLEL=1 CLUSTER=N EXCLUDE=STATISTICS
Export: Release 11.2.0.1.0 - Production on Tue Dec 26 09:50:00 2023
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_SCHEMA_01": "/******** AS SYSDBA" directory=acfsdmp dumpfile=meta2.dmp LOGFILE=meta2.log SCHEMAS=J1 PARALLEL=1 CLUSTER=N EXCLUDE=STATISTICS
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 55.06 MB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SYNONYM/SYNONYM
Processing object type SCHEMA_EXPORT/DB_LINK
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
. . exported "J1"."J001" 6.961 MB 72012 rows
. . exported "J1"."J0011" 6.961 MB 72014 rows
. . exported "J1"."J0022" 6.961 MB 72018 rows
. . exported "J1"."J01" 6.960 MB 72006 rows
. . exported "J1"."J22" 6.961 MB 72011 rows
. . exported "J1"."JJ0" 6.961 MB 72017 rows
. . exported "J1"."BIG_J2" 11.99 KB 17 rows
. . exported "J1"."J1" 10.32 KB 1 rows
. . exported "J1"."J11" 10.32 KB 1 rows
. . exported "J1"."J12" 10.32 KB 1 rows
. . exported "J1"."J123" 10.32 KB 1 rows
. . exported "J1"."J17" 5.015 KB 1 rows
. . exported "J1"."J37" 5.015 KB 1 rows
. . exported "J1"."JJJJ" 5.421 KB 1 rows
. . exported "J1"."T0" 5.421 KB 1 rows
. . exported "J1"."T01" 10.32 KB 1 rows
. . exported "J1"."T02" 10.32 KB 1 rows
. . exported "J1"."T03" 5.421 KB 1 rows
. . exported "J1"."T2" 5.421 KB 1 rows
. . exported "J1"."T3" 10.32 KB 1 rows
. . exported "J1"."T4" 10.79 KB 7 rows
. . exported "J1"."T5" 10.32 KB 1 rows
. . exported "J1"."T6" 5.421 KB 1 rows
. . exported "J1"."BIG_J1" 0 KB 0 rows
Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
/bak/meta2.dmp
Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at 09:50:19
19c环境：
[oracle@rac1 admin]$ sqlplus jyc/jyc@192.168.52.185:1521/dbcenter
SQL*Plus: Release 19.0.0.0.0 - Production on Tue Dec 26 09:37:10 2023
Version 19.8.0.0.0
Copyright (c) 1982, 2020, Oracle. All rights reserved.
ERROR:
ORA-01017: invalid username/password; logon denied
Enter user-name: ^C
[oracle@rac1 admin]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Tue Dec 26 09:37:34 2023
Version 19.8.0.0.0
Copyright (c) 1982, 2020, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.8.0.0.0
SQL> drop user jyc cascade;
User dropped.
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.8.0.0.0
[oracle@rac1 admin]$ impdp \'/ as sysdba\' directory=dump dumpfile=meta1.dmp LOGFILE=i-meta1.log SCHEMAS=JYC PARALLEL=1 CLUSTER=N
Import: Release 19.0.0.0.0 - Production on Tue Dec 26 09:41:24 2023
Version 19.8.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Master table "SYS"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_SCHEMA_01": "/******** AS SYSDBA" directory=dump dumpfile=meta1.dmp LOGFILE=i-meta1.log SCHEMAS=JYC PARALLEL=1 CLUSTER=N
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "JYC"."J" 9.563 MB 72497 rows
Job "SYS"."SYS_IMPORT_SCHEMA_01" successfully completed at Tue Dec 26 09:41:27 2023 elapsed 0 00:00:03
[oracle@rac1 admin]$ sqlplus jyc/jyc@192.168.52.185:1521/dbcenter
SQL*Plus: Release 19.0.0.0.0 - Production on Tue Dec 26 09:41:37 2023
Version 19.8.0.0.0
Copyright (c) 1982, 2020, Oracle. All rights reserved.
ERROR:
ORA-01017: invalid username/password; logon denied
Enter user-name: ^C
[oracle@rac1 admin]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Tue Dec 26 09:41:53 2023
Version 19.8.0.0.0
Copyright (c) 1982, 2020, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.8.0.0.0
SQL> alter user jyc identified by jyc;
User altered.
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.8.0.0.0
^[[A[oracle@rac1 admin]$ sqlplus jyc/jyc@192.168.52.185:1521/dbcenter
SQL*Plus: Release 19.0.0.0.0 - Production on Tue Dec 26 09:42:02 2023
Version 19.8.0.0.0
Copyright (c) 1982, 2020, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.8.0.0.0
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.8.0.0.0
[oracle@rac1 admin]$ expdp \'/ as sysdba\' directory=dump dumpfile=meta2.dmp LOGFILE=meta2.log SCHEMAS=JYC PARALLEL=1 CLUSTER=N EXCLUDE=STATISTICS
Export: Release 19.0.0.0.0 - Production on Tue Dec 26 09:42:46 2023
Version 19.8.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Starting "SYS"."SYS_EXPORT_SCHEMA_01": "/******** AS SYSDBA" directory=dump dumpfile=meta2.dmp LOGFILE=meta2.log SCHEMAS=JYC PARALLEL=1 CLUSTER=N EXCLUDE=STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
. . exported "JYC"."J" 9.563 MB 72497 rows
Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
/oracle/app/oracle/dmp/meta2.dmp
Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at Tue Dec 26 09:43:07 2023 elapsed 0 00:00:20
[oracle@rac1 admin]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Tue Dec 26 09:43:11 2023
Version 19.8.0.0.0
Copyright (c) 1982, 2020, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.8.0.0.0
SQL> drop user jyc cascade;
User dropped.
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.8.0.0.0
[oracle@rac1 admin]$ impdp \'/ as sysdba\' directory=dump dumpfile=meta2.dmp LOGFILE=i-meta2.log SCHEMAS=JYC PARALLEL=1 CLUSTER=N
Import: Release 19.0.0.0.0 - Production on Tue Dec 26 09:43:31 2023
Version 19.8.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Master table "SYS"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_SCHEMA_01": "/******** AS SYSDBA" directory=dump dumpfile=meta2.dmp LOGFILE=i-meta2.log SCHEMAS=JYC PARALLEL=1 CLUSTER=N
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "JYC"."J" 9.563 MB 72497 rows
Job "SYS"."SYS_IMPORT_SCHEMA_01" successfully completed at Tue Dec 26 09:43:36 2023 elapsed 0 00:00:03
[oracle@rac1 admin]$ sqlplus jyc/jyc@192.168.52.185:1521/dbcenter
SQL*Plus: Release 19.0.0.0.0 - Production on Tue Dec 26 09:43:42 2023
Version 19.8.0.0.0
Copyright (c) 1982, 2020, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.8.0.0.0
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.8.0.0.0
[oracle@rac1 admin]$ mv sqlnet.ora sqlnet.ora11
[oracle@rac1 admin]$ cp sqlnet.orabak sqlnet.ora
[oracle@rac1 admin]$ cat sqlnet.ora
SQLNET.ALLOWED_LOGON_VERSION_SERVER=8
SQLNET.ALLOWED_LOGON_VERSION_CLIENT=8
[oracle@rac1 admin]$ sqlplus jyc/jyc@192.168.52.185:1521/dbcenter
SQL*Plus: Release 19.0.0.0.0 - Production on Tue Dec 26 09:44:20 2023
Version 19.8.0.0.0
Copyright (c) 1982, 2020, Oracle. All rights reserved.
Last Successful login time: Tue Dec 26 2023 09:43:42 +08:00
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.8.0.0.0
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.8.0.0.0
[oracle@rac1 admin]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Tue Dec 26 09:45:06 2023
Version 19.8.0.0.0
Copyright (c) 1982, 2020, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.8.0.0.0
SQL> show parameter case;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sec_case_sensitive_logon boolean FALSE
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.8.0.0.0
[oracle@rac1 admin]$ cd /oracle/app/ora
oracle/ oraInventory/
[oracle@rac1 admin]$ cd /oracle/app/oracle/dmp/
[oracle@rac1 dmp]$ scp 192.168.52.172:/bak/meta2.dmp ./
The authenticity of host '192.168.52.172 (192.168.52.172)' can't be established.
ECDSA key fingerprint is SHA256:1SjKKOnjsejVyySF2t3hRliryd1+19Dff0wtofsSRS4.
ECDSA key fingerprint is MD5:a1:3c:c6:8d:56:fe:3b:56:cf:e7:fe:f1:85:37:76:95.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.52.172' (ECDSA) to the list of known hosts.
oracle@192.168.52.172's password:
meta2.dmp 100% 42MB 120.2MB/s 00:00
[oracle@rac1 dmp]$ ls -lt|head
total 53924
-rw-r----- 1 oracle asmadmin 44490752 Dec 26 09:51 meta2.dmp
-rw-r--r-- 1 oracle asmadmin 1020 Dec 26 09:43 i-meta2.log
-rw-r--r-- 1 oracle asmadmin 1198 Dec 26 09:43 meta2.log
-rw-r--r-- 1 oracle asmadmin 1020 Dec 26 09:41 i-meta1.log
-rw-r--r-- 1 oracle asmadmin 1198 Dec 26 09:27 meta1.log
-rw-r----- 1 oracle asmadmin 10391552 Dec 26 09:27 meta1.dmp
-rw-r--r-- 1 oracle asmadmin 1056 Dec 26 09:26 meta.log
-rw-r----- 1 oracle asmadmin 315392 Dec 26 09:26 meta.dmp
[oracle@rac1 dmp]$ impdp \'/ as sysdba\' directory=dump dumpfile=meta2.dmp LOGFILE=i-meta2-1.log SCHEMAS=J1 PARALLEL=1 CLUSTER=N
Import: Release 19.0.0.0.0 - Production on Tue Dec 26 09:51:53 2023
Version 19.8.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Master table "SYS"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_SCHEMA_01": "/******** AS SYSDBA" directory=dump dumpfile=meta2.dmp LOGFILE=i-meta2-1.log SCHEMAS=J1 PARALLEL=1 CLUSTER=N
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"J1" already exists
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SYNONYM/SYNONYM
Processing object type SCHEMA_EXPORT/DB_LINK
Processing object type SCHEMA_EXPORT/TABLE/TABLE
ORA-39083: Object type TABLE:"J1"."BIG_J1" failed to create with error:
ORA-00959: tablespace 'JYC_BIG' does not exist
Failing sql is:
CREATE TABLE "J1"."BIG_J1" ("OWNER" VARCHAR2(30 BYTE), "OBJECT_NAME" VARCHAR2(128 BYTE), "SUBOBJECT_NAME" VARCHAR2(30 BYTE), "OBJECT_ID" NUMBER, "DATA_OBJECT_ID" NUMBER, "OBJECT_TYPE" VARCHAR2(19 BYTE), "CREATED" DATE, "LAST_DDL_TIME" DATE, "TIMESTAMP" VARCHAR2(19 BYTE), "STATUS" VARCHAR2(7 BYTE), "TEMPORARY" VARCHAR2(1 BYTE), "GENERATED" VARCHAR2(1 BYTE), "SECONDARY" VARCHAR2(1 BYTE), "NAMESPACE" NUMBER, "EDITION_NAME" VARCHAR2(30 BYTE)) SEGMENT CREATION DEFERRED PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING TABLESPACE "JYC_BIG"
ORA-39083: Object type TABLE:"J1"."BIG_J2" failed to create with error:
ORA-00959: tablespace 'JYC_BIG' does not exist
Failing sql is:
CREATE TABLE "J1"."BIG_J2" ("OWNER" VARCHAR2(30 BYTE), "OBJECT_NAME" VARCHAR2(128 BYTE), "SUBOBJECT_NAME" VARCHAR2(30 BYTE), "OBJECT_ID" NUMBER, "DATA_OBJECT_ID" NUMBER, "OBJECT_TYPE" VARCHAR2(19 BYTE), "CREATED" DATE, "LAST_DDL_TIME" DATE, "TIMESTAMP" VARCHAR2(19 BYTE), "STATUS" VARCHAR2(7 BYTE), "TEMPORARY" VARCHAR2(1 BYTE), "GENERATED" VARCHAR2(1 BYTE), "SECONDARY" VARCHAR2(1 BYTE), "NAMESPACE" NUMBER, "EDITION_NAME" VARCHAR2(30 BYTE)) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "JYC_BIG"
ORA-39083: Object type TABLE:"J1"."JJJJ" failed to create with error:
ORA-00959: tablespace 'JYC_BIG' does not exist
Failing sql is:
CREATE TABLE "J1"."JJJJ" ("ID" NUMBER(*,0), "NAME" VARCHAR2(20 BYTE)) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "JYC_BIG"
ORA-39083: Object type TABLE:"J1"."J0011" failed to create with error:
ORA-00959: tablespace 'JYC_BIG' does not exist
Failing sql is:
CREATE TABLE "J1"."J0011" ("OWNER" VARCHAR2(30 BYTE), "OBJECT_NAME" VARCHAR2(128 BYTE), "SUBOBJECT_NAME" VARCHAR2(30 BYTE), "OBJECT_ID" NUMBER, "DATA_OBJECT_ID" NUMBER, "OBJECT_TYPE" VARCHAR2(19 BYTE), "CREATED" DATE, "LAST_DDL_TIME" DATE, "TIMESTAMP" VARCHAR2(19 BYTE), "STATUS" VARCHAR2(7 BYTE), "TEMPORARY" VARCHAR2(1 BYTE), "GENERATED" VARCHAR2(1 BYTE), "SECONDARY" VARCHAR2(1 BYTE), "NAMESPACE" NUMBER, "EDITION_NAME" VARCHAR2(30 BYTE)) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "JYC_BIG"
ORA-39083: Object type TABLE:"J1"."J0022" failed to create with error:
ORA-00959: tablespace 'JYC_BIG' does not exist
Failing sql is:
CREATE TABLE "J1"."J0022" ("OWNER" VARCHAR2(30 BYTE), "OBJECT_NAME" VARCHAR2(128 BYTE), "SUBOBJECT_NAME" VARCHAR2(30 BYTE), "OBJECT_ID" NUMBER, "DATA_OBJECT_ID" NUMBER, "OBJECT_TYPE" VARCHAR2(19 BYTE), "CREATED" DATE, "LAST_DDL_TIME" DATE, "TIMESTAMP" VARCHAR2(19 BYTE), "STATUS" VARCHAR2(7 BYTE), "TEMPORARY" VARCHAR2(1 BYTE), "GENERATED" VARCHAR2(1 BYTE), "SECONDARY" VARCHAR2(1 BYTE), "NAMESPACE" NUMBER, "EDITION_NAME" VARCHAR2(30 BYTE)) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "JYC_BIG"
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "J1"."J001" 6.961 MB 72012 rows
. . imported "J1"."J01" 6.960 MB 72006 rows
. . imported "J1"."J22" 6.961 MB 72011 rows
. . imported "J1"."JJ0" 6.961 MB 72017 rows
. . imported "J1"."J1" 10.32 KB 1 rows
. . imported "J1"."J11" 10.32 KB 1 rows
. . imported "J1"."J12" 10.32 KB 1 rows
. . imported "J1"."J123" 10.32 KB 1 rows
. . imported "J1"."J17" 5.015 KB 1 rows
. . imported "J1"."J37" 5.015 KB 1 rows
. . imported "J1"."T0" 5.421 KB 1 rows
. . imported "J1"."T01" 10.32 KB 1 rows
. . imported "J1"."T02" 10.32 KB 1 rows
. . imported "J1"."T03" 5.421 KB 1 rows
. . imported "J1"."T2" 5.421 KB 1 rows
. . imported "J1"."T3" 10.32 KB 1 rows
. . imported "J1"."T4" 10.79 KB 7 rows
. . imported "J1"."T5" 10.32 KB 1 rows
. . imported "J1"."T6" 5.421 KB 1 rows
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Job "SYS"."SYS_IMPORT_SCHEMA_01" completed with 6 error(s) at Tue Dec 26 09:52:00 2023 elapsed 0 00:00:06
[oracle@rac1 dmp]$ sqlplus j1/j1@192.168.52.185:1521/dbcenter
SQL*Plus: Release 19.0.0.0.0 - Production on Tue Dec 26 09:52:34 2023
Version 19.8.0.0.0
Copyright (c) 1982, 2020, Oracle. All rights reserved.
ERROR:
ORA-01017: invalid username/password; logon denied
Enter user-name: ^C
[oracle@rac1 dmp]$
[oracle@rac1 dmp]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Tue Dec 26 09:52:42 2023
Version 19.8.0.0.0
Copyright (c) 1982, 2020, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.8.0.0.0
SQL> drop user j1 cascade;
User dropped.
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.8.0.0.0
[oracle@rac1 dmp]$ impdp \'/ as sysdba\' directory=dump dumpfile=meta2.dmp LOGFILE=i-meta2-1.log SCHEMAS=J1 PARALLEL=1 CLUSTER=N
Import: Release 19.0.0.0.0 - Production on Tue Dec 26 09:52:57 2023
Version 19.8.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Master table "SYS"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_SCHEMA_01": "/******** AS SYSDBA" directory=dump dumpfile=meta2.dmp LOGFILE=i-meta2-1.log SCHEMAS=J1 PARALLEL=1 CLUSTER=N
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SYNONYM/SYNONYM
Processing object type SCHEMA_EXPORT/DB_LINK
Processing object type SCHEMA_EXPORT/TABLE/TABLE
ORA-39083: Object type TABLE:"J1"."BIG_J1" failed to create with error:
ORA-00959: tablespace 'JYC_BIG' does not exist
Failing sql is:
CREATE TABLE "J1"."BIG_J1" ("OWNER" VARCHAR2(30 BYTE), "OBJECT_NAME" VARCHAR2(128 BYTE), "SUBOBJECT_NAME" VARCHAR2(30 BYTE), "OBJECT_ID" NUMBER, "DATA_OBJECT_ID" NUMBER, "OBJECT_TYPE" VARCHAR2(19 BYTE), "CREATED" DATE, "LAST_DDL_TIME" DATE, "TIMESTAMP" VARCHAR2(19 BYTE), "STATUS" VARCHAR2(7 BYTE), "TEMPORARY" VARCHAR2(1 BYTE), "GENERATED" VARCHAR2(1 BYTE), "SECONDARY" VARCHAR2(1 BYTE), "NAMESPACE" NUMBER, "EDITION_NAME" VARCHAR2(30 BYTE)) SEGMENT CREATION DEFERRED PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING TABLESPACE "JYC_BIG"
ORA-39083: Object type TABLE:"J1"."BIG_J2" failed to create with error:
ORA-00959: tablespace 'JYC_BIG' does not exist
Failing sql is:
CREATE TABLE "J1"."BIG_J2" ("OWNER" VARCHAR2(30 BYTE), "OBJECT_NAME" VARCHAR2(128 BYTE), "SUBOBJECT_NAME" VARCHAR2(30 BYTE), "OBJECT_ID" NUMBER, "DATA_OBJECT_ID" NUMBER, "OBJECT_TYPE" VARCHAR2(19 BYTE), "CREATED" DATE, "LAST_DDL_TIME" DATE, "TIMESTAMP" VARCHAR2(19 BYTE), "STATUS" VARCHAR2(7 BYTE), "TEMPORARY" VARCHAR2(1 BYTE), "GENERATED" VARCHAR2(1 BYTE), "SECONDARY" VARCHAR2(1 BYTE), "NAMESPACE" NUMBER, "EDITION_NAME" VARCHAR2(30 BYTE)) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "JYC_BIG"
ORA-39083: Object type TABLE:"J1"."JJJJ" failed to create with error:
ORA-00959: tablespace 'JYC_BIG' does not exist
Failing sql is:
CREATE TABLE "J1"."JJJJ" ("ID" NUMBER(*,0), "NAME" VARCHAR2(20 BYTE)) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "JYC_BIG"
ORA-39083: Object type TABLE:"J1"."J0011" failed to create with error:
ORA-00959: tablespace 'JYC_BIG' does not exist
Failing sql is:
CREATE TABLE "J1"."J0011" ("OWNER" VARCHAR2(30 BYTE), "OBJECT_NAME" VARCHAR2(128 BYTE), "SUBOBJECT_NAME" VARCHAR2(30 BYTE), "OBJECT_ID" NUMBER, "DATA_OBJECT_ID" NUMBER, "OBJECT_TYPE" VARCHAR2(19 BYTE), "CREATED" DATE, "LAST_DDL_TIME" DATE, "TIMESTAMP" VARCHAR2(19 BYTE), "STATUS" VARCHAR2(7 BYTE), "TEMPORARY" VARCHAR2(1 BYTE), "GENERATED" VARCHAR2(1 BYTE), "SECONDARY" VARCHAR2(1 BYTE), "NAMESPACE" NUMBER, "EDITION_NAME" VARCHAR2(30 BYTE)) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "JYC_BIG"
ORA-39083: Object type TABLE:"J1"."J0022" failed to create with error:
ORA-00959: tablespace 'JYC_BIG' does not exist
Failing sql is:
CREATE TABLE "J1"."J0022" ("OWNER" VARCHAR2(30 BYTE), "OBJECT_NAME" VARCHAR2(128 BYTE), "SUBOBJECT_NAME" VARCHAR2(30 BYTE), "OBJECT_ID" NUMBER, "DATA_OBJECT_ID" NUMBER, "OBJECT_TYPE" VARCHAR2(19 BYTE), "CREATED" DATE, "LAST_DDL_TIME" DATE, "TIMESTAMP" VARCHAR2(19 BYTE), "STATUS" VARCHAR2(7 BYTE), "TEMPORARY" VARCHAR2(1 BYTE), "GENERATED" VARCHAR2(1 BYTE), "SECONDARY" VARCHAR2(1 BYTE), "NAMESPACE" NUMBER, "EDITION_NAME" VARCHAR2(30 BYTE)) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "JYC_BIG"
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "J1"."J001" 6.961 MB 72012 rows
. . imported "J1"."J01" 6.960 MB 72006 rows
. . imported "J1"."J22" 6.961 MB 72011 rows
. . imported "J1"."JJ0" 6.961 MB 72017 rows
. . imported "J1"."J1" 10.32 KB 1 rows
. . imported "J1"."J11" 10.32 KB 1 rows
. . imported "J1"."J12" 10.32 KB 1 rows
. . imported "J1"."J123" 10.32 KB 1 rows
. . imported "J1"."J17" 5.015 KB 1 rows
. . imported "J1"."J37" 5.015 KB 1 rows
. . imported "J1"."T0" 5.421 KB 1 rows
. . imported "J1"."T01" 10.32 KB 1 rows
. . imported "J1"."T02" 10.32 KB 1 rows
. . imported "J1"."T03" 5.421 KB 1 rows
. . imported "J1"."T2" 5.421 KB 1 rows
. . imported "J1"."T3" 10.32 KB 1 rows
. . imported "J1"."T4" 10.79 KB 7 rows
. . imported "J1"."T5" 10.32 KB 1 rows
. . imported "J1"."T6" 5.421 KB 1 rows
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Job "SYS"."SYS_IMPORT_SCHEMA_01" completed with 5 error(s) at Tue Dec 26 09:53:02 2023 elapsed 0 00:00:04
[oracle@rac1 dmp]$ sqlplus j1/j1@192.168.52.185:1521/dbcenter
SQL*Plus: Release 19.0.0.0.0 - Production on Tue Dec 26 09:53:06 2023
Version 19.8.0.0.0
Copyright (c) 1982, 2020, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.8.0.0.0
SQL> conn / as sysdba
Connected.
SQL> show parameter case;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sec_case_sensitive_logon boolean FALSE
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.8.0.0.0
[oracle@rac1 dmp]$ sqlplus j1/J1@192.168.52.185:1521/dbcenter
SQL*Plus: Release 19.0.0.0.0 - Production on Tue Dec 26 09:53:34 2023
Version 19.8.0.0.0
Copyright (c) 1982, 2020, Oracle. All rights reserved.
Last Successful login time: Tue Dec 26 2023 09:53:06 +08:00
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.8.0.0.0
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.8.0.0.0
[oracle@rac1 dmp]$ sqlplus jyc/JYC@192.168.52.185:1521/dbcenter
SQL*Plus: Release 19.0.0.0.0 - Production on Tue Dec 26 09:53:46 2023
Version 19.8.0.0.0
Copyright (c) 1982, 2020, Oracle. All rights reserved.
Last Successful login time: Tue Dec 26 2023 09:44:20 +08:00
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.8.0.0.0
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.8.0.0.0
[oracle@rac1 dmp]$ exit
最后修改时间：2023-12-26 10:46:05
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
