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