SQL：全テーブルの現在のデータ数を知りたい。

環境
DBサーバ ： Linux　+ Oracle9i　
クライアント ： WindowsXP + Oracle10g の SQLPlus(w)

当方SQL初心者です。以下のSelectを行うSQL文がわかりません。（色々悪戦苦闘しましたが）
どなたかご教授下さい。

条件指定する TABLESPACE_NAME に属する、全テーブルの現在のデータ数を、
各テーブルに comment on したコメントと一緒に表示するSQLを教えて下さい。
Select 結果のイメージは以下の感じです。

TABLESPACE_NAMETABLE_NAMECount(*)　　　Comments
------------------------------------------- --------------------

条件としては、
　(1) where TABLESPACE_NAME like 'xxxx%'
です。
そして TABLESPACE_NAME が異なれば、同じ TABLE_NAME のテーブルがありますが
(1)の条件に合致する全てのテーブルを Select したいということです。

すみませんが、お分かりの方、宜しくお願い致します。

No.3 回答者： muyoshid 回答日時： 2012/05/17 20:43

こんにちわ。

> やはりSQL１文ではチョット無理なようですね。
SQL 文1つでは無理ですが、ちょっと工夫すればこんな感じでできます。

SQL> select owner, count(*) from dba_tables
where tablespace_name = 'SYSAUX' group by owner;

OWNER COUNT(*)
--------------- ----------
SYSTEM 24
DBSNMP 16
APPQOSSYS 4
SYS 297

上記のような状態で以下のようなスクリプト (count.sql) を用意します。

[count.sql]
--------------------------------------------------------------------------------
set pages 1000
set lines 180
set echo off
set feedback off
set heading off
set verify off
set termout off
set trimspool on
col sql_txt format a170
col cmd format a100
spool count1.sql
select 'set pages 100' as cmd,
'set lines 100' as cmd,
'set echo off' as cmd,
'set feedback off' as cmd,
'col owner format a15' as cmd,
'col table_name format a30' as cmd,
'col tablespace_name format a15' as cmd,
'col cnt format 99999999' as cmd
from dual;

select 'select ' || chr(39) || owner || chr(39) || ' as owner, ' ||
chr(39) || table_name || chr(39) || ' as table_name, ' ||
chr(39) || tablespace_name || chr(39) || ' as tablespace_name, ' ||
'count(*) as cnt from ' || owner || '.' || table_name || ';' as sql_txt
from dba_tables where owner = user and tablespace_name = upper('&1')
order by table_name;

select 'exit' as cmd from dual;
spool off

set heading on
set termout on
@count1.sql
exit
--------------------------------------------------------------------------------
これを、以下のように実行してやると SYSTEM スキーマ (ログイン名) が
所有者でSYSAUX 表領域 (パラメータ) にあるテーブルの一覧が取得できます。

$ sqlplus system/manager @count.sql sysaux

最終的に実行しているSQL は、count1.sql (count.sql で作成しています) を
確認して下さい。

この回答へのお礼

muyoshidさん。
再度の回答、ありがとうございます。
またお礼の返事が遅くなってすみません。

なるほど、スクリプトからSQLのテキストを出力して実行させるのですね。
初めて知りましたし、大変参考になりました。
前回教えて頂いた NUM_ROWS でなんとかやっていましたが
こちらのスクリプトの方法に変更しようと思います。

いつもお世話になり、ありがとうございました。

お礼日時：2012/05/19 03:11

No.2 回答者： muyoshid 回答日時： 2012/05/17 07:51

こんにちわ。

> create table Def_01.DEVICE_TBL
> (
> ・・・・中略・・・・
> ) tablespace Def_01 ;

Create table で指定している「Def_01.DEVICE_TBL」ですが、
SQL の構文では[スキーマ名].テーブル名 となります。
以下に、Oracle9.2 のSQL リファレンスがあるので確認してみて下さい。
http://otndnld.oracle.co.jp/document/oracle9i/92 …

参考URL：http://otndnld.oracle.co.jp/document/oracle9i/92 …

この回答へのお礼

muyoshidさん。
再度のご指導、ありがとうございます。
確かにご指摘の通りです。

またまた説明漏れで申し訳ありません。
補足致しますと、別途下記SQLで Oracleユーザーを作成しています。

create user Def_01
identified by xxxxxx
default tablespace Def_01
temporary tablespace DEF_01Tmp ;

紛らわしくてすみません。
今後とも宜しくお願い致します。

お礼日時：2012/05/17 08:58

No.1 回答者： muyoshid 回答日時： 2012/05/17 01:39

こんにちわ。

> 条件指定する TABLESPACE_NAME に属する、全テーブルの現在のデータ数を、
> 各テーブルに comment on したコメントと一緒に表示するSQLを教えて下さい。
単純なSQL では難しいですね。
やるとすれば、以下の2つの方法がありますが。
1) テーブルをAnalyze して、ALL_TABLES.NUM_ROWS から件数を取得する。
2) ALL_TABLES から欲しい表領域に属するテーブル全てを
COUNT(*) するスクリプトを作成して実行する。

きちんとAnalyze していて、多少件数がブレても問題ないのであれば、
ALL_TABLES.NUM_ROWS から件数を取得するのが楽ですね。

> TABLESPACE_NAME が異なれば、同じ TABLE_NAME のテーブルがありますが
これは、Partitioning の事でしょうか？
Partitioning であれば、表領域は異なる必要はありません。
スキーマが同じであれば、表領域が異なる同名のテーブルは作成できません。

この回答へのお礼

muyoshidさん。
早速の回答、ありがとうございます。

やはりSQL１文ではチョット無理なようですね。
なるほど、Analize して、ALL_TABLES.NUM_ROWS から取得するという方法ですね。
概略の件数でOKなので、これが簡単なような気がします。早速明日試してみます。

> TABLESPACE_NAME が異なれば、同じ TABLE_NAME のテーブルがありますが．．．
とは、Partitioning のような高度な機能ではなく、単に世代管理のように、同じテーブル名で、
複数のテーブルスペースに作表しています。
例として、create tablespace と createテーブルの SQL文を下記に掲載しますと。

create tablespace Def_01
・・・・中略・・・・
segment space management auto ;

create tablespace Def_02
・・・・中略・・・・
segment space management auto ;

----------------------------------

create table Def_01.DEVICE_TBL
(
・・・・中略・・・・
) tablespace Def_01 ;

create table Def_02.DEVICE_TBL
(
・・・・中略・・・・
) tablespace Def_02 ;

のような感じです。
説明不足ですみません。　

お礼日時：2012/05/17 03:31