| Doubts [message #10662] |
Sun, 08 February 2004 21:36  |
sudhir
Messages: 20
Registered: November 2000
|
Junior Member |
|
|
Hi evrybdy,
i am a beginner in Oracle, & a few doubts have come up while i've been studying it. ls do help me with my doubts.
1) What has to be done if I want to change a value of a column of data type string, to "&abs" because when ever we take "&" the next part is considered as a variable name.
2) Can we see the uncommitted data by any command?
3) How to see the time content of the date type column & insert the time with date or only time?
4) What is the difference between the BLOB & BFILE and what is the size is ROW & LONG ROW in bytes?
5) How to distinguish between the AD & BC dates, for example i want to record a date of an event which occurred in B.C period so how to do it?
6) What is the difference between cat, tab & user_catalog?
7) There is a option "SET UNUSED" after marking the column unused which is not actually dropped from the table. So is it possible to retrieve it back to make it accessible?
8) Can we change the column name of a table?
Thanx a lot,
Sudhir
|
|
|
|
| Re: Doubts [message #10664 is a reply to message #10662] |
Sun, 08 February 2004 23:13  |
 |
Maaher
Messages: 7065
Registered: December 2001
|
Senior Member |
|
|
1. You specify that your string '&abs' is not a variable by either:orSET ESCAPE <I>(or some other character)</I> SQL> set escape
SQL> select '&abs' from dual;
'&AB
----
&abs
SQL>
2. From another session: no.
3. Change your date format (NLS_DATE_FORMAT). You can do this on your client (in the registry) for every session or you can do it for one session via an alter session command:SQL> ALTER SESSION SET NLS_DATE_FORMAT='DD/MM/YYYY HH24:MI';
Session altered.
SQL> Select SYSDATE
2 From dual;
SYSDATE
----------------
09/02/2004 09:48
SQL>
5. Again, change the NLS_DATE_FORMAT, but this time specify 'AD' in it, e.g. DD-MON-YYYYAD:SQL> desc mhetest
Name Null? Type
------------------------------- -------- ----
COL1 DATE
SQL> insert into mhetest values(sysdate);
1 row created.
SQL> insert into mhetest values ('12-JUL-1206BC');
1 row created.
SQL> select * From mhetest;
COL1
-------------
09-FEB-2004AD
12-JUL-1206BC
SQL>
6.TAB is included for compatibility with Oracle version 5. Oracle Corporation recommends that you do not use this view.
CAT is a synonym for USER_CATALOG.
USER_CATALOG lists indexes, tables, views, clusters, synonyms, and sequences owned by the current user.
This explanation comes from the Oracle reference. At otn.oracle.com you can find all docs. You need to register but it's free of charge.
7.Until you actually drop these columns, they continue to count toward the absolute limit of 1000 columns in a single table. However, as with all DDL statements, you cannot roll back the results of this clause. That is, you cannot issue SET USED counterpart to retrieve a column that you have SET UNUSED.
Also, if you mark a column of datatype LONG as UNUSED, then you cannot add another LONG column to the table until you actually drop the unused LONG column.
8. In Oracle 9.2.x: yes you can in a single command:
Alter table x rename column AA to BB;
In previous versions you can always add a column, duplicate the data and drop the old column, or you could even create a new table with the CREATE TABLE AS SELECT construction (specify a column list). Afterwards, drop the original table and rename the newly created table to the original table name. But this has the consequences that all foreign keys and triggers need recreation.
This is not a complete reply, but it should get you on the way.
HTh,
MHE
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
