{"id":2829,"date":"2025-02-06T14:26:33","date_gmt":"2025-02-06T14:26:33","guid":{"rendered":"https:\/\/w3buddy.com\/?p=2829"},"modified":"2026-01-15T13:20:40","modified_gmt":"2026-01-15T07:50:40","slug":"how-to-connect-to-oracle-database-using-sqlplus","status":"publish","type":"post","link":"https:\/\/w3buddy.com\/blog\/how-to-connect-to-oracle-database-using-sqlplus\/","title":{"rendered":"How to Connect to Oracle Database Using SQL*Plus"},"content":{"rendered":"\n

SQL*Plus is a command-line tool used to interact with Oracle databases. This guide covers all possible ways to connect using sqlplus<\/code>, along with examples.<\/p>\n\n\n\n

1. Connect as SYSDBA (Administrator Mode)<\/strong><\/h3>\n\n\n\n

If you have administrative privileges, you can connect as SYSDBA<\/code>. This is useful for managing the database.<\/p>\n\n\n\n

Command:<\/strong><\/h4>\n\n\n\n
sqlplus \/ as sysdba<\/code><\/pre>\n\n\n\n
Example Output:<\/strong><\/h4>\n\n\n\n
SQL*Plus: Release 19.0.0.0.0 - Production on Mon Feb 5 10:00:00 2025\nConnected to:\nOracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production<\/code><\/pre>\n\n\n\n
This method works only if SQL*Plus is run on the same machine as the database<\/strong>.<\/p>\n\n\n\n
2. Connect Using a Username and Password<\/strong><\/h3>\n\n\n\n
If you have a valid Oracle database user account, you can connect using your username and password.<\/p>\n\n\n\n
Command:<\/strong><\/h4>\n\n\n\n
sqlplus username\/password<\/code><\/pre>\n\n\n\n
Example:<\/strong><\/h4>\n\n\n\n
sqlplus scott\/tiger<\/code><\/pre>\n\n\n\n
    \n
  • Here, scott <\/strong>is the username, and tiger <\/strong>is the password.<\/li>\n\n\n\n
  • This works only on the local database<\/strong> unless configured for remote access.<\/li>\n<\/ul>\n\n\n\n
    3. Connect to a Remote Database Using TNS (Oracle Net Service Name)<\/strong><\/h3>\n\n\n\n
    If your Oracle database is running on another server, you can use a TNS alias<\/strong> (defined in tnsnames.ora<\/code>).<\/p>\n\n\n\n
    Command:<\/strong><\/h4>\n\n\n\n
    sqlplus username\/password@TNS_ALIAS<\/code><\/pre>\n\n\n\n
    Example:<\/strong><\/h4>\n\n\n\n
    sqlplus scott\/tiger@orcl<\/code><\/pre>\n\n\n\n
      \n
    • Here, orcl <\/strong>is the TNS alias configured in tnsnames.ora<\/strong> (usually found in $ORACLE_HOME\/network\/admin\/<\/strong>).<\/li>\n\n\n\n
    • Make sure the Oracle client is installed and tnsnames.ora<\/code> is properly configured.<\/li>\n<\/ul>\n\n\n\n
      4. Connect Using a Full Connection String<\/strong><\/h3>\n\n\n\n
      If you don\u2019t have a tnsnames.ora<\/strong> entry, you can connect using a fully qualified connection string.<\/p>\n\n\n\n
      Command:<\/strong><\/h4>\n\n\n\n
      sqlplus username\/password@\/\/hostname:port\/service_name<\/code><\/pre>\n\n\n\n
      Example:<\/strong><\/h4>\n\n\n\n
      sqlplus scott\/tiger@\/\/192.168.1.100:1521\/orclpdb<\/code><\/pre>\n\n\n\n
        \n
      • 192.168.1.100 \u2192 Oracle server IP\/hostname<\/strong><\/li>\n\n\n\n
      • 1521 \u2192 Oracle listener port<\/strong><\/li>\n\n\n\n
      • orclpdb \u2192 Oracle service name<\/strong> (or database name)<\/li>\n<\/ul>\n\n\n\n
        This method is useful when connecting over the network without configuring tnsnames.ora<\/strong>.<\/p>\n\n\n\n
        5. Connect Without Providing the Password (Secure Method)<\/strong><\/h3>\n\n\n\n
        If you don\u2019t want to type the password in the command (for security reasons), use the nolog<\/code> option and then manually enter credentials.<\/p>\n\n\n\n
        Command:<\/strong><\/h4>\n\n\n\n
        sqlplus \/nolog<\/code><\/pre>\n\n\n\n
        Then, at the SQL prompt, enter:<\/p>\n\n\n\n
        SQL> connect username@service_name\nEnter password:<\/code><\/pre>\n\n\n\n
        Example:<\/strong><\/h4>\n\n\n\n
        SQL> connect scott@orclpdb\nEnter password:<\/code><\/pre>\n\n\n\n
        This prevents the password from being exposed in the command history.<\/p>\n\n\n\n
        6. Connect Using an EZCONNECT (Easy Connect) String<\/strong><\/h3>\n\n\n\n
        The EZCONNECT method<\/strong> allows you to connect without configuring tnsnames.ora<\/code>.<\/p>\n\n\n\n
        Command:<\/strong><\/h4>\n\n\n\n
        sqlplus username\/password@hostname:port\/service_name<\/code><\/pre>\n\n\n\n
        Example:<\/strong><\/h4>\n\n\n\n
        sqlplus scott\/tiger@192.168.1.100:1521\/orclpdb<\/code><\/pre>\n\n\n\n
        To use EZCONNECT, ensure the following parameter is set in Oracle:<\/p>\n\n\n\n
        ALTER SYSTEM SET LOCAL_LISTENER='(ADDRESS=(PROTOCOL=TCP)(HOST=0.0.0.0)(PORT=1521))';<\/code><\/pre>\n\n\n\n
        This method simplifies remote connections.<\/p>\n\n\n\n
        7. Connect Using a Wallet (For Secure Passwordless Login)<\/strong><\/h3>\n\n\n\n
        Oracle wallets<\/strong> allow users to connect without entering a password.<\/p>\n\n\n\n
        Steps to Configure:<\/strong><\/h4>\n\n\n\n
          \n
        1. Create a secure wallet using Oracle tools:<\/li>\n<\/ol>\n\n\n\n
          mkstore -wrl $ORACLE_HOME\/network\/admin -create\nmkstore -wrl $ORACLE_HOME\/network\/admin -createCredential orclpdb scott tiger<\/code><\/pre>\n\n\n\n
            \n
          1. Use the wallet to connect:<\/li>\n<\/ol>\n\n\n\n
            sqlplus \/@orclpdb<\/code><\/pre>\n\n\n\n
            This is useful for automated scripts<\/strong> where you don\u2019t want to store passwords.<\/p>\n\n\n\n
            8. Connect Using SYSOPER (Alternative to SYSDBA)<\/strong><\/h3>\n\n\n\n
            SYSOPER<\/code> provides limited administrative privileges.<\/p>\n\n\n\n
            Command:<\/strong><\/h4>\n\n\n\n
            sqlplus \/ as sysoper<\/code><\/pre>\n\n\n\n
            This is similar to SYSDBA<\/code> but with fewer privileges<\/strong> (e.g., it cannot perform database recovery).<\/p>\n\n\n\n
            Conclusion<\/strong><\/h2>\n\n\n\n
            SQL*Plus offers multiple ways to connect to an Oracle database, whether you\u2019re working locally or remotely.<\/p>\n\n\n\n
            Summary of Connection Methods:<\/strong><\/h3>\n\n\n\n
            Method<\/th>Command Example<\/th><\/tr><\/thead>
            SYSDBA (Admin)<\/td>sqlplus \/ as sysdba<\/code><\/td><\/tr>
            Username & Password<\/td>sqlplus scott\/tiger<\/code><\/td><\/tr>
            Remote Connection (TNS)<\/td>sqlplus scott\/tiger@orcl<\/code><\/td><\/tr>
            Full Connection String<\/td>sqlplus scott\/tiger@\/\/192.168.1.100:1521\/orclpdb<\/code><\/td><\/tr>
            Secure Login (No Password in Command)<\/td>sqlplus \/nolog<\/code> \u2192 connect scott@orclpdb<\/code><\/td><\/tr>
            EZCONNECT<\/td>sqlplus scott\/tiger@192.168.1.100:1521\/orclpdb<\/code><\/td><\/tr>
            Wallet (Passwordless)<\/td>sqlplus \/@orclpdb<\/code><\/td><\/tr>
            SYSOPER<\/td>sqlplus \/ as sysoper<\/code><\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
            Each method is useful in different scenarios. If you’re working in a secure environment<\/strong>, consider using passwordless login (wallets) or secure login (\/nolog<\/code>)<\/strong>.<\/p>\n","protected":false},"excerpt":{"rendered":"
            SQL*Plus is a command-line tool used to interact with Oracle databases. This guide covers all possible ways to connect using sqlplus, along with examples. 1. Connect as SYSDBA (Administrator Mode) If you have administrative privileges, you can connect as SYSDBA. This is useful for managing the database. Command: Example Output: This method works only if […]<\/p>\n","protected":false},"author":1,"featured_media":2831,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"googlesitekit_rrm_CAowu461DA:productID":"","footnotes":""},"categories":[1225],"tags":[],"class_list":["post-2829","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-database"],"_links":{"self":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts\/2829","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/comments?post=2829"}],"version-history":[{"count":1,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts\/2829\/revisions"}],"predecessor-version":[{"id":2830,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts\/2829\/revisions\/2830"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/media\/2831"}],"wp:attachment":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/media?parent=2829"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/categories?post=2829"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/tags?post=2829"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}