SQL*Plus中HR账户登录异常及SELECT语句执行失败求助

Hey there! Let's break down and fix those two Oracle XE issues you're hitting while working through Stefan Heitsiek's Oracle Express Edition book—they're super common when mixing SQL*Plus and APEX with the HR schema, so you're not alone!

Issue 1: ORA-28000: the account is locked (even though APEX shows it unlocked) #

What's going on? #

The HR database user is locked by default in Oracle XE 11.2. The "Manage Users and Groups" section in APEX is for managing APEX workspace users—these are separate from the underlying database-level HR user. That's why you're seeing conflicting lock statuses.

Fix steps: #

1. Fire up SQL*Plus and log in as SYSDBA:

   sqlplus / as sysdba
   

2. Unlock the HR database user with this command:

   ALTER USER hr ACCOUNT UNLOCK;
   

3. (Strongly recommended) Reset the HR user's password—default passwords often expire or aren't configured properly for direct SQL*Plus access:

   ALTER USER hr IDENTIFIED BY your_preferred_password;
   

4. Now try connecting again, and it should work:

   CONNECT hr/your_preferred_password
   

Issue 2: ORA-00942: table or view does not exist (when querying as SYSDBA) #

What's going on? #

When you log in as SYSDBA, your default schema is SYS, not HR. The regions table lives in the HR schema, so you can't access it directly without specifying which schema it's in. In APEX's SQL Workshop, you're working within the HR workspace, which automatically sets your default schema to HR—so the query works there without extra steps.

Fix options: #

You've got two simple ways to fix this:

• Option 1: Add the schema prefix to your query
  Just explicitly tell Oracle which schema the table belongs to:

  SELECT * FROM hr.regions;
  

• Option 2: Switch your session to the HR schema
  If you want to run multiple queries without typing the prefix every time, switch your current session's default schema:

  ALTER SESSION SET CURRENT_SCHEMA = hr;
  -- Now you can query directly
  SELECT * FROM regions;
  

That should get you back on track with your Oracle learning! Let me know if you run into any other small hurdles as you work through the book.

内容的提问来源于stack exchange，提问作者noobie