^[pRoUd]^ 작업실

create directory [dir_nm] as '[dir_path]';

create table [table_name]
(
 column1 char(10)
,column2 char(10)
)
organization external
(
 type oracle_loader
default directory [dir_nm]
access parameters(
records delimited by newline
fields terminated by ';'(
column1 char,
column2 char))
location('[file_name]'))
;

How to find full hint naming.

Possible Above 10g R2 (maybe)

select * from table(dbms_xplan.display(null, null, 'outline'));

Range Partition

- Create Table

CREATE TABLE TABLE_NAME
(
 COLUMN_1 DATE
,COLUMN_2 VARCHAR2(X)
)
TABLESPACE XXX
PCTUSED    0
PCTFREE    0
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          5M
            NEXT             10M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOLOGGING 
COMPRESS 
NOCACHE
NOPARALLEL
NOMONITORING
PARTITION BY RANGE(COLUMN_1)
(
 PARTITION PAR_MIN VALUES LESS THAN (TO_DATE('2009-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
,PARTITION PAR_MAX VALUES LESS THAN (TO_DATE('9999-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
);

alter table table_name exchange partition partition_name with table table_name with validation;

alter table table_name add partition partition_name values less than ('value');

alter table table_name drop partition partition_name;

alter table table_name truncate partition partition_name;

2. Else

 split

alter table table_name split partition partition_name at ('20090301') into (partition par_200902, partition par_max) parallel 8;

alter index [index_owner].[index_name] coalesce;

alter index [index_owner].[index_name] modify partition [partition_name] coalesce;

alter index [index_owner].[index_name] rebuild partition [partition_name];

explan plan for
[sql];

select * from table(dbms_xplan.display);

set serveroutput on
declare
   l_fs1_bytes number;
   l_fs2_bytes number;
   l_fs3_bytes number;
   l_fs4_bytes number;
   l_fs1_blocks number;
   l_fs2_blocks number;
   l_fs3_blocks number;
   l_fs4_blocks number;
   l_full_bytes number;
   l_full_blocks number;
   l_unformatted_bytes number;
   l_unformatted_blocks number;
begin
   dbms_space.space_usage(
      segment_owner      => '&&owner',
      segment_name       => '&&name',
      segment_type       => 'TABLE',
      fs1_bytes          => l_fs1_bytes,
      fs1_blocks         => l_fs1_blocks,
      fs2_bytes          => l_fs2_bytes,
      fs2_blocks         => l_fs2_blocks,
      fs3_bytes          => l_fs3_bytes,
      fs3_blocks         => l_fs3_blocks,
      fs4_bytes          => l_fs4_bytes,
      fs4_blocks         => l_fs4_blocks,
      full_bytes         => l_full_bytes,
      full_blocks        => l_full_blocks,
      unformatted_blocks => l_unformatted_blocks,
      unformatted_bytes  => l_unformatted_bytes
   );
   dbms_output.put_line(' FS1 Blocks = '||l_fs1_blocks||' Bytes = '||l_fs1_bytes);
   dbms_output.put_line(' FS2 Blocks = '||l_fs2_blocks||' Bytes = '||l_fs2_bytes);
   dbms_output.put_line(' FS3 Blocks = '||l_fs3_blocks||' Bytes = '||l_fs3_bytes);
   dbms_output.put_line(' FS4 Blocks = '||l_fs4_blocks||' Bytes = '||l_fs4_bytes);
   dbms_output.put_line('Full Blocks = '||l_full_blocks||' Bytes = '||l_full_bytes);
   dbms_output.put_line('Unformatted Blocks = '||l_unformatted_blocks||' Bytes = '||l_unformatted_bytes);
end;
/
set serveroutput off

exec dbms_stats.gather_table_stats ( -
'&&owner', '&&table_name' -
,estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE -
,block_sample => true -
,granularity => 'all' -
,method_opt => 'for all columns size auto' -
,cascade => true)

exec dbms_stats.gather_table_stats ( -
'&&owner', '&&table_name' -
,estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE -
,block_sample => true -
,granularity => 'all' -
,method_opt => 'for all indexed columns size auto' -
,cascade => true)

analyze index [INDEX_NAME] validate structure;

select *
  from index_stats
;

,to_char(floor(avg_time*24)) hour
,to_char(case when floor(avg_time*24*60) >= 60
                     then (floor(avg_time*24*60)-(floor(avg_time*24)*60))
                     else floor(avg_time*24*60) end ) min
,to_char(floor(((avg_time*24*60)-floor(avg_time*24*60))*60)) sec

select to_char(to_date(round((sysdate-(sysdate-1/24/60*5))*24*60),'sssss'),'hh24:mi:ss') from dual;

substr(to_timestamp(to_char(end_date,'YYYYMMDDHH24MISS'),'YYYYMMDDHH24MISS') - to_timestamp(to_char(start_date,'YYYYMMDDHH24MISS'),'YYYYMMDDHH24MISS'),12,8) as time_between