RE:
Thanks anita, -Original Message- Sent: Sunday, August 12, 2001 8:15 PM To: Multiple recipients of list ORACLE-L Jaimin, I'm not an OS person, so my explanation of what's going on at the OS level is my best guess, but I'm hoping someone else on the list will correct me and/or explain it better. The OS has no knowledge of Oracle's internal layout of the file and db block size, nor does it need to. To the OS an Oracle datafile is the same as any other file and it will store it physically on disk in what ever manner it sees fit. Unless the disk has been defragged recently, most likely the file is scattered all over the place on the disk. The requirement for blocks within an extent to be contiguous refers only to Oracle's internal numbering of the db_block_size blocks within the datafile. Again, this is so Oracle can efficiently identify which blocks belong to which extent. This has absolutely NOTHING to do with how the file is stored at the OS level. Oracle doesn't care about how the OS is storing the file physically on disk because it doesn't need to know anything about this. When Oracle needs to read block 10 of datafile 1, for example, it makes a call to the OS. This is where my knowledge of the OS gets fuzzy. I'm guessing that Oracle is going to make a logical read request to the OS to read datafile 1 and give the starting point (the offset into the datafile) and the number of bytes to read. So for block 10 of a datafile with a 2K block size that would be an offset of 20480 and 2048 bytes to read. The OS will in turn determine where this chunk of 2048 bytes of the file is located on disk and read as many different OS blocks (that could be anywhere on the disk) as needed to fullfill Oracle's read request. Again, Oracle doesn't care what the OS does with the file, because it merely makes calls to the OS to perform reads/writes. List, feel free to correct me! HTH, -- Anita --- jaimin [EMAIL PROTECTED] wrote: Hi, Thanks for the reply, But when you take cold backup You use operating system's copy command, so operating system will copy it depending on operating systems block size i.e 1024 bytes and my database block size is 2048 bytes. Now how it will store database blocks continuousely? I am little bit confused Please help me with this. Thanks, Jaimin. -Original Message- Sent: Saturday, August 11, 2001 9:41 PM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Jaimin, When Oracle allocates extents, it must find contiguous blocks within its own internal layout of the datafile. This is completely different from, and has no realtionship to, how the OS stores the datafile on disk. HTH, -- Anita --- jaimin [EMAIL PROTECTED] wrote: Hi gurus, Can u please help me? Operating system: windows nt 4.0 Oracle version : oracle 8. db block size 2048 Operating system block: 1024 I am taking hot backup of my database and using copy command of operating system to backup my files. For an extent in database all db blocks are continuous. If I copy datafile from one place to another will my db blocks be continuous to form one extent? If I have two disks. If I copy one datafile from one disk to another, as operating system will allocate operating system blocks in harddisk and makes link from one block to another. Will I get continous blocks to form an extent? How oracle and operating system manages with this? Thanks, Jaimin. __ Do You Yahoo!? Send instant messages get email alerts with Yahoo! Messenger. http://im.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: A. Bardeen INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: jaimin INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE:
Thanks Anita, That clears my doubt. Jaimin. -Original Message- Sent: Saturday, August 11, 2001 10:18 PM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Jaimin, Oracle does not care how the file is stored at the OS level - it leaves that entirely up to the OS. All it cares about is its own layout of the blocks. For example, let's take a datafile that is 200K. With a 2K Oracle blocksize this means the datafile can store 100 Oracle blocks. So Oracle has a layout of the datafile with all 100 blocks so it can keep track of which blocks have been allocated to extents belonging to objects and which blocks are free. When it allocates an extent the blocks must be contiguous. So given a 10K extent it can use blocks 20-24 for that extent, but not blocks 12, 18,19, 35 and 51. If you look in DBA_EXTENTS you'll see the BLOCK_ID, indicating the first block for that extent, and BLOCKS, indicating the # of blocks in the exent. By requiring that an extent be composed of contiguous exetnts, this allows Oracle to identify the extent in a datafile in which a block resides by using just those two pieces of information. If the blocks didn't have to be contiguous, then Oracle would have to maintain some form of linked list to identify the blocks associated with an extent. Imagine the tremendous overhead this would cause and the lack of scalability with large extents and datafiles. Now lets say that Oracle needs to read block 20 from the file, Oracle merely makes a request to the OS to read blocks 20-22 from that file. The OS in turn determines where blocks 20-22 are stored on disk and reads them. Oracle doesn't care whether blocks 20-22 are contiguous on disk as Oracle isn't managing the location of the blocks on disk, the OS is. To put it another way, Oracle doesn't care whether the blocks are located contiguously in a physical manner, it cares that they are numbered contiguously. Block 37 will always be the 37th block in the datafile, regardless of where the OS locates that block on disk. Does that help... or did I just muddy the waters even further? ;) -- Anita --- jaimin [EMAIL PROTECTED] wrote: Hi, Thanks for the reply, But when you take cold backup You use operating system's copy command, so operating system will copy it depending on operating systems block size i.e 1024 bytes and my database block size is 2048 bytes. Now how it will store database blocks continuousely? I am little bit confused Please help me with this. Thanks, Jaimin. -Original Message- From: A. Bardeen [mailto:[EMAIL PROTECTED]] Sent: Saturday, August 11, 2001 9:41 PM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: Jaimin, When Oracle allocates extents, it must find contiguous blocks within its own internal layout of the datafile. This is completely different from, and has no realtionship to, how the OS stores the datafile on disk. HTH, -- Anita --- jaimin [EMAIL PROTECTED] wrote: Hi gurus, Can u please help me? Operating system: windows nt 4.0 Oracle version : oracle 8. db block size 2048 Operating system block: 1024 I am taking hot backup of my database and using copy command of operating system to backup my files. For an extent in database all db blocks are continuous. If I copy datafile from one place to another will my db blocks be continuous to form one extent? If I have two disks. If I copy one datafile from one disk to another, as operating system will allocate operating system blocks in harddisk and makes link from one block to another. Will I get continous blocks to form an extent? How oracle and operating system manages with this? Thanks, Jaimin. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: jaimin INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do You Yahoo!? Send instant messages get email alerts with Yahoo! Messenger. http://im.yahoo.com/ __ Do You Yahoo!? Send instant messages get email alerts with Yahoo! Messenger. http://im.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: jaimin INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access
RE: Update ALL colums without knowing their name??-correction
Hi, I don't know whether this will serve your needs ! If you are using oracle8i then you can make relation between this two tables by adding common column. And then giving statement (ex/ new_columns is the column relating two tables) Update Table1 a set a.Every_columns = ( Selectb.Every_columns from Table2where a.new_columns=b.new_columns ) where a.new_columns=(select b.new_columns from Table2where a.new_columns=b.new_columns . Then dropping that column named new_columns. I don't know how You can make a procedure with this? BFN. Jaimin. -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Csillag ZsoltSent: Wednesday, July 25, 2001 1:06 AMTo: Multiple recipients of list ORACLE-LSubject: RE: Update ALL colums without knowing their name??-correctionHi,Thank you for your answer.Unfortunatelly I can't truncate, then insert into ... select ( I myself thought about it before )becausethe tables are always used ( there are always a few users connecting to database).Thank youZsolt Csillag HungaryAt 13:52 2001.07.23. -0800, you wrote: Are you just trying to totally refresh table1 from table 2? how about: 1. TRUNCATE table1; 2. INSERT INTO table1 SELECT * FROM table2; (assuming structures of the two tables are compatible) Or am I just totally missing the mark in what you are looking to do? Jon [EMAIL PROTECTED] -Original Message- From: Csillag Zsolt [mailto:[EMAIL PROTECTED]] Sent: Monday, July 23, 2001 5:33 PM To: Multiple recipients of list ORACLE-L Subject: Update ALL colums without knowing their name?? Hi, I need an universal update procedure in which I don' t know the colums name. How can I make an update like this: Update Table1 set Every_columns = ( Select * from Table2) I tried set Table1.* wiht no result. How can I work around this? Csillag Zsoltwww.star-soft.hu
RE: Online backup script..........
Thanks svend, BFN. -Original Message- Jensen Sent: Thursday, July 05, 2001 12:51 AM To: Multiple recipients of list ORACLE-L Here are two files, a NT cmd and a sql script that do hot backup of archivelog databases. Remember to configure the scripts and ajust to your enviroment jaimin wrote: Hello guru's, I am using one database in nonarchive log mode, I want to change it to archive log mode. Platform Windows nt 4.0 Oracle 7.3.0 database size 10GB. Can any body give me the script which will automatically take online backup of my database? BFN, Jaimin. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: jaimin INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: jaimin INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Online backup script..........
Hello guru's, I am using one database in nonarchive log mode, I want to change it to archive log mode. Platform Windows nt 4.0 Oracle 7.3.0 database size 10GB. Can any body give me the script which will automatically take online backup of my database? BFN, Jaimin. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: jaimin INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: urgent..! how to combining 2 querries...
Try this, select a.tablespace_name,sum(a.bytes),sum(b.bytes) from dba_data_files a,dba_free_space b where a.tablespace_name=b.tablespace_namegroup by a.tablespace_name; JAIMIN. -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Saurabh SharmaSent: Tuesday, July 03, 2001 5:16 PMTo: Multiple recipients of list ORACLE-LSubject: urgent..! how to combining 2 querries... hi gurus, any body have any idea of how to join these querries. i want to select from two different tables namely dba_data_filescall it A and dba_free_space call it B. but the columns being selected are same i.e. tablespace_name and bytes from both tables. i want to select in a single querry as.. tablespace_name bytes1 bytes2 -- -- -- -- -- -- -- -- -- where bytes1 is sum(bytes) group by tablespace_name in table a. and bytes2 is sum(bytes) group by tablespace_name in table b. Saurabh Sharma [EMAIL PROTECTED]http://www.geocities.com/saurabh00pc/stride.html
Snapshot package.
Hello gurus,
I am testing dbms_snapshot package on my test server. I run
dbmssnapshot.sql script which is in rdbms directory. The script gives no
error.
I have snapshot called 'TEST_SNAP'.
When I execute procedure it gives me following error.
SQL exec dbms_snapshot.refresh ('test_snap','?');
begin dbms_snapshot.refresh ('ja','?'); end;
*
ERROR at line 1:
ORA-04068: existing state of packages has been discarded
ORA-04063: package body SYS.DBMS_SNAPSHOT has errors
ORA-06508: PL/SQL: could not find program unit being called
ORA-06512: at line 1
Can any one help me on this?
Regards,
Jaimin.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: jaimin
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Snapshot package.
Hello gurus,
I am testing dbms_snapshot package on my test server. I run
dbmssnapshot.sql script which is in rdbms directory. The script gives no
error.
I have snapshot called 'TEST_SNAP'.
When I execute procedure it gives me following error.
SQL exec dbms_snapshot.refresh ('test_snap','?');
begin dbms_snapshot.refresh ('ja','?'); end;
*
ERROR at line 1:
ORA-04068: existing state of packages has been discarded
ORA-04063: package body SYS.DBMS_SNAPSHOT has errors
ORA-06508: PL/SQL: could not find program unit being called
ORA-06512: at line 1
Can any one help me on this?
Regards,
Jaimin.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: jaimin
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: Data dictionary for function
Hi, DBA_SOURCE is the table which stores information about all procedures and fuctions. SQL desc dba_source NameNull?Type --- OWNER NOT NULL VARCHAR2(30) NAMENOT NULL VARCHAR2(30) TYPE VARCHAR2(12) LINENOT NULL NUMBER TEXT VARCHAR2(4000) Jaimin. -Original Message- Xing Sent: Friday, June 29, 2001 9:50 AM To: Multiple recipients of list ORACLE-L Hi all, Do you know what data dictionary store information about all user function, store procedure ? Best Regards Sinardy -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Sinardy Xing INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: jaimin INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Is it possible???
I think you can try using PRODUCT_USER_PROFILE table. You can disable perticular command by this table. This will only work in sql prompt. Other applications can use commands. -Original Message- Malik(IT) Sent: Monday, June 25, 2001 11:50 AM To: Multiple recipients of list ORACLE-L Hi Friends, I want that user only access data from application not from sql prompt. Any help would be highly appreciated. Shahid. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Shahid Malik(IT) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: jaimin INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: which initSID?
This will not give you solution. As backup of controlfile to trace will not give initSID.ora file path. Actually as per my knowledge there is no table which gives path of initSID.ora file. What you can do is find all init parameter files in your system. Then check SID part of your filename. If you have any doubt then also count SGA of each initSID.ora and your database SGA. This is not 100 percent correct and also seems to be some what tedious but you can try this for your database. Regards, Jaimin. -Original Message- Michael (TEM) Sent: Tuesday, June 26, 2001 3:51 AM To: Multiple recipients of list ORACLE-L Can't you do it indirectly with an 'alter database backup controlfile to trace' command? I seem to recall that there's a path to the initSID.ora file there. 'Course, it could be just the generic path...I never tried changing it to see... HTH, Mike --- === Michael P. Vergara | I've got a PBS mind in an MTV world Oracle DBA | Guidant Corporation | -Original Message- Sent: Monday, June 25, 2001 2:51 PM To: Multiple recipients of list ORACLE-L Seema, There is no way to find that out. Unless, the init.ora uses ifile option and the file pointed to by ifile contains all the parameters. In that case you can query v$parameter view to see what ifile points to. By default Oracle looks for it in the $ORACLE_HOME/dbs directory. HTH, - Kirti Deshpande Verizon Information Services http://www.superpages.com -Original Message- From: Seema Singh [SMTP:[EMAIL PROTECTED]] Sent: Monday, June 25, 2001 2:09 PM To: Multiple recipients of list ORACLE-L Subject: which initSID? Hi gurus How we know which initSID.ora file are in use in running instance if disk is not designed as OFA? Where can I found referenced tables name menas which Data dictionary table? Thanks -Seema -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Vergara, Michael (TEM) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: jaimin INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
