How To Get A List Of Oracle Database Schemas?

Well, this is one of those circumstances, where your ability to answer this question will depend upon your user’s assigned security roles and what you actually want. 

To get a complete list, you will need to use the DBA_ administrator tables to which most of us will not have access.  In the very simple examples below, you may want to add a WHERE clause to eliminate the system schemas from the list, like ‘SYS’ and ‘SYSTEM,’ if you have access to them.

Example Administrator (DBA) Schema List

SELECT distinct OWNER as SCHEMA_NAME

FROM  DBA_OBJECTS

ORDER BY OWNER;

Example Administrator (DBA) Schema List Results Screenshot

Fortunately for the rest of us, there are All user tables, from which we can get a listing of the schemas to which we have access.

Example All Users Schema List

SELECT distinct OWNER as SCHEMA_NAME

FROM    ALL_OBJECTS

ORDER BY OWNER;

Example All Users Schema List Results Screenshot

Related References

Oracle help Center > Database> Oracle > Oracle Database > Release 19

https://docs.oracle.com/en/database/oracle/oracle-database/19/cncpt/data-dictionary-and-dynamic-performance-views.html#GUID-BDF5B748-EB43-4B48-938E-89099069C3BB

Linux man (manual) Command

Despite the ‘man’ commands, relative simplicity and appearance of unimportance, the ‘man’ command is, perhaps, one of the most important commands to lean in Linux. 

Why the ‘man’ command important?

The true value of the ‘man’ command is that provides access to the online manuals (documentation), which will be consulted often until Linux commands and functions have to be learned and internalized.  Even after learning the more familiar and commonly used Linux command and functions, one will still need to refer the less commonly used capabilities or to confirm something which has been used in a while.

When some the more arrogant Linux users will sometime tell folks with questions to “read the frickin’ manual” (RTFM), the ‘man’ command is what they are usually talking about.  Although there are other perfectly useful reference materials online (e.g., git documentation project) or commercial books, the ‘man’ command should be the go-to place for documentation.  The reason this is actually very simple, if the command or function is installed in your version or environment instance of Linux, then man pages will be available.  Therefore, usually, there will no need to go search on the internet for answers or carrying books around.

The ‘man’ command syntax

The syntax of the ‘man’ command is simple and easy to learn to use.  In fact, the ‘man’ command is so easy to use that people frequently will not even use options when they use the man command and enter ‘man’ command and the keyword.

‘man’ command syntax

man [options] (keywords)

Simple examples to illustrate how to use the ‘man’ command.

Example to pull up the ‘Man’ command documentation

[blog-server ~]$ man man

In this example, the man command is using ‘man man’ to pull up its own online documentation.

Example to pull up the ‘ls’ command documentation

[blog-server ~]$ man ls

In this example, the man command is using ‘man ls’ to pull up the ‘list directory contents‘ online documentation.

list directory contents

Example to pull up the ‘cp’ command documentation

[blog-server ~]$ man cp

In this example, the man command is using ‘man cp’ to pull up the ‘copy files and directories ‘ online documentation.

Example screenshot of the ‘cp’ (copy files and directories) file command online documentation

Oracle SQL*Plus Is still Around

It is funny how you cannot work with some for a while because of newer tools, and then rediscover them, so to speak.  The other day I was looking at my overflow bookshelf in the garage and saw an old book on Oracle SQL*Plus and was thinking, “do I still want or need that book?”. 

In recent years I have been using a variety of other tools when working with oracle. So, I really hadn’t thought about the once ubiquitous Oracle SQL*Plus command-line interface for Oracle databases, which around for thirty-five years or more.  However, I recently needed to do an Oracle 18C database install to enable some training and was pleasantly surprised Oracle SQL*Plus as a menu item. 

Now I have been purposely using Oracle SQL*Plus again to refresh my skills, and I will be keeping my Oracle SQL*Plus: The Definitive Guide, after all. 

How to install locate command in Linux Redhat and Centos

To install the locate command (mlocate) in Redhat or Centos, use the ’YUM’ command, install function. 

  • Logon as ‘root’ or use with ‘sudo’ permissions
  • Then, run a ’man’ command to confirm that the locate command is not already installed: ‘man locate’

Example ‘man’ command (as root user)

[root@blog-server ~]# man locate

  • If the ’man’ documentation page returns, then it is already installed.  If no ’man’ documentation page is returned, then run the ‘yum install’ command.
  • Run the ‘yum install’ command as ‘root’ or ‘sudo’ user:

Example ‘yum install’ command (as root user)

[root@blog-server ~]# yum install mlocate

Example ‘yum install’ command (as sudo user)

[root@blog-server ~]# sudo yum install mlocate

Then run the [root@data-server ~]# updated

Example Run ‘updatedb’ Command (as Root user)

[root@blog-server ~]# updated

Example Run ‘updatedb’ Command (as sudo user)

[root@blog-server ~]# sudo updatedb

How To quit the Linux vi editor without saving changes

To quit the Linux ’vi’ editor without saving any changes which have been made:

To Exit the insert or append mode

  1. From within insert or append mode, press the ‘Esc‘ key.

To Enable VI Command Line

  1. Press the ‘:’ (colon) Key. The cursor should reappear in the lower-left corner of the screen beside a colon prompt.
  2. Then, Enter the ‘q!’ Command and press the ‘Enter’ Key.

Example VI Command Line

: q!

This will quit the Linux VI editor, and all changes the document made in this session will be lost.

Oracle Express Edition Error – ORA-65096: invalid common user or role name

While trying to create user in Oracle Database 18c Express Edition I kept getting an “ORA-65096: invalid common user or role name” error, which didn’t make sense to me so after validating my command, that I was signed in as an admin user, and determining that my “CREATE USER” was formatted correctly.  I did some additional research and determined that in the hidden parameter “_ORACLE_SCRIPT” needed to be set to “True” starting with the Oracle Version 12c and higher.

Setting the “_ORACLE_SCRIPT” values

To set the “_ORACLE_SCRIPT” hidden variable to “True” you need to run an “Alter” command. Then you will be able to create the desired user and run your grants commands as usual.

Alter SQL Command

alter session set “_oracle_script”=true;

Linux Move (mv) Command

The Linux move command (mv) is one of the essential commands, which can be very useful in Linux, Unix, and AIX.  The primary purpose of the move command is obviously to move files, and of course, directories.   The move command may also be used to rename files and to make backups.

Move Command Syntax

$ mv [options] source (file or directory)  destination

Move Command options

option description
mv -f force move by overwriting destination file without prompt
mv -i interactive prompt before overwriting
mv -u update – move when the source is newer than the destination
mv -v verbose – print source and destination files
MV – t explicitly saying to move the file or directory here, rather trying to fit everything into the last argument.
mv * Move all (Multiple) files to a specific director without listing by name

For More move command details see the Linux documentation manuals using the man command

$ man mv

mv command examples

Here are some quick and very simple move command (MV) examples for reference.

Move Move to files  to the /Archive/ directory:

$ mv happy.txt garden.txt /Archive/

Move all “.txt” files in the current directory to subdirectory backup:

$ mv *.txt backup

Move all files in subdirectory ‘backup’ to current directory:

$ mv backup/*

Rename file happy.txt to happy.bak filename:

$ mv happy.txt happy.bak

Rename directory backup to backup2:

$ mv backup backup2

Update – move when happy.txt is newer or missing in target directory:

$ mv -u happy.txt backup

Move happy.txt and prompt before overwrite backup / happy.txt:

$ mv -v happy.txt backup