Home » Other » General » Simple Newbie Questions about SQL and Oracle 10g (Oracle 10g)
Simple Newbie Questions about SQL and Oracle 10g [message #414396] Tue, 21 July 2009 23:52 Go to next message
peace2009
Messages: 11
Registered: July 2009
Junior Member
First of all, i'm glad to be a member in this forum. I can see its for the advanced developers and DBA. However, i'm quite new to Databases in general. I got some newbie questions, i hope you answer me. Either way, i will google and read books to find the answers too. Excuse me if you see any writing mistakes.

1. About External Tables. What are external tables? When is it used in real-life? In other words, when do we need to use External Tables? Why do we need External Tables?

2. "Drop column command is going to make the table go into the exclusive mode lock, thus making it unavailable for others. So if there is a huge traffic(workload) going on, the better option would be to just mark the column as unsued which won't actually drop the column. When the traffic becomes lower, you can go ahead and drop that unused column. Locks are kept as long as the transaction is not over."

When does a transaction end? Is it when ; hits or commit; ?

3. Is tablespace_name carries the name of a partitioned table?

4. Is this query correct:

ALTER TABLE emp2 ADD CONSTRAINT emp_dt_fk
FOREIGN KEY (Department_id)
REFERENCES departments ON DELETE CASCADE;

OR this:

ALTER TABLE emp2 ADD CONSTRAINT emp_dt_fk
FOREIGN KEY (Department_id)
REFERENCES departments(dept_id) ON DELETE CASCADE;

The second one should be correct. However, in the text book is written as the first query! If the first one is correct, why? Department_id references to no column in departments table.

I'm looking forward to hearing from you.

Regards,
Peace2009
Re: Simple Newbie Questions about SQL and Oracle 10g [message #414411 is a reply to message #414396] Wed, 22 July 2009 01:10 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Welcome to the forum.

It is appreciated you first read OraFAQ Forum Guide and search before posting.

All Oracle documentation is on http://tahiti.oracle.com
I recommend you to read:
Database Concepts
Database SQL Reference
They will answer to 3 of your questions.

I recommend to intensively use Search Documentation. If you just put "external table" in the field you will immediately get the answer to your first question, for instance.

Regards
Michel


Re: Simple Newbie Questions about SQL and Oracle 10g [message #414414 is a reply to message #414396] Wed, 22 July 2009 01:28 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
"External tables" feature makes it possible to use a file system file (for example, an ordinary TXT file) as if it was a table in your database. Before it was invented, we have used SQL*Loader (which is still here!) to load data from that TXT file into a table. Now, you can access this file directly, using - for example - SELECT statement.


End of the transaction can be achieved in several ways:
- COMMIT or ROLLBACK statements will do that explicitly
- DDL statement will implicitly commit changes (and end the transaction)
- if you disconnect (or kill) user session, transaction will be implicitly ended


I'm not sure I understood the third question.


If you create a small example, you'll see that both ALTER TABLE ... ADD CONSTRAINT statements work correctly. As emp2.dept_id must reference a column which is either primary or unique key of the parent table (departments.dept_id), the referenced key column is optional.
SQL> create table departments
  2  (dept_id   number primary key,
  3   dept_name varchar2(20)
  4  );

Table created.

SQL> create table emp2
  2  (emp_id   number,
  3   emp_name varchar2(20),
  4   dept_id  number
  5  );

Table created.

SQL> insert all
  2    into departments (dept_id, dept_name) values (1, 'Ora')
  3    into departments (dept_id, dept_name) values (2, 'Faq')
  4    into emp2 (emp_id, emp_name, dept_id) values (100, 'Little', 1)
  5    into emp2 (emp_id, emp_name, dept_id) values (100, 'Foot', 1)
  6    into emp2 (emp_id, emp_name, dept_id) values (100, 'Big', 2)
  7  select * from dual;

5 rows created.

The first constraint:
SQL> alter table emp2 add constraint emp_dt_fk
  2  foreign key (dept_id)
  3  references departments on delete cascade;

Table altered.

SQL> delete from departments where dept_id = 1;

1 row deleted.

SQL> select * from emp2;

    EMP_ID EMP_NAME                DEPT_ID
---------- -------------------- ----------
       100 Big                           2

SQL>

The second one:
SQL> rollback;

Rollback complete.

SQL> alter table emp2 drop constraint emp_dt_fk;

Table altered.

SQL> alter table emp2 add constraint emp_dt_fk
  2  foreign key (dept_id)
  3  references departments (dept_id) on delete cascade;

Table altered.

SQL> select * from emp2;

    EMP_ID EMP_NAME                DEPT_ID
---------- -------------------- ----------
       100 Little                        1
       100 Foot                          1
       100 Big                           2

SQL> delete from departments where dept_id = 2;

1 row deleted.

SQL> select * from emp2;

    EMP_ID EMP_NAME                DEPT_ID
---------- -------------------- ----------
       100 Little                        1
       100 Foot                          1

SQL>

See? Everything works just fine.

This is Oracle 10g Documentation (for your further research).
Re: Simple Newbie Questions about SQL and Oracle 10g [message #414572 is a reply to message #414414] Wed, 22 July 2009 13:44 Go to previous messageGo to next message
peace2009
Messages: 11
Registered: July 2009
Junior Member
Thanks...Its much clear now. However,

"the referenced key column is optional"

Since its optional, i can say Oracle server references to the right column implicitly - foreign key (dept_id) - in this case Oracle server references to the parent column dept_id...

Regarding my third question, what is tablespace_name that's in user_tables data dictionary? Can you tell me in a simple way...


Again, your response answered my questions... I appreciate it
Re: Simple Newbie Questions about SQL and Oracle 10g [message #414577 is a reply to message #414572] Wed, 22 July 2009 14:38 Go to previous message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Quote:
what is tablespace_name that's in user_tables data dictionary

Oh, this?
SQL> select table_name, tablespace_name from user_tables;

TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
EMP                            USERS
DEPT                           USERS

TABLESPACE_NAME represents a tablespace into which Oracle has created the table. It can be specified within the CREATE TABLE statement; if you omit it, table will be created in a default tablespace of the owner of the schema that contains the table.
Previous Topic: What is the meaning of 1 to 1000 oraganization users,,!!!
Next Topic: oRACLE 9i post installaion problem
Goto Forum:
  


Current Time: Thu Mar 28 12:51:29 CDT 2024