oracle01

--sqlplus noboru01/noboru01 @test0003.sql

column log_date new_value log_date_text noprint
select to_char(sysdate,'yyyymmdd') log_date from dual;


spool hoge_&log_date_text._temp.log

set timing on
set echo on
set autotrace off
set linesize 200
set trimspool on
--
drop table tbl01 cascade constraints purge;

create table tbl01
(
  id1 char(8) ,
  txt varchar2(20),
  primary key( id1 )
);
declare
vID1     char(8);
--vText   varchar2(250);
begin
for i in 1..5000000
--for i in 1..100
loop
     vID1 := to_char(i, 'FM00000000');
     insert into tbl01 (id1, txt) values (vID1, 'AAAAA');
end loop;
commit;
end;
/

--select count(*) from tbl01;
--
drop table tbl02 cascade constraints purge;
create table tbl02 as select * from tbl01;
truncate table tbl02;
insert into tbl02 select * from tbl01;

exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'NOBORU01',TABNAME => 'TBL01',CASCADE => TRUE);

drop table tbl02 cascade constraints purge;
create table tbl02 as select * from tbl01;
truncate table tbl02;
insert into tbl02 select * from tbl01;

truncate table tbl02;
ALTER TABLE tbl02 ADD CONSTRAINT tbl02_pk PRIMARY KEY (id1) ;
insert into tbl02 select * from tbl01;


--
SELECT TABLE_NAME tbl, LAST_ANALYZED FROM USER_TABLES ;
SELECT TABLE_NAME tbl, INDEX_NAME idx, LAST_ANALYZED FROM USER_INDEXES ;
SELECT TABLE_NAME tbl, COLUMN_NAME, LAST_ANALYZED FROM USER_TAB_COLUMNS;
--
SELECT
  TABLE_NAME,
  NUM_ROWS,
  AVG_ROW_LEN,
  BLOCKS,
  EMPTY_BLOCKS,
  CASE AVG_ROW_LEN
    WHEN null THEN null
    WHEN 0 THEN 0
    ELSE ( CEIL( NUM_ROWS / TRUNC( ( ( BLOCK_SIZE - KCBH - UB4 - KTBBH
      - ( INI_TRANS - 1 ) * KTBIT - KDBH )
      * ( 100 - PCT_FREE ) / 100 - KDBT ) / AVG_ROW_LEN ) ) + 1 )
  END EST_BLOCKS
  FROM USER_TABLES
    ,(SELECT TYPE_SIZE KCBH  FROM V$TYPE_SIZE WHERE TYPE = 'KCBH')
    ,(SELECT TYPE_SIZE UB4   FROM V$TYPE_SIZE WHERE TYPE = 'UB4')
    ,(SELECT TYPE_SIZE KTBBH FROM V$TYPE_SIZE WHERE TYPE = 'KTBBH')
    ,(SELECT TYPE_SIZE KTBIT FROM V$TYPE_SIZE WHERE TYPE = 'KTBIT')
    ,(SELECT TYPE_SIZE KDBH  FROM V$TYPE_SIZE WHERE TYPE = 'KDBH')
    ,(SELECT TYPE_SIZE KDBT  FROM V$TYPE_SIZE WHERE TYPE = 'KDBT')
    ,(SELECT VALUE     BLOCK_SIZE FROM V$PARAMETER WHERE NAME = 'db_block_size')
  ORDER BY BLOCKS DESC;

--
--column log_block_size new_value log_block_size_txt noprint
--select VALUE log_block_size from v$parameter where name in ( 'db_block_size' );

--show parameter db_block_size
SELECT TABLE_NAME,((BLOCKS*BLOCK_SIZE )/1024/1024) AS "SIZE(M)"
FROM USER_TABLES
    ,(SELECT VALUE     BLOCK_SIZE FROM V$PARAMETER WHERE NAME = 'db_block_size')
;
select segment_name , sum(bytes)/1024/1024 from user_segments group by segment_name;
--
set lines 120
set pages 100
set term off
tti off
clear col
col TABLESPACE_NAME    format a15
col "SIZE(KB)"          format a20
col "USED(KB)"          format a20
col "FREE(KB)"          format a20
col "USED(%)"          format 990.99
select
  tablespace_name,
  to_char(nvl(total_bytes / 1024,0),'999,999,999') as "size(KB)",
  to_char(nvl((total_bytes - free_total_bytes) / 1024,0),'999,999,999') as "used(KB)",
  to_char(nvl(free_total_bytes/1024,0),'999,999,999') as "free(KB)",
  round(nvl((total_bytes - free_total_bytes) / total_bytes * 100,100),2) as "rate(%)"
from
  ( select
      tablespace_name,
      sum(bytes) total_bytes
    from
      dba_data_files
    group by
      tablespace_name
  ),
  ( select
      tablespace_name free_tablespace_name,
      sum(bytes) free_total_bytes
    from
      dba_free_space
    group by tablespace_name
  )
where
  tablespace_name = free_tablespace_name(+)
/
--exit
spool off