Forum Stats

  • 3,734,169 Users
  • 2,246,900 Discussions
  • 7,857,146 Comments

Discussions

SQLcl - Question about alias in SQLcl

daniel_hauke
daniel_hauke Member Posts: 211 Bronze Badge
edited Jan 25, 2019 1:22AM in SQLcl

Hi everybody,

i was able to create my own alias definiton in my login.sql file (located in our $SQLPATH).

When i login into sql i can see those alias after typing the command alias.

Later i changed the name of some of these alias. After doing this and logging in back again in sql, i can see the new alias and the old alias which are no longer present in the login.sql file.

Here is the content of my login.sql file:

SET sqlprompt "_user '@' _connect_identifier >"SET sqlformat ansiconsoledefine _EDITOR=inlineSET pagesize 25alias instance_info=select instance_name,host_name,status from v$instance;alias archivelog_mode=archive log list;

And here is the output of the command alias in sql:

SYS @ orcl_db >aliasarchivelog_modearchivelogmodeinstance_infoinstanceinfo

Can anyone provide me a hint how to refresh the list of alias after doing such an change in the login.sql?

Greetings Daniel

Gaz in Ozdaniel_hauke

Best Answer

  • srg
    srg Member Posts: 21 Blue Ribbon
    edited Jan 23, 2019 9:58AM Accepted Answer

    @Daniel,

    As best as I can tell, aliases are persisted from session to session - they cannot be "renamed", they must be dropped. If you type "help alias" at the sqlcl command prompt you will see the various options available with the command.

    In Windows, aliases are stored in the user profile under the hidden folder AppData\Roaming\sqlcl\aliases.xml along with backups that are created when new aliases are added.

    Hope this helps!

    Regards

    Gaz in Oz

Answers

  • srg
    srg Member Posts: 21 Blue Ribbon
    edited Jan 23, 2019 9:58AM Accepted Answer

    @Daniel,

    As best as I can tell, aliases are persisted from session to session - they cannot be "renamed", they must be dropped. If you type "help alias" at the sqlcl command prompt you will see the various options available with the command.

    In Windows, aliases are stored in the user profile under the hidden folder AppData\Roaming\sqlcl\aliases.xml along with backups that are created when new aliases are added.

    Hope this helps!

    Regards

    Gaz in Oz
  • daniel_hauke
    daniel_hauke Member Posts: 211 Bronze Badge
    edited Jan 24, 2019 7:42AM

    Hi SRG,

    thanks for the hint. I have checked the following folder under Linux (because we are using sqlcl on our Linux machines):

    /home/oracle/.sqlcl

    In this folder i found the aliases.xml file. I deleted this and issued the command alias in sqlcl again.

    Now it shows me only the aliases i defined in my login.sql.

    And after checking the folder again, a new aliases.xml was created containing only my aliases.

    So my way to see only my defined aliases is the following:

    • Change a existing or create a new alias in the $SQLPATH/Login.sql
    • Delete the aliases.xml under /home/oracle/.sqlcl
    • Login into sqlcl
    • Issue the alias command

    So unless you are deleting the aliases.xml file you will see old and new aliases in sqlcl. So thank you!

    Greetings Daniel

  • srg
    srg Member Posts: 21 Blue Ribbon
    edited Jan 24, 2019 9:44AM

    Hey Daniel, just an additional comment, if you will permit, I may be misunderstanding your specific use case...

    The sqlcl alias command provides all functionality required to manage aliases without deleting the aliases.xml file.

    In your initial post you define 2 aliases within login.sql along with 2 unwanted aliases that had been defined in an earlier session.

    you showed all aliases defined:

    SQL> aliasarchivelog_modearchivelogmodeinstance_infoinstanceinfo

    to permanently remove (delete) an alias:

    SQL> alias drop instanceinfoAlias instanceinfo droppedSQL> aliasarchivelog_modearchivelogmodeinstance_info

    you can even export the aliases to another file defined by you and then load them:

    SQL> alias save srgaliastest.xmlALIAS-007 - Aliases saved to srgaliastest.xmlSQL> alias drop archivelogmodeAlias archivelogmode droppedSQL> aliasarchivelog_modeinstance_infoSQL> alias load srgaliastest.xmlAliases LoadedSQL> aliasarchivelog_modearchivelogmodeinstance_info

    Please forgive me if this is known to you, but thought I would provide the additional examples just in case...

    Regards!

    daniel_haukedaniel_hauke
  • daniel_hauke
    daniel_hauke Member Posts: 211 Bronze Badge
    edited Jan 25, 2019 1:22AM

    Hi SRG,

    thanks for providing me some more tipps about the alias command.

    The possibility to eport the aliasses to a different file and load them is something new to me.

    And i totally forgot about the drop alias command

    So thanks.

    Greetings Daniel

Sign In or Register to comment.