Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> I need your all suggestions! Please give me suggestions.

I need your all suggestions! Please give me suggestions.

From: Xiaoliang Chen <ochen_at_netrue.com>
Date: Tue, 11 Jul 2000 14:57:26 -0700
Message-Id: <10555.111733@fatcity.com> 





hi, all,



I decide to pass my OCP DBA in two months. But I do not if I directly choose
Oracle8i test package or choose Oracle8 test package and then have a
Oracle8i new features test.



One of my friend said this to me:


Let me tell you why it is not good to do oracle 8i at the moment.
1. It will cost you a lot since no one has the stuff for oracle 8i and is
selling it on the net.


2. the  market will not revert to oracle 8i before a couple of years,
because mosst companies recently migrated from oracle 7.3 to oracle 8
3. if you do oracle 8 and give the upgrade exam you will have two
certification. oracle 8 dba and 8i dba.



I am afraid I could hardly get Exam crams about Oracle8i at this time. Maybe
I should first pass the Oracle8 and then pass the upgrate test.
But someone tell me one must pass the upgrate test in 6 months from the day
the new version was coming up. Which mean if Oracle8i was coming up on
01/01/00, I have to pass the upgrate test from Oracle8 to Oracle8i before
07/01/00. That mean I could not the upgrate test.....I am so
confused....help me!


What is your all 's suggestion? Please give me a reply. Your reply will be
fully appreciated.



Best wishes!


Yours sincerely, Xiaoliang Chen




SUN certified Solaris7 System Administrator



Day phone: 1-714-870-0861 x130


Home phone: 1-714-738-3720



Mail:


1400 N. Harbor Blvd., Suite 601


Fullerton, CA 92835



My Homepage:


http://www.geocities.com/earnmuchmoney


----- Original Message -----


From: "Ron Rogers" <RROGERS_at_galottery.org>
To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
Sent: Tuesday, July 11, 2000 12:20 PM


Subject: RE: Free space for all tablespaces




Tom,


Listed below are the 4 scripts that I run to give me an indication of the
database and space usage. It is run by the "DAILY.SQL" script and stirred in
my "C:" drive. Adjust the parameters accordingly. The tsstats.sql will list
the instance that I am reporting on by the name the database is given.

¯---------------------




¯DAILY.SQL


¯----------------------




Rem daily sql scripts run to report on table space conditions. File
c:\glcsql\daily.sql


Spool  c:\glcsql\rorreps


@c:\glcsql\tsstats;



ttitle off


@c:\glcsql\tsfree;



ttitle off


@c:\glcsql\tsdetail;



ttitle off


@c:\glcsql\tswarn;



ttitle off


spool off


¯-------------------------




 TSSTATS.SQL


¯---------------------------




Rem listing of the table space statistics. File c:\glcsql\tsstats.sql
set feedback off;


set pagesize 60;


set head OFF;


column today noprint new_value xtoday


Select to_char(sysdate,'MM/DD/YYYY"  " HH24:MI:SS') today from dual;
SELECT DECODE (VALUE,'oracle','REPORT FOR GLC_DEVELOPMENT_411 server',

'oracle1','REPORT FOR OLD_DATABASE',
'oracle2','REPORT FOR GLC_DATABASE server',
'finance1','REPORT FOR GLC_APPLICATION server','REPORT FOR UNLISTED')


FROM V$PARAMETER WHERE NAME='db_name';


TTITLE LEFT


'---------------------------------------------------------------------------
-------------------' -




SKIP CENTER xtoday -


SKIP CENTER   '*********** DATA FILE STATISTICS ***********' -

SKIP LEFT


'---------------------------------------------------------------------------
-------------------'




set head on;


column tablespace_name format a25 heading 'TS NAME'
column bytes format 9,999,999,999


column file_name format a35


select tablespace_name,bytes,status,file_name
from dba_data_files


order by tablespace_name;

¯---------------------------




TSFREE.SQL


¯--------------------------




Rem listing of the free table space. File c:\glcsql\tsfree.sql



set feedback off;


set pagesize 60;


set head on;


column today noprint new_value xtoday


Select to_char(sysdate,'MM/DD/YYYY"  "HH24:MI:SS') today from dual;
TTITLE LEFT


'---------------------------------------------------------------------------
-------------------' -




SKIP center xtoday -


SKIP CENTER   '*********** TABLESPACE FREE SPACE ***********' -

SKIP LEFT


'---------------------------------------------------------------------------
-------------------'




column tablespace_name format a25 heading 'TS NAME'
column sum(bytes) format 9,999,999,999 heading 'bytes free'
column max(bytes) format 9,999,999,999 heading 'max bytes'



select tablespace_name,sum(bytes),max(bytes)
from dba_free_space


group by tablespace_name;

¯-------------------------




TSDETAIL.SQL


¯----------------------




Rem listing of the table space information. File c:\glcsql\tsdetail.sql
set feedback off;


set pagesize 256;


set head on;


column today noprint new_value xtoday


Select to_char(sysdate,'MM/DD/YYYY"  " HH24:MI:SS') today from dual;
TTITLE LEFT


'---------------------------------------------------------------------------
-------------------' -




SKIP CENTER    xtoday -


SKIP CENTER   'TABLESPACE SEGMENTS DETAIL LISTING BY SEGMENT TYPE,

TABLESPACE, SEGMENT NAME' -



SKIP LEFT


'---------------------------------------------------------------------------
-------------------'




column segment_name format a30


column segment_type format a6 heading TYPE
column tablespace_name format a25 heading 'TS NAME'
column extents format 999 heading EXTS


column bytes format 9,999,999,999


column blocks format 9,999,999


select segment_name,segment_type,tablespace_name,
extents,bytes,blocks from dba_segments


where tablespace_name != 'SYSTEM'


and owner not in ('SYS','SYSTEM')


order by segment_type,tablespace_name,segment_name;

¯------------------------




TSWARN.SQL


¯-------------------------




Rem listing of the table space segments greater than 3 extents. File
c:\glcsql\tswarn.sql



set feedback off;


set pagesize 60;


set head on;


column today noprint new_value xtoday


Select to_char(sysdate,'MM/DD/YYYY"  " HH24:MI:SS') today from dual;
TTITLE LEFT


'---------------------------------------------------------------------------
-------------------' -




SKIP CENTER xtoday -


SKIP CENTER   'TABLESPACE SEGMENTS WITH MORE THAN 3 EXTENTS' -

SKIP LEFT


'---------------------------------------------------------------------------
-------------------'




column segment_name format a30


column segment_type format a6 heading TYPE
column tablespace_name format a25 heading 'TS NAME'
column extents format 999 heading EXTS


column bytes format 9,999,999,999


column blocks format 9,999,999


select segment_name,segment_type,tablespace_name,
extents,bytes,blocks from dba_segments


where tablespace_name != 'SYSTEM'


and owner not in ('SYS','SYSTEM') and


extents > 3


order by segment_type,tablespace_name,segment_name;

¯----------------------------------------------------





Hope this will help in your quest.


Ron Rogers


DBA



ATL.GA


>>> blair_at_pjm.com 07/11/00 12:05PM >>>


Thanks for the reply but...  I have tried this.  For some reason that I
don't


understand it seems to fail when the tablespace has more than 1 datafile.
It


does not generate an error - it just gives the wrong answer - really weird.



thanks anyway,



..tom







> -----Original Message-----
> From: Suhen Pather [SMTP:pathers5_at_telkom.co.za]
> Sent: Tuesday, July 11, 2000 10:58 AM
> To: Multiple recipients of list ORACLE-L
> Subject: Re: Free space for all tablespaces
>
> You can try this it works
>
> select a.tablespace_name, sum(a.bytes/1024/1024) "Allocated" ,
> sum(b.bytes/1024/1024) "Free Space" from sys.dba_data_files a,
> sys.dba_free_space b
> where a.tablespace_name = b.tablespace_name
> group by a.tablespace_name
> /
>
> Regards
> $uhen
> Oracle DBA
> Telkom SA
>
>
> >>> blair_at_pjm.com 07/11/00 03:56PM >>>
> I just want a SQL query to give me the freespace in all tablespaces.  This
> doesn't work:
>
> select a.tablespace_name, sum(a.bytes) TOTAL_SPACE, sum(b.bytes)a


FREE_SPACE


> from sys.dba_data_files a, sys.dba_free_space b
> where a.tablespace_name = b.tablespace_name
> group by 1;
>
> Does anyone have a query that does work??
>
> thanks,
>
> ..tom
>
>
>
> --
> Author:
>   INET: blair_at_pjm.com
>
> 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: ListGuru_at_fatcity.com (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).
>
> --
> Author: Suhen Pather
>   INET: pathers5_at_telkom.co.za
>
> 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: ListGuru_at_fatcity.com (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).



--
Author:
  INET: blair_at_pjm.com

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: ListGuru_at_fatcity.com (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).
--
Author: Ron Rogers
  INET: RROGERS_at_galottery.org

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


Received on Tue Jul 11 2000 - 16:57:26 CDT












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US