newbie question about OracleExpress 1

[jtripper6]

I've used MySQL before, but never Oracle. I just downloaded and installed Oracle Express 10g.

I'm confused. In MySQL, you can create a database and the tables it contains. You then can do 'show databases' to show what databases exist and 'show tables' to show what tables are in the particular database you're using. I'm using Sql*Plus and want to know what databases there are to connect to, and how to create databases and tables. Can someone tell me what I need to do or point me to a beginners Oracle tutorial on the web? I'd appreciate any help.
JT

 

[SantaMufasa]

JTripper,

Welcome to the Amazing World of Tek-Tips.com...A great place to get answers to questions (as you may have already found out on your other couple of posts since you started here last week).

And also, welcome to the Amazing World of Oracle. I know your pain. (I have been a Database Administrator for Oracle, MySQL, Sybase, MS SQL Server, DB2, Postgres and EnterpriseDB, and I recall all too vividly my frustration at there not being "equality" amongst the differing environments.) One of my "Bucket List" goals is to write an equivalency matrix for all the of the "Frequently Used" commands for these environments. (Perhaps I should call it my "FU Commands". <grin>)

Now to your questions...

Firstly, the term "database" as you know it in MySQL (and as it is with all other database engines except Oracle) has a very different meaning in the Oracle World. For ease of reference, let's use the term Rest of World (RoW) to collectively represent MySQL plus the rest of the Database World, excluding Oracle.

The term database in the RoW is roughly equivalent to these virtually synonymous terms in Oracle: USER/SCHEMA/OWNER (I'll coin the initialization, "USO" to represent these virtual synonyms in Oracle.)

(In my text that follows, assume SQL*Plus as the application in which any sample commands execute.)

When you connect (i.e., "log in") to an Oracle environment (using SQL*Plus) you will reference a <USO Name>, its <USO password>, and, optionally at <Host alias or string>. For example, if you are using a Windows graphical version of SQL*Plus, when you invoke SQL*Plus, it prompts you for:

User Name:
Password:
Host String:

If, instead, you invoke SQL*Plus from an operating-system command prompt (with virtually any operating system), it could appear as:

C:\ sqlplus <USO name>/<USO password>@<host alias>

example:

c:\ sqlplus dhunt/thisisnotmyrealpassword@hrappl

(In all of my examples, anything appearing within carets ("<...>") should be replaced with some valid replacement value as I have done in the example, above.)

In both environments (GUI or o/s command line), SQL*Plus responds to a successful log-in attempt with the default prompt:

SQL> _

At the SQL*Plus prompt, you can issue commands from three completely different languages, which SQL*Plus is prepared to interpret and execute. Those three different languages, each with their own different set of syntax and commands, are:[ul][li]SQL*Plus (Oracle's own non-industry-standard, proprietary user-environment interaction language) Click this link to SQL*Plus for a full on-line reference of SQL*Plus commands.

[/li][li]SQL (a industry-"familiar" [not yet industry-standard] database-interaction language that has commands/syntax to read from and update relational databases. SQL, across all database-engine vendors typically supports these types of commands:

[/li][ul][li]Data-Definition Language (DDL): commands to Add, Modify, or Remove larger database objects/structures such as tables and indexes:
[/li][ul][li]CREATE <object type> <object name> <specifications>;[/li][li]ALTER <object type> <object name> <modifications>;[/li][li]DROP <object type> <object name>;

[/li][/ul][li]Data-Control Language (DCL): commands to Add or Remove permission to use database commands, database objects, or to be a member of a database ROLE:[/li][ul][li]GRANT...[/li][li]REVOKE...


[/li][/ul][li]Data-Manipulation Language (DML]: commands to Query, Add, Change, Delete, Save (or Undo) changes to individual elements (i.e., "rows") of information from a large object (such as a table):[/li][ul][li]SELECT...[/li][li]INSERT...[/li][li]UPDATE...[/li][li]DELETE...[/LI][li]COMMIT;[/LI][LI]ROLLBACK;


[/LI][/ul][/ul][li]PL/SQL (Oracle's proprietary Procedural-Language (i.e., 3GL) environment for running SQL statements and manipulating data in a Third-Generation (3GL), procedural style, complete with 3GL constructs such as Loops, Branching, and user-defined Procedures and Functions. Click this link to PL/SQL for an overview and reference of the PL/SQL language.[/li]
[/ul]

In MySQL, you can create a database...

In Oracle, the closest equivalent to a MySQL database is a "User/Schema/Owner" (USO, my term). To create an Oracle USO, the "creator" must have been granted the "CREATE USER" privilege or be a member of the "DBA" role, which possesses the "CREATE USER" privilege. A user possessing the "CREATE USER" privilege, either via membership in the "DBA" role or via direct granting, can issue commands similar to the following:

CREATE USER jtripper6
       IDENTIFIED BY jtripperpw
       DEFAULT TABLESPACE users
       TEMPORARY TABLESPACE TEMP
       QUOTA 100M ON USERS;
GRANT CONNECT, RESOURCE TO jtripper6;

Once you (as "jtripper6") connect to SQL*Plus (by virtue of the "GRANT CONNECT" privilege from above), using the password that appears, above, you can create tables and other objects (by virtue of the "GRANT CONNECT" and "QUOTA..." specifications, above).

In MySQL...You...can do 'show databases' to show what databases exist

In Oracle, you could see what USOs exist with this command:

select username from all_users;

In MySQL...You...can...'show tables' to show what tables are in the particular database you're using.

In SQL*Plus, you can issue this command to see what tables You (as you have connected to SQL*Plus) own:

SELECT TABLE_NAME FROM USER_TABLES;

To display the structure of any of the tables you own, you can issue this SQL*Plus (not [/I][/B]SQL[/I][/B]) command:

DESCRIBE XYZ

To see the names of tables that other USOs own, you can enter this SQL query:

select owner, table_name
  from all_tables
 order by owner, table_name;

If you wanted to see just the names of tables that I (as USO “dhunt”, a different owner from you) own, you can issue a similar command:

 select owner, table_name
  from all_tables
 where owner = ‘DHUNT’
 order by table_name;

You can see the structure of a table that some other USO owns with this SQL*Plus command (similar to the one, above):

describe dhunt.hr_table

Notice the qualification I used, “dhunt”, to specify the owner of the table I wish to see. If I failed to specify the owner explicitly, then the “describe” command looks in my own collection of tables (as the USO to which I am currently connected) to try to find a table by that name. If I don’t own such a table, then SQL*Plus responds with this error:

 SQL> describe some_table
ERROR:
ORA-04043: object some_table does not exist

Notice that if I qualify the table name with the name of an owner under which the table exists, then I get results:

 SQL> describe yada5.some_table
 Name                    Null?    Type
 ----------------------- -------- ----------------
 ABC                              NUMBER
 XYZ                              VARCHAR2(20)

In MySQL, you can connect as one login name, then tell MySQL that you want to “use <some DB name>”, which causes default access to be against the database that is <some DB name>”. In Oracle, login name with which you connect to Oracle (in SQL*Plus) becomes your default “database”, and if you wish to access data from some other database, then you can either:[ul][li]connect to the other USO, or[/li][li]qualify the table you wish to access with the name of the other USO that owns the table you wish to access.[/li][/ul]Note: If you wish to access another USO’s table in Oracle, the other USO must GRANT to you the privileges you need to access her/his table.

There are a couple of “shortcuts” one can use in Oracle to access someone else’s table, without having to qualify their table’s name with the owner’s name, but that can be for another lesson.

In the above examples, you might notice that I queried objects that don't exist in MySQL, such as "ALL_USERS", "USER_TABLES", and "ALL_TABLES". These are only three of the nearly 2000 data-dictionary views that exist in an Oracle database, which do not exist in MySQL (or most of the RoW)! This massive data-dictionary feature of Oracle is one of the distinctive, powerful differences between Oracle and the RoW. (Note: DBAs have access to the nearly-2000 views; USOs with non-DBA permissions have access to nearly 800 of the views.)

To obtain a comprehensive list of data_dictionary views that are available to you, you can run this query:

select view_name from all_views
order by view_name;

You can DESCRIBE any one of those 700+ views just as you would describe a table or view that you personally owner, and you don't even need to qualify the view name with the owner name! (The owner, in the case of data-dictionary VIEWs is "SYS", BTW.)

When you run that query to list names of data dictionary VIEWs, you will probably notice a couple of distinct categories:[ul][li]Views prefixed with USER. These are views whose rows contain entries for which your log-in (i.e. your USO) is the owner. Examples: "USER_TABLES", "USER_VIEWS", "USER_SYNONYMS", et cetera. When a DBA creates a new user, and that new user has not yet created any of her/his own objects, these "USER_..." views will generally be empty.[/li][li]Views prefixed with ALL. These are views whose rows contain entries for not only objects that you own, but also for objects that other users own, but to which you have access.[/li][/ul]

I have covered much here. I hope I have not caused you "intellectual indigestion".

I believe, however, that I have addressed your original questions (perhaps to a much greater detail than you wanted), but I’ll bet there are other questions that will soon come. If the follow-on questions relate specifically to the commands we’ve covered here, then post the follow-ons in this thread.

If, however, any follow-on questions are on different issues, then please create a new thread, using an appropriate subject/thread title.

Good luck,


Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[SantaMufasa]

At least one correction to my post, above: Please change

...(by virtue of the "GRANT CONNECT" and "QUOTA..." specifications, above).

...to instead read:

...(by virtue of the "GRANT RESOURCE" and "QUOTA..." specifications, above).

Sorry.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[KenCunningham]

Dave - yet another magnum opus useful for dabblers and professionals alike. Thanks again and have yet another star to add to your constellation!

I want to be good, is that not enough?

 

[SantaMufasa]

You are very generous, Ken...Thank you kindly.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[dstxaix]

Mufasa - Your post shows very clearly that you are a good "teacher" (or instructor).