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  |
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 #414414 is a reply to message #414396] |
Wed, 22 July 2009 01:28  |
 |
Littlefoot
Messages: 21823
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 |
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 |
|
Littlefoot
Messages: 21823
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.
|
|
|
|
Goto Forum:
Current Time: Mon Nov 25 22:29:34 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
