I cannot change my password. I followed the procedure exactly and I
got the following results:
SQL> ALTER USER fakeuser IDENTIFIED BY newpassword
2
SQL>
But nothing happened, and my password is still the same!
A: The blank numbered line after the command you entered is the second line of the SQLPlus buffer, which you entered because you did not properly terminate the command above with a semicolon. You can always execute the contents of the buffer by typing '/' at the SQL prompt (this gives you an easy way to repeat actions). But since you didn't execute the buffer, and you didn't terminate your command with a semicolon, your request simply stayed in the buffer, and your password was not changed.
Q: Do I need to save stuff from my database outside of Oracle? Will tables I've loaded into Oracle still be there if I quit and come back again?
A:Oracle will save your tables (that's its job!), so they should be there when you come back again. However, it is a very good idea for you to save all of the commands and data necessary to create your schema on your leland accounts because: a) the leland file system is backed up, while Oracle has not been in the past; b) if you screw up your database somehow, you'll want a means to rebuild it; and c) you probably don't want to leave your randomly generated data sitting in the database when you're not using it, because it takes up space and it is very easy to write a simple shell script that will re-run sqlldr on your data files automatically so you can call just a single command.
Q: What exactly do you mean when you say it is against the Honor Code to edit trascripts? Can we delete bad inputs and the corresponding error messages?
A: The main thing we want to avoid is people creating output that doesn't match the contents of their database or the contents of a given query of their database. e.g. it may be possible for a student to roughly know what the output of a SQL query should be, but they can't figure out how to write it. In that case, we don't want to student to simply make up the output for the query -- that would be a violation of the Honor Code. If you cut the outputs of bad commands and typos or whatever, that's fine. If you are concerned and have a specific question regarding the Honor Code, please email the course staff.
Q: I have a relation where the key is a unique id number. How do I use Oracle to automatically manage the generation of unique numbers for my key?
A: Oracle allows you to create a piece of data called a SEQUENCE, which you can name, and then call a function NEXTVAL, that returns the next value and increments the stored value. For example:
CREATE TABLE test (a INT PRIMARY KEY, b INT); CREATE SEQUENCE aseq; INSERT INTO test VALUES (aseq.NEXTVAL, 3); INSERT INTO test VALUES (aseq.NEXTVAL, 1); INSERT INTO test VALUES (aseq.NEXTVAL, 7); SELECT * FROM VALUES;returns the output:
A B
---------- ----------
1 3
2 1
3 7
You can find out what sequence you've created by querying the
user_sequences table:
SELECT sequence_name FROM user_sequences;and you can drop sequences just as you would any other schema element:
DROP SEQUENCE aseq;
Q: Can we perform math in SELECT/FROM/WHERE queries?
A: Yes. e.g.: SELECT satScore + (400 * GPA) AS scaledScore is a valid fragment of SQL.
Q: What is the best datatype for holding long strings of text? Is there something better suited than a really large varchar?
A: There are two datatypes, CLOB and LONG, that allow for very large text regions (4 GB and 2 GB maximum respectively). LONG is an older datatype, apparently, and has a number of restrictions on it. Oracle suggests that LONGs should be updated to CLOBs. They both seem to work in the SQLPlus environment for basic inserts and queries, though they may pose a challenge to bulk loading, and there may be problems with accessing CLOBs over JDBC (which may cause problems when you construct your web interface). Feel free to use them if you'd like, but understand that there may be some risk that you'll be restricted in doing what you'd like with them.
Q: When I use the INT datatype and then DESCRIBE my table, Oracle tells me I'm using NUMBER(38). Is this a 38-bit number? What if I only need a small range of numbers and don't want to waste that space?
A: INT is just another name for the datatype NUMBER(38), which is a number with a precision of 38 (decimal) digits. Instead of declaring your attributes as INT, you can specifically declare them as, say, NUMBER(2), which can represent only 2 digit numbers (-99 .. 99) -- an error will be raised if you try to enter a value outside of this range.