show_space

CREATE OR REPLACE PROCEDURE SYS.SHOW_SPACE(p_segname_1 IN VARCHAR2,
                                       p_space     IN VARCHAR2 DEFAULT 'AUTO',
                                       p_type_1    IN VARCHAR2 DEFAULT 'TABLE',
                                       p_analyzed  IN VARCHAR2 DEFAULT 'N',
                                       p_owner_1   IN VARCHAR2 DEFAULT USER) AS
  p_segname VARCHAR2(100);
  p_type    VARCHAR2(10);
  p_owner   VARCHAR2(30);
  l_unformatted_blocks NUMBER;
  l_unformatted_bytes  NUMBER;
  l_fs1_blocks         NUMBER;
  l_fs1_bytes          NUMBER;
  l_fs2_blocks         NUMBER;
  l_fs2_bytes          NUMBER;
  l_fs3_blocks         NUMBER;
  l_fs3_bytes          NUMBER;
  l_fs4_blocks         NUMBER;
  l_fs4_bytes          NUMBER;
  l_full_blocks        NUMBER;
  l_full_bytes         NUMBER;
  l_free_blks          NUMBER;
  l_total_blocks       NUMBER;
  l_total_bytes        NUMBER;
  l_unused_blocks      NUMBER;
  l_unused_bytes       NUMBER;
  l_LastUsedExtFileId  NUMBER;
  l_LastUsedExtBlockId NUMBER;
  l_LAST_USED_BLOCK    NUMBER;
  PROCEDURE p(p_label IN VARCHAR2,
              p_num   IN NUMBER) IS
  BEGIN
    dbms_output.put_line(rpad(p_label, 40, '.') || p_num);
  END;

BEGIN
  p_segname := upper(p_segname_1); -- rainy changed
  p_owner   := upper(p_owner_1);
  p_type    := p_type_1;
  IF (p_type_1 = 'i' OR p_type_1 = 'I') THEN
    --rainy changed
    p_type := 'INDEX';
  END IF;
  IF (p_type_1 = 't' OR p_type_1 = 'T') THEN
    --rainy changed
    p_type := 'TABLE';
  END IF;
  IF (p_type_1 = 'c' OR p_type_1 = 'C') THEN
    --rainy changed
    p_type := 'CLUSTER';
  END IF;
  dbms_space.unused_space(segment_owner => p_owner, segment_name => p_segname, segment_type => p_type,
                          total_blocks => l_total_blocks, total_bytes => l_total_bytes, unused_blocks => l_unused_blocks,

                          unused_bytes => l_unused_bytes, LAST_USED_EXTENT_FILE_ID => l_LastUsedExtFileId,
                          LAST_USED_EXTENT_BLOCK_ID => l_LastUsedExtBlockId, LAST_USED_BLOCK => l_LAST_USED_BLOCK);
  IF p_space = 'MANUAL' OR (p_space <> 'auto' AND p_space <> 'AUTO') THEN
    dbms_space.free_blocks(segment_owner => p_owner, segment_name => p_segname, segment_type => p_type,
                           freelist_group_id => 0, free_blks => l_free_blks);
    p('Free Blocks', l_free_blks);
  END IF;
  p('Total Blocks', l_total_blocks);
  p('Total Bytes', l_total_bytes);
  p('Unused Blocks', l_unused_blocks);
  p('Unused Bytes', l_unused_bytes);
  p('Last Used Ext FileId', l_LastUsedExtFileId);
  p('Last Used Ext BlockId', l_LastUsedExtBlockId);
  p('Last Used Block', l_LAST_USED_BLOCK);
  /*IF the segment is analyzed */
  IF p_analyzed = 'Y' THEN
    dbms_space.space_usage(segment_owner => p_owner, segment_name => p_segname, segment_type => p_type,
                           unformatted_blocks => l_unformatted_blocks, unformatted_bytes => l_unformatted_bytes,
                           fs1_blocks => l_fs1_blocks, fs1_bytes => l_fs1_bytes, fs2_blocks => l_fs2_blocks,
                           fs2_bytes => l_fs2_bytes, fs3_blocks => l_fs3_blocks, fs3_bytes => l_fs3_bytes,
                           fs4_blocks => l_fs4_blocks, fs4_bytes => l_fs4_bytes, full_blocks => l_full_blocks,
                           full_bytes => l_full_bytes);
    dbms_output.put_line(rpad(' ', 50, '*'));
    dbms_output.put_line('The segment is analyzed');
    p('0% -- 25% free space blocks', l_fs1_blocks);
    p('0% -- 25% free space bytes', l_fs1_bytes);
    p('25% -- 50% free space blocks', l_fs2_blocks);
    p('25% -- 50% free space bytes', l_fs2_bytes);
    p('50% -- 75% free space blocks', l_fs3_blocks);
    p('50% -- 75% free space bytes', l_fs3_bytes);
    p('75% -- 100% free space blocks', l_fs4_blocks);
    p('75% -- 100% free space bytes', l_fs4_bytes);
    p('Unused Blocks', l_unformatted_blocks);
    p('Unused Bytes', l_unformatted_bytes);
    p('Total Blocks', l_full_blocks);
    p('Total bytes', l_full_bytes);
  END IF;
END;
/

GRANT EXECUTE ON show_space TO PUBLIC;
CREATE OR REPLACE Public SYNONYM  show_space FOR SYS.Show_Space;

remove old mysql and install new mysql on rhel 5

[g]# rpm -q cyrus-sasl
cyrus-sasl-2.1.19-14
cyrus-sasl-2.1.19-14
[g]# rpm -qa | grep cyrus
cyrus-sasl-2.1.19-14
cyrus-sasl-sql-2.1.19-14
cyrus-sasl-md5-2.1.19-14
cyrus-sasl-plain-2.1.19-14
cyrus-sasl-ntlm-2.1.19-14
cyrus-sasl-md5-2.1.19-14
cyrus-sasl-devel-2.1.19-14
cyrus-sasl-gssapi-2.1.19-14
cyrus-sasl-2.1.19-14
cyrus-sasl-plain-2.1.19-14
[g]# rpm -e cyrus-sasl-sql
[g]# ls
MySQL-server-5.5.15-1.linux2.6.x86_64.rpm  mysql-5.5.15-linux2.6-x86_64.tar.gz
MySQL-server-5.5.15-1.rhel4.x86_64.rpm
[g]# rpm -Uvh MySQL-server-5.5.15-1.rhel4.x86_64.rpm
警告: MySQL-server-5.5.15-1.rhel4.x86_64.rpm: V3 DSA signature: NOKEY, key ID 5072e1f5
エラー: Failed dependencies:
        MySQL conflicts with mysql-4.1.22-2.el4.i386
[g]# rpm -e mysql
[g]# rpm -Uvh MySQL-server-5.5.15-1.rhel4.x86_64.rpm
警告: MySQL-server-5.5.15-1.rhel4.x86_64.rpm: V3 DSA signature: NOKEY, key ID 5072e1f5
Preparing...                ########################################### [100%]
   1:MySQL-server           ########################################### [100%]

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:

/usr/bin/mysqladmin -u root password 'new-password'
/usr/bin/mysqladmin -u root -h td-agtmf-t01 password 'new-password'

Alternatively you can run:
/usr/bin/mysql_secure_installation

which will also give you the option of removing the test
databases and anonymous user created by default.  This is
strongly recommended for production servers.

See the manual for more instructions.

Please report any problems with the /usr/bin/mysqlbug script!

Notes regarding SELinux on this platform:
=========================================

The default policy might cause server startup to fail because it is
not allowed to access critical files.  In this case, please update
your installation.

The default policy might also cause inavailability of SSL related
features because the server is not allowed to access /dev/random
and /dev/urandom. If this is a problem, please do the following:

  1) install selinux-policy-targeted-sources from your OS vendor
  2) add the following two lines to /etc/selinux/targeted/src/policy/domains/program/mysqld.te:
       allow mysqld_t random_device_t:chr_file read;
       allow mysqld_t urandom_device_t:chr_file read;
  3) cd to /etc/selinux/targeted/src/policy and issue the following command:
       make load

Unix ODBC

install Unix ODBC:

./configure --prefix=/usr/local/unixODBC-2.3.0 --includedir=/usr/include --libdir=/usr/lib -bindir=/usr/bin --sysconfdir=/etc

make
make install

オラクルパーティションとアルゴリズムの検証

最近オラクルのパーティションの置換に気になって、検証しました。
・パーティションはWHERE条件に合っていれば、自動的に使われる
・exchange partition without validationはデータの保障を一切しない
・場合によって実行の文は正しい値が戻せない

SQL> drop table hist_part_list;

表が削除されました。

SQL> create table hist_part_list (cust_no varchar2(12), trade_code number(3), call_datetime date, memo varchar2(20),
  2  constraint hist_part_list_pk primary key (cust_no, trade_code, call_datetime) using index local
  3  )
  4  partition by list(trade_code) (
  5  partition hist_part_list_01 values (1, 11),
  6  partition hist_part_list_02 values (2, 12)
  7  );

表が作成されました。

SQL> insert into hist_part_list values('000000000001', 1, to_date('2011/01/01','YYYY/MM/DD'), 'test');

1行が作成されました。

SQL> insert into hist_part_list values('000000000002', 2, to_date('2010/01/01','YYYY/MM/DD'), 'test');

1行が作成されました。

SQL> insert into hist_part_list values('000000000001', 1, to_date('2011/01/02','YYYY/MM/DD'), 'test');

1行が作成されました。

SQL> insert into hist_part_list values('000000000002', 2, to_date('2010/01/02','YYYY/MM/DD'), 'test');

1行が作成されました。

SQL> insert into hist_part_list values('000000000001', 1, to_date('2011/01/03','YYYY/MM/DD'), 'test');

1行が作成されました。

SQL> insert into hist_part_list values('000000000002', 2, to_date('2010/01/03','YYYY/MM/DD'), 'test');

1行が作成されました。

SQL> commit;

コミットが完了しました。

SQL>
SQL> insert into hist_part_list values('000000000001', 1, to_date('2012/01/01','YYYY/MM/DD'), 'test');

1行が作成されました。

SQL> insert into hist_part_list values('000000000002', 2, to_date('2012/01/01','YYYY/MM/DD'), 'test');

1行が作成されました。

SQL> insert into hist_part_list values('000000000001', 1, to_date('2012/01/02','YYYY/MM/DD'), 'test');

1行が作成されました。

SQL> insert into hist_part_list values('000000000002', 2, to_date('2012/01/02','YYYY/MM/DD'), 'test');

1行が作成されました。

SQL> insert into hist_part_list values('000000000001', 1, to_date('2012/01/03','YYYY/MM/DD'), 'test');

1行が作成されました。

SQL> insert into hist_part_list values('000000000002', 2, to_date('2012/01/03','YYYY/MM/DD'), 'test');

1行が作成されました。

SQL> commit;

コミットが完了しました。

SQL> drop table hist_part_rng_list;

表が削除されました。

SQL> create table hist_part_rng_list (cust_no varchar2(12), trade_code number(3), call_datetime date, memo varchar2(20),

  2  constraint hist_part_rng_list_pk primary key (cust_no, trade_code, call_datetime) using index local
  3  )
  4  partition by range(call_datetime) subpartition by list(trade_code) (
  5     partition hist_part_rng_list_10 values less than (to_date('2012/01/01','YYYY/MM/DD')) (
  6             subpartition hist_part_rng_list_11_01 values(1, 11),
  7             subpartition hist_part_rng_list_11_02 values(2, 12)
  8     ),
  9     partition hist_part_rng_list_11 values less than (to_date('2013/01/01','YYYY/MM/DD')) (
 10             subpartition hist_part_rng_list_12_01 values(1, 11),
 11             subpartition hist_part_rng_list_12_02 values(2, 12)
 12     )
 13  );

表が作成されました。

SQL> drop table hist_temp;

表が削除されました。

SQL> create table hist_temp (cust_no varchar2(12), trade_code number(3), call_datetime date, memo varchar2(20),
  2  constraint hist_temp_pk primary key (cust_no, trade_code, call_datetime)
  3  );

表が作成されました。

SQL> alter table hist_part_list exchange partition hist_part_list_01
  2  with table hist_temp
  3  including indexes without validation;

表が変更されました。

SQL> alter table hist_part_rng_list exchange subpartition hist_part_rng_list_11_01
  2  with table hist_temp
  3  including indexes without validation;

表が変更されました。

SQL> select * from hist_part_rng_list;

CUST_NO      TRADE_CODE CALL_DAT MEMO
------------ ---------- -------- --------------------
000000000001          1 11-01-01 test
000000000001          1 11-01-02 test
000000000001          1 11-01-03 test
000000000001          1 12-01-01 test
000000000001          1 12-01-02 test
000000000001          1 12-01-03 test

6行が選択されました。

SQL> alter table hist_part_list exchange partition hist_part_list_02
  2  with table hist_temp
  3  including indexes without validation;

表が変更されました。

SQL> alter table hist_part_rng_list exchange subpartition hist_part_rng_list_11_02
  2  with table hist_temp
  3  including indexes without validation;

表が変更されました。

SQL> select * from hist_part_rng_list;

CUST_NO      TRADE_CODE CALL_DAT MEMO
------------ ---------- -------- --------------------
000000000001          1 11-01-01 test
000000000001          1 11-01-02 test
000000000001          1 11-01-03 test
000000000001          1 12-01-01 test
000000000001          1 12-01-02 test
000000000001          1 12-01-03 test
000000000002          2 10-01-01 test
000000000002          2 10-01-02 test
000000000002          2 10-01-03 test
000000000002          2 12-01-01 test
000000000002          2 12-01-02 test
000000000002          2 12-01-03 test

12行が選択されました。

SQL> set autot on explain;
SQL> select * from hist_part_rng_list a where trade_code = 1 and call_datetime > to_date('2012/01/01','yyyy/mm/dd');

レコードが選択されませんでした。

実行計画
----------------------------------------------------------
Plan hash value: 3952932626

-------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name               | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |                    |     1 |    42 |     2   (0)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE SINGLE|                    |     1 |    42 |     2   (0)| 00:00:01 |     2 |     2 |
|   2 |   PARTITION LIST SINGLE|                    |     1 |    42 |     2   (0)| 00:00:01 |   KEY |   KEY |
|*  3 |    TABLE ACCESS FULL   | HIST_PART_RNG_LIST |     1 |    42 |     2   (0)| 00:00:01 |     3 |     3 |
-------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("TRADE_CODE"=1 AND "CALL_DATETIME">TO_DATE(' 2012-01-01 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss'))

Note
-----
   - dynamic sampling used for this statement

SQL>

generate table row count statistics script

generate table row count statistics script. (should delete first 'union all')
with create table, we needn't worry about the interruption and rollback. it doesn't lock any table or row, neither generate overload on rollback processing.

set echo off;
set verify off;
set feedback off;
spool table_stat_script.sql;

select 'create table table_stats as' from dual;
select 'union all select /*+ parallel(a) */'''||(select name from v$database)||''' db_name, '''||owner||''' owner,'''||table_name||''' table_name, count(*) cnt from '||owner||'.'||table_name||' a'

from dba_tables where owner = '&owner' order by owner, table_name;

spool off;

@table_stat_script.sql