Error when run Oracle database sample schema scrip

2019-08-23 19:00发布

问题:

(I asked this question: SP2-0606: Cannot create SPOOL file , but I still stucked.)

I am using Oracle 12c Release 2 on Windows 10 pro x64. Oracle installed at folder E:\app\summer\. This is my tnsnames.ora

# tnsnames.ora Network Configuration File: E:\app\summer\product\12.1.0\dbhome_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.

ORACLR_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
    (CONNECT_DATA =
      (SID = CLRExtProc)
      (PRESENTATION = RO)
    )
  )

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = Desktop42.example.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl.example.com)
    )
  )

I checkout Oracle database sample schema script:

git clone https://github.com/oracle/db-sample-schemas.git
git checkout v12.1.0.2


cd /d E:\github.com\oracle\db-sample-schemas
sqlplus sys/summer as sysdba
@mksample "summer" "summer" "summer" "summer" "summer" "summer" "summer" "summer" users temp "E:\vy\" "localhost:1521/orcl"

Error:

E:\app\summer\product\12.1.0\dbhome_1\demo\schema>cd /d E:\github.com\oracle\db-sample-schemas

E:\github.com\oracle\db-sample-schemas>sqlplus sys/summer as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Wed Jun 28 15:52:19 2017

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> @mksample "summer" "summer" "summer" "summer" "summer" "summer" "summer" "summer" users temp "E:\vy\" "localhost:1521/orcl"

specify password for SYSTEM as parameter 1:

specify password for SYS as parameter 2:

specify password for HR as parameter 3:

specify password for OE as parameter 4:

specify password for PM as parameter 5:

specify password for IX as parameter 6:

specify password for  SH as parameter 7:

specify password for  BI as parameter 8:

specify default tablespace as parameter 9:

specify temporary tablespace as parameter 10:

specify log file directory (including trailing delimiter) as parameter 11:

specify connect string as parameter 12:

Sample Schemas are being created ...

A subdirectory or file E:\vy\ already exists.

ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor


Warning: You are no longer connected to ORACLE.
SP2-0640: Not connected
SP2-0640: Not connected
SP2-0640: Not connected
SP2-0640: Not connected
SP2-0640: Not connected
SP2-0640: Not connected
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor


SP2-0310: unable to open file "__SUB__CWD__/human_resources/hr_main.sql"
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor


SP2-0310: unable to open file "__SUB__CWD__/order_entry/oe_main.sql"
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor


SP2-0310: unable to open file "__SUB__CWD__/product_media/pm_main.sql"
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor


SP2-0310: unable to open file "__SUB__CWD__/info_exchange/ix_main.sql"
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor


SP2-0310: unable to open file "__SUB__CWD__/sales_history/sh_main.sql"
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor


SP2-0310: unable to open file "__SUB__CWD__/bus_intelligence/bi_main.sql"
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor


not spooling currently
SP2-0310: unable to open file "__SUB__CWD__/mkverify.sql"
SQL>

My Question:

(1) What is reason and how to fix error: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

(2) This guide for Linux operating system. https://github.com/oracle/db-sample-schemas#23-change-all-embedded-paths-to-match-your-working-directory I am using Windows OS, how to revise the mismatch problem in this case? I think after revising it, error SP2-0310: unable to open file "__SUB__CWD__/mkverify.sql" will be removed.

(3) If you see any other mistake in my installing (Oracle sample database schema), please show me.

回答1:

Regarding your second question, i am not a big expert in oracle but it seems the script couldn't replace the __SUB__CWD__ with the current dir. Anyway i found a site that mentioned to do this replace manually in the scripts, run the following from your main sample folder (for me that was db-sample-schemas-12.2.0.1), after that it worked for me on linux didn't try on windows but you can also use notepad++ and find in files feature to do the replace.

perl -p -i.bak -e 's#__SUB__CWD__#'$(pwd)'#g' *.sql */*.sql */*.dat