How to locate all instances of a field in all your tables

Hey all,

Lets say you create a field called IDNUM and you use that same field in 8 different tables. Now down the road you forget what tables that field is in and you need to know this in order to link it properly. Enter this command and you will see all the tables you have that include the IDNUM field

EXAMPLE

SQL> select column_name,table_name FROM all_tab_cols WHERE column_name
> = 'IDNUM'
> 2 /
>
> COLUMN_NAME TABLE_NAME
> ------------------------------ ------------------------------
> IDNUM DELIVERY
> IDNUM CUSTOMER


Hope it helps!

How to grant view & 10g

Tried several passwords until I decided to use tiger as my password under user ID system. I opened another SQL session and logged in with ID SYSTEM and used PW tiger and then typed at the sql prompt granted view to user ID Scott;

Password for System

What is the password for system ID?

Permissions

Don't know if anyone else had this problem. While I was doing Week 6 assignment using the Scott account at home, I received a permission error preventing me from completing problem #1. In order to get around this you will have to grant CREATE VIEW permission to the Scott account. To do this, you will need to log in with the SYSTEM login and do the following commands:

GRANT CREATE VIEW
TO scott;

You should then be able to complete problem #1 of the homework.

Install tips for Oracle 10g

If you are install Oracle 10g Enterprise Edition on XP with a dynamic IP address from a DHCP server you may encounter a couple of errors during installation.

Here is what I did to get around those errors and get it working.

First, if you get a warning during the Prerequisite checks which states that you need a Loopback Adapter then follow the instructions from this link to install it: http://support.microsoft.com/kb/839013

After you install the Microsoft Loopback Adapter, you may have to configure the IP address to a static IP in the 192.168.x.x range for it to work properly. After its installed you should pass the prerequisite checks.

The next error you may encounter will be a ORA-12638: Credential retrieval failed error. This can be worked around by editing your sqlnet.ora file. This file can be found in you C:\oracle\....\db_1\NETWORK\ADMIN folder if you install in the default folders. You can use Wordpad to edit it and make the following change:
Original Entry - SQLNET.AUTHENTICATION_SERVICES= (NTS)

Modified Entry - SQLNET.AUTHENTICATION_SERVICES= (NONE)

I hope these tips are helpful. If you are still having trouble getting Enterprise Edition to work, I would suggest completely disinstalling it and installing the Personal Edition. With the Personal Edition you won't be able to manage your databases remotely over a network and you won't have access to the Enterprise Management Module, but you can still do your homework using the SQL Plus without any trouble.

Welcome to CIS50!

Please post any notes or questions for classmates about the course. For example, it would be helpful to post the steps you used to install. Others may find it helpful.