remote dba support
More
    HomeOracle DBA TipsSome detail about login.sql

    Some detail about login.sql

    Article explains about login.sql and banner.sql of Oracle 18c database for information usages.

    I downloaded the newest version of the Oracle “instant client”, version 18.3 for windows. I first wanted to ascertain how dynamic line length management worked as described here.

    In fact, I even have not been ready to obtain great results, within the sense that with tables with different columns I don’t resize the columns to form them fit on a row, it just enlarges the space available on the window. But what I got stuck on before investigating further on the layout of the results is that the incontrovertible fact that sqlplus doesn’t load me the initial configuration with the login.sql file.

    - Advertisement -
    dbametrix

    My memory may be a little unpracticed and only after a short time did I remember that it had been a subject I had already addressed; actually, a while has passed. ranging from the top of that post I discovered that there’s a patch (25804573) that ought to correct the very fact that sqlplus on windows (they mistakenly distribute the patch for all platforms but it seems to only concern windows as reported during this comment) doesn’t take into considering the SQLPATH environment variable. I attempted the patch (it is that the sqlplus.exe executable, so I used to be ready to use it with the moment client by replacing the file by hand) and apparently I didn’t see any differences, within the sense that it doesn’t launch my login anyway. SQL. The difference is that with the patch, calling the login.sql file specifying the extension works:

    SQL> @login.sql
    PL / SQL procedure successfully completed.
    Session altered.

    Now, on the instant client sqlplus 18.3 the behavior has changed slightly:

    SQL * Plus: Release 18.0.0.0.0 - Production on Thu Jun 25 09:30:44 2020
    Version 18.3.0.0.0
    Copyright (c) 1982, 2018, Oracle. All rights reserved.

    - Advertisement -
    dbametrix

    Last Successful login time: Thu Jun 25 2020 09:27:54 +02: 00

    Connected to:

    Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 – Production
    Version 18.3.0.0.0

    SQL> @login.sql

    SP2-0310: unable to open file ” c: \users\dbametrix\oracle\conf\login.sql ”

    SQL> @c:\users\dbametrix\oracle\conf\login.sql

    PL / SQL procedure successfully completed.

    Session altered.

    I must say that here I was a bit confused and to be sure I did the test over and over again because I thought I had put the character ♂ making confusion with the keyboard and it seemed to me that calling the command with the “history, that is scrolling with the arrows on the keyboard, deleting the “l” and retyping it worked but then “cleaning” the environment and retesting the test I finally had a repeatable behavior which is the one above. The SQLPATH environment variable is set to C: \ ORACLE \ conf and the other scripts present in that path are found without problems:

    SQL> @banner

    BANNER_FULL
    —————————
    Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 – Production
    Version 18.3.0.0.0

    SQL> @banner.sql

    BANNER_FULL
    ————————–
    Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 – Production
    Version 18.3.0.0.0

    So there is a restriction only on login.sql.

    An evolution I found on 18.3 is that if SQLPATH points to a path below the one where the SQLPLUS executable is, the automatic loading of login.sql works:

    C:\ORACLE\sqlplus> set SQLPATH = c:\users\dbametrix\oracle\conf

    C:\ORACLE\sqlplus> sqlplus testuser/testuser@ testserver.test.com

    SQL * Plus: Release 18.0.0.0.0 – Production on Thu Jun 25 10:02:28 2020
    Version 18.3.0.0.0
    Copyright (c) 1982, 2018, Oracle. All rights reserved.
    Connected to:
    Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 – Production
    Version 18.3.0.0.0
    Session altered.
    PL / SQL procedure successfully completed.
    Session altered.

    [email protected]> @banner
    SP2-0310: unable to open file “banner.sql”

    I honestly found no mention of this restriction. This messes up my configuration a little bit with multiple client versions and the single script repository; to workaround, I have to copy or rather move all the scripts under the installation directory of the oracle client 18.3 and switch to the exclusive use of that version.

    I did a quick test on the Linux server where I installed Oracle 18c, setting, for example, ORACLE_PATH =/home/oracle and stuffing the login.sql file into it and it loads without problems.

    When you want to make a strong Oracle DBA career then you should be aware of database services and other database technology. Without having knowledge of Oracle internals, Oracle performance tuning, and skill of Oracle database troubleshooting you can’t be an Oracle DBA expert. This expert DBA Team club blog always provides you latest technology news and database news to keep yourself up to date. You should need to be aware of Cloud database technology like DBaaS. All Oracle DBA tips are available in a single unique resource at our orageek. Meanwhile, we are also providing some sql tutorials for Oracle DBA. This is the part of Dbametrix Group and you would enjoy more advanced topics from our partner resource.

    - Advertisement -
    dbametrix

    1 COMMENT

    1. using login.sql we can hack database and protect or restrict database too. This is the important of login.sql file.

    Comments are closed.

    - Advertisment -
    remote dba services

    Most Popular