Re: A DBA's career development
"Khedr, Waleed" wrote: > I'd like to be rich and retire now. Spend more time with my family and kids. >... > When this happens, I do not want to see Oracle:)- Ooohkay...but you still going to stick with us sorry lot that still have to fight the brain-frying, mutant-cyber pokemon-beast, right ??? Robert Chin -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Robert Chin 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: XP vs. W2000
Nah, I'm no DBA, just a %X*$@?! developer that gets a secret joy out of watching DBAs pulling their hair out.(or chewing off their nails...) Robert Chin - Original Message - > Robert, you're a DBA, and you still have hair? ;) > > Jared ( hasn't pulled it *all* out, yet ) -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Robert Chin 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: XP vs. W2000
> > Forget it. > Get Suse 7.2 or 7.3, or the latest version of RedHat. Yeah, LINUX is great but get ready to pull (some of) your hair out installing Oracle. God help you. (or find a LINUX guru and pay him to do it) Robert Chin -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Robert Chin 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: Help - DBA interview questions I faced
> I liked the interviews where they told me about their project, and > I told them how I could contribute - strengths and weaknesses. > I'm great at *. I don't know much about YYY, but I'm willing to > learn..We cannot know everything. That worked in the "good old days" (pre-2001), not any more, not by a long shot These days one is expected to be EXPERT of everything the employers list explicitly and implicitly (which seems to be getting longer...) plus this and that Oracle guru ? you'd wish it's so simple...so Mr Xyz, do you know SQL Server ? Sybase ? MQ ? Java ? EJB? ASP? Data Warehousing ? data modeling ? Oracle Apps ? SAP? Essbase ? Cognos ? Informatica ? That's what's so freaky and hedious the job market for hands-on techies is. You ARE expected to know everything ! Robert Chin -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Robert Chin 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: excel data into oracle through sql loader
I was tempted to fall back on my VBA background and give you a bit of code to fix this nasty habit of your users (101201 = 10th Dec 2001 ! how the heck are they allowed to do that ???) Then I realized you're using sqlldr to do the loadingso now you don't have to mess around with Excel. So you can use functions in the control file to convert this data column then... eg. LOAD DATA INFILE * INTO TABLE DEPT FIELDS TERMINATED BY ',' (DEPTNO, DNAME, MESSY_DATE "to_date(:messy_date,'ddmmyy')" ) BEGINDATA 10,Sales,101201 20,Accounting,111201 30,Consulting,121201 40,Finance,131201 OR LOAD DATA INFILE * INTO TABLE DEPT FIELDS TERMINATED BY ',' (DEPTNO, DNAME, MESSY_DATE date 'ddmmyy' ) BEGINDATA 10,Sales,101201 20,Accounting,111201 30,Consulting,121201 40,Finance,131201 HTH Robert Chin - Original Message - > Hi lists, > > I need to load the excel sheet data to oracle tables. A date column is > improperly entered by users. > > In the excel sheet the date column was filled up without using hyphens > or slashes > the data is like this > > todays date: 10th Dec 2001 > (it is supposed to be 10/12/2001 or 10-Dec-2001 etc...) > > But it was entered in the cells as 101201 > When I formatted the data using the excel option cells -> format > > The data it is displaying in a strage format: 1/27/2177 > > All the date values are displayed improperly. > > Does anybody come across such a situation. How to format the date cells > in excelsheet. > (should I change any options for the sheet) > > > Thnx in advance, > > Srinivas -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Robert Chin 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: SQL Lock & consistency - a great answer
> I have to share this post I got on another list. Excuse me but what List is this ? Thanks Robert Chin - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Tuesday, December 11, 2001 3:10 PM > I have to share this post I got on another list. Someone had asked a > question about how Oracle locks work, here is the question... > > Q: i'd like to understand the lock modes (share & exclusive) > & how to make them on table in DML transaction & why locking is > automatically use the lowest level of restrictiveness > & how the read consistency occures > > > Here is the response someone sent > > An Oracle table is like a public restroom with an open door. > > When you go in to use the restroom, you shut the door as you > enter, and you have placed the bathroom in shared lock mode. > When another person want to use the restroom too, they can > push open the door and enter. Both of you are now in the > restroom and sharing it. > > However, if you go into the restroom, shut the door, and also > lock it, then you have acquired the restroom in exclusive lock > mode. Nobody else can come into the bathroom, even though there > are multiple stalls in there: your exclusiveness prevents anybody > else from going in there. > > In a third situation, you go into the restroom in shared mode, > and then somebody else comes in after you, then it would be > very presumptious of him to lock the door behind him because > it just isn't done for someone to lock another stranger into > the restroom; it would also make you very apprehensive to know > that he's now locked you in there with him. So, if someone > already is in the restroom and is sharing it, it prohibits > someone else from coming in later and claiming exclusivity > on it (at least not until the first person leaves.) > > So, when you acquire the restroom, you should use the lowest > level of lockage, otherwise you're just being rude in not > sharing use of the facilities, and you would probably cause > a long queue of people outside the restroom causing contention > problems waiting to gain access to the bathroom because it's > been locked in exclusive mode. > > Now, suppose you set up web cams inside the restroom to > take video images of the restroom and the stalls therein, > and issue every one who enters virtual reality googles. > The web cams guarantees users' privacy by use of read- > consistency: the moment someone goes into a stall and closes > the door, the web cam switches from live feed to replaying > footage of an empty stall that was recorded from just prior > to someone going into that stall. So, although there may be > several people in the stall doing their business, your > virtual reality googles always seem to show you clean, empty > stalls. You pick an empty stall and try to enter it. If the > stall is occupied, the stall door will be locked, so you > can't enter it and do things to it, but through the magic > of read-consistency, it looks as if it is unused; so like an > obstinate idiot, you keep trying, and trying to open the door > that won't open; at least not until the person in the stall > comes out again, but you don't know that because your eyes > tell you that the stall is free; you can see it, you just > can't seem to be able to touch it. If the stall is truly > unoccupied, you may enter and close the door behind you and > now you have locked your stall record in exclusive mode > (although the restroom as a whole can still be in shared > mode; you just don't want anyone else with you in the stall > itself.) > > After you do your business in the stall, and piss all over > the toilet seat making a huge mess, you can either commit > your work by just exiting the stall, exposing what you've > done to the stall to all the other web cams now looking in > on your messy, empty stall; or you can rollback your work > by cleaning up the mess so that when you exit, nobody else > is the wiser that you peed with the toilet seat down. The > moment you commit or rollback by exiting the stall, your > claim on the stall is released, and someone else may now > acquire the stall. > > So, the lesson to be learnt here is: either learn to piss > sitting down, or raise the seat when you pee. > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Farnsworth, Dave > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing Lists > --
Re: Installing Oracle on W2k
>Any-body had any similar experience? Oh yeahand how. Yours is a Pentium 4 box ? then read on... It's a well-known problem... The reason for this is that the P4 architecture has problems with the Java Just In Time installer. I'm not going to repeat it here. The detailed answers are on orafaq.com, just search on "installation windows 2000" Robert - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Sunday, December 09, 2001 12:30 AM > Hi All, > > I am trying to install oracle 8.1.7 on W2k Server. > OUI doesn't start at all. What could be wrong? Any-body had > any similar experience? > > TIA, > Rajesh > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: RAJESH DAYAL > 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: Robert Chin 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: Need a listserver for :-( ms sql server
Wh...what's that ? Robert Chin > - Original Message - > To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> > Sent: Friday, December 07, 2001 19:45 > > > > Hi, > > > > Anyone know of a good list for mssql? > > > > -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Robert Chin 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: DBA Weakest Link
Uhm..Mike, you mean you've come across the *references* to it in prints/chat often, and not in your production db in your role as DBA, right ? Robert - Original Message - > You gotta be kidding! I see this so often I see it in my sleep! > "Snapshot too old: rollback segment...too small" > > Are you getting this error a lot? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Robert Chin 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: DBA Weakest Link
Q: Why does Larry Ellison keeps asking for bigger and bigger rollback segments ? A: Because he never commits ! Anyway I don't mean to rain on your parade but i think this game your scheming is kinda dumb. Personally at Christmas party (heck or any partyesp. when there's alcohol) I DO NOT want to hear NOTHING about Oracle, or MS...NADA ! And with dutch courage I'd probably poison anyone who gets into it and gets "technical". Robert - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Friday, December 07, 2001 9:20 AM > Hello DBAs, > > I'm having a company holiday party today, and as you all may know my company > employs mostly DBAs. (Pythian is an Oracle DBA Outsourcing shop.) > > I'm thinking of having a "DBA Weakest Link" game for fun at some point of > the evening. Yes, I know, the partners/husbands/wives will think it's a bit > of a bore, but imagine the fun for us DBAs! :-) -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Robert Chin 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: Datawarehouse Sizes.....informal poll.
>> My short take on this is that a datawarehouse is a OLTP database.. What ??? I think you should keep that "short take" to yourself for the benefit of those who are seeking to learn/understand about DWH. It's not uncommon to find OLTP databases with de-normalized data, and they are certainly NOT DWH. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Robert Chin 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: December Oracle Newsletter - too funny
Oh...give them a *break*, will ya. Robert - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Tuesday, December 04, 2001 3:45 PM > Yea, and I bet that behavior "scales up" too. > > ;-) > > -Original Message- > Sent: Tuesday, December 04, 2001 2:35 PM > To: Multiple recipients of list ORACLE-L > > > > The following bit regarding the 'Unbreakable' appeared > in my December copy of a newsletter I receive from Oracle. > > The part enclosed in appeared in bold print. > > :) > > Jared > > --- > > When it comes to your mission-critical > information, an ounce of prevention is worth > tons of cure, as no business can afford to > waste time with rare--but often catastrophic-- > data corruption. That's why Oracle launched > the Hardware Assisted Resilient Data > (HARD) initiative, delivering **Error! > Bookmark not defined**., unbreakable data. > > --- > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: > 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: Mohan, Ross > 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: Robert Chin 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: Listener
I'm inclined to think that NO ONE can explain it in this medium better than the documentation. - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Tuesday, December 04, 2001 2:25 AM > Hallo, > > Can anybody tell me simply how the listener works? > > Thanks in advance > > Roland S > > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: > 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: Robert Chin 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).
Message to Session Users - How's it done in your org ?
In our current environment, we have the need to be able to
send LAN pop-up message to users of ACTIVE Oracle sessions.
("Pls log out NOW !...", "Pls don't do any x transactions..." etc you get
the idea)
If your organization have similar need.
Do you know how it's done in your organization ?
Pls specify the infrastructure (whether it's via Internet or LAN and the OS)
Thanks
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Robert Chin
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).
Calling SQL*Plus and Run Procedure in UNIX script, How ?
In UNIX shell script: How does one invoke SQL*Plus, pass a shell variable
to run a stored procedure ?
i.e. if you have a UNIX variable $my_table (= dept)
how do you invoke sql*plus and pass $my_table to a procedure
which you'd otherwise manually run in sql*plus as
SQL> exec check_table('dept')
Thanks very much !
Robert Chin
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Robert Chin
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: How to compare files on the server with local machine
Who's Gugu ? > ...it should compare the local files with the files > on the server(s) >.SO the local machine will get updated also. Are we talking about a one-to-many thing here ? You want to compare files on server(s) to files on ALL client boxes ? BTW, what's this got to do with Oracle ? am i missing something ? - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Wednesday, November 28, 2001 3:07 PM > Hi, Gugu: > > We have application installed on the server, and we > also have fat client installed on everyone's local > machine. But files on the server sometimes got updates > by anonymous without notified. So, we want to have a > batch file to do the comparison and copy funtions. > Every time the batch file runs, it should compare the > local files with the files on the server(s), if a > change finds, it will copy the new file from server to > local machine. SO the local machine will get updated > also. > > I want this to be dynamic, since there are more than > 800 files to compare. And this could be happened on > any time. > > Do you have a clue where/how should I start this? a > sample? > > Thanks in advance. > > Wendy -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Robert Chin 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: Need Help Please - With Procedures
WOW ! all those new column names wth quirky names.
Well here is the procdure. make sure you got the GRANTs right
in order to compile it.
Also check to make sure I got the your_variable/my_variable match right.
hth
robert chin
CREATE OR REPLACE PROCEDURE table_fragmentation_info
( v_table IN VARCHAR2,
v_owner IN VARCHAR2
)
IS
v_num_rows dba_tables.num_rows%TYPE;
v_hwm_blocks dba_tables.blocks%TYPE;
v_above_hwm dba_tables.empty_blocks%TYPE;
v_row_chains dba_tables.chain_cnt%TYPE;
v_row_size dba_tables.avg_row_len%TYPE;
v_pct_used dba_tables.pct_used%TYPE;
v_pct_free dba_tables.pct_free%TYPE;
v_row_chains_pct NUMBER;
v_block_size v$parameter.value%TYPE;
v_blocks_alloc NUMBER;
v_bytes_alloc NUMBER;
v_hwm_bytesNUMBER;
v_bytes_used NUMBER;
v_blocks_pct_used NUMBER;
v_bytes_pct_used NUMBER;
v_sf INTEGER;
BEGIN
select num_rows,
blocks,
empty_blocks,
chain_cnt,
avg_row_len,
pct_used,
pct_free,
100*chain_cnt/num_rows
INTO
v_num_rows,
v_hwm_blocks,
v_above_hwm,
v_row_chains,
v_row_size,
v_pct_used,
v_pct_free,
v_row_chains_pct
from dba_tables
where table_name = upper(v_table)
and owner = upper(v_owner);
-
select value INTO v_block_size
FROM v$parameter WHERE name = 'db_block_size';
--
v_blocks_alloc := (v_hwm_blocks + v_above_hwm);
v_bytes_alloc := (V_hwm_blocks + v_above_hwm)* v_block_size/1024/1024;
v_hwm_bytes := (v_hwm_blocks * v_block_size)/1024/1024;
v_bytes_used := (v_num_rows * v_row_size)/1024/1024;
--
v_blocks_pct_used := 100 * v_hwm_blocks / v_blocks_alloc;
v_bytes_pct_used := 100 * v_num_rows * v_row_size / v_hwm_bytes/1024/1024;
--
select count(*) INTO v_sf
from dba_extents
where segment_name= upper(v_table)
and owner = upper(v_owner);
--
dbms_output.put_line('Owner : '||v_owner);
dbms_output.put_line('Table name : '||v_table);
dbms_output.put_line('pct_free : '||v_pct_free);
dbms_output.put_line('pct_used : '||v_pct_used);
dbms_output.put_line('Number of extents : '||v_sf||' <-- Segment
Fragmentation');
dbms_output.put_line('Rows : '||v_num_rows);
dbms_output.put_line('Row size : '||v_row_size);
dbms_output.put_line('Rows frag:migration: '||v_row_chains);
dbms_output.put_line('Row % frag:migr. : '||v_row_chains_pct||'% <-- Row
Fragmentation');
dbms_output.put_line('DB block size : '||v_block_size);
dbms_output.put_line('Blocks alloc : '||v_blocks_alloc);
dbms_output.put_line('Block HWM : '||v_hwm_blocks);
dbms_output.put_line('% alloc used by HWM: '||v_blocks_pct_used||'%');
dbms_output.put_line('MB alloc : '||v_bytes_alloc||'MB');
dbms_output.put_line('MB HWM : '||v_hwm_bytes||'MB');
dbms_output.put_line('MB used: '||v_bytes_used||'MB');
dbms_output.put_line('% HWM bytes used : '||v_bytes_pct_used||'% <-- Block
Fragmentation');
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(CHR(10)||'Are you sure you entered FIRST TABLE NAME THEN
OWNER NAME correctly ?');
END;
/
- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Wednesday, November 28, 2001 2:05 PM
> Hello all,
>
> I have attached an sql file containing a set of sql's (6) of them, which gives
me information regarding table fragmentation. What I need to do is instead of
writing seperate sql's, I need to write a procedure, where in I pass the owner
and table name and then the result comes out, as you will see in the last sql.
>
> What I need is, if some one could please help in writing a procedure? How to
put cursors and use all the information in different cursor variables etc.
>
> Please help.
>
> Thanks.
>
> Rgds,
>
> Raja
>
>
>
>
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Robert Chin
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).
