2022年12月22日木曜日

パーティション作成

【MariaDB パーティション作成】
ALTER TABLE sales.sst004 PARTITION BY RANGE COLUMNS( createdatetime ) ( 
  PARTITION p202301 VALUES LESS THAN ('20230201000000000'),
  PARTITION p202302 VALUES LESS THAN ('20230301000000000'),
  PARTITION p202303 VALUES LESS THAN ('20230401000000000'),
  PARTITION p202304 VALUES LESS THAN ('20230501000000000'),
  PARTITION p202305 VALUES LESS THAN ('20230601000000000'),
  PARTITION p202306 VALUES LESS THAN ('20230701000000000'),
  PARTITION p202307 VALUES LESS THAN ('20230801000000000'),
  PARTITION p202308 VALUES LESS THAN ('20230901000000000'),
  PARTITION p202309 VALUES LESS THAN ('20231001000000000'),
  PARTITION p202310 VALUES LESS THAN ('20231101000000000'),
  PARTITION p202311 VALUES LESS THAN ('20231201000000000'),
  PARTITION p202312 VALUES LESS THAN ('20240101000000000'),
  PARTITION p999999 VALUES LESS THAN MAXVALUE
);

追加
ALTER TABLE sales.sst004 REORGANIZE PARTITION p999999 INTO (
  PARTITION p202402 VALUES LESS THAN ('20240301000000000'),
  PARTITION p999999 VALUES LESS THAN MAXVALUE
);

データ削除
ALTER TABLE sales.sst004 TRUNCATE PARTITION p202301;

削除＆データ削除
ALTER TABLE sales.sst004 DROP PARTITION p20240101;

解放
ALTER TABLE sales.sst004 REMOVE PARTITIONING;


ALTER TABLE sales.sst004 PARTITION BY RANGE COLUMNS( createdatetime )
  SUBPARTITION BY HASH(GCODE,CCODE,SODE)
  SUBPARTITIONS 8 (
  PARTITION p202301 VALUES LESS THAN ('20230201000000000'),
  PARTITION p202302 VALUES LESS THAN ('20230301000000000'),
  PARTITION p202303 VALUES LESS THAN ('20230401000000000'),
  PARTITION p202304 VALUES LESS THAN ('20230501000000000'),
  PARTITION p202305 VALUES LESS THAN ('20230601000000000'),
  PARTITION p202306 VALUES LESS THAN ('20230701000000000'),
  PARTITION p202307 VALUES LESS THAN ('20230801000000000'),
  PARTITION p202308 VALUES LESS THAN ('20230901000000000'),
  PARTITION p202309 VALUES LESS THAN ('20231001000000000'),
  PARTITION p202310 VALUES LESS THAN ('20231101000000000'),
  PARTITION p202311 VALUES LESS THAN ('20231201000000000'),
  PARTITION p202312 VALUES LESS THAN ('20240101000000000'),
  PARTITION p999999 VALUES LESS THAN MAXVALUE
);

ALTER TABLE sales.sst004 SUBPARTITION BY HASH COLUMNS( gcode,ccode,scode ) (
 SUBPARTITIONS 8 )
 
 
 
 create table  sales.sst0041  (
   remarks1        varchar(256),
   remarks2        varchar(256),
   remarks3        varchar(256),
   remarks4        varchar(256),
   remarks5        varchar(256),
  primary key ( gcode ,ccode ,scode, createdatetime  )
) engine=innodb default charset=utf8mb4 comment='会計伝票'
 PARTITION BY RANGE  COLUMNS( createdatetime )
SUBPARTITION BY HASH(GCODE,CCODE,SODE)             → MariaDB ではエラー
SUBPARTITIONS 8
(PARTITION `p202301` VALUES LESS THAN ('20230201000000000') ,
 PARTITION `p202302` VALUES LESS THAN ('20230301000000000') ,
 PARTITION `p202303` VALUES LESS THAN ('20230401000000000') ,
 PARTITION `p202304` VALUES LESS THAN ('20230501000000000') ,
 PARTITION `p202305` VALUES LESS THAN ('20230601000000000') ,
 PARTITION `p202306` VALUES LESS THAN ('20230701000000000') ,
 PARTITION `p202307` VALUES LESS THAN ('20230801000000000') ,
 PARTITION `p202308` VALUES LESS THAN ('20230901000000000') ,
 PARTITION `p202309` VALUES LESS THAN ('20231001000000000') ,
 PARTITION `p202310` VALUES LESS THAN ('20231101000000000') ,
 PARTITION `p202311` VALUES LESS THAN ('20231201000000000') ,
 PARTITION `p202312` VALUES LESS THAN ('20240101000000000') ,
 PARTITION `p999999` VALUES LESS THAN (MAXVALUE));

【ORACLE パーティション作成】
CREATE TABLE ARCSDBCB.CCP020 (
CDATE VARCHAR2 (8) NOT NULL,
GCODE VARCHAR2 (3) NOT NULL,
CCODE VARCHAR2 (4) NOT NULL,
SCODE VARCHAR2 (4) NOT NULL,
RCODE VARCHAR2 (13) NOT NULL,
SALESDATE VARCHAR2 (8) NOT NULL,
SALESTIME VARCHAR2 (6) NOT NULL,
RCOUNTER VARCHAR2 (12) NOT NULL,
CANCELNO VARCHAR2 (12) ,
CARDTYPE VARCHAR2 (1) NOT NULL,
SALESTYPE VARCHAR2 (1) NOT NULL,
CREDITCCODE VARCHAR2 (5) NOT NULL,
CREDITBTYPE VARCHAR2 (1) NOT NULL,
CREDITKEYSID VARCHAR2 (15) NOT NULL,
CREDITSID VARCHAR2 (15) ,
AMOUNT NUMBER (10) NOT NULL,
CARDMETHOD VARCHAR2 (2) NOT NULL,
COMPANYFEE NUMBER (6,2) ,
COMPANYAMOUNT NUMBER (10) ,
MEMBERFEE NUMBER (6,2) ,
MEMBERAMOUNT NUMBER (10) ,
PAIDPLANDATE VARCHAR2 (8) DEFAULT '00000000',
APPROVALNO VARCHAR2 (10) ,
CAFISDATE VARCHAR2 (8) ,
PGCODE VARCHAR2 (3) NOT NULL,
PCCODE VARCHAR2 (4) NOT NULL,
PSCODE VARCHAR2 (4) NOT NULL,
UDATE VARCHAR2 (14) NOT NULL,
CTYPE VARCHAR2 (1) DEFAULT '0'
)
PCTFREE 10
STORAGE(INITIAL 64K)
PARTITION BY RANGE ( PAIDPLANDATE )
SUBPARTITION BY HASH ( GCODE, CCODE, PSCODE )
SUBPARTITIONS 2
(
PARTITION p202212 VALUES LESS THAN ('20230101'),
PARTITION p202301 VALUES LESS THAN ('20230201'),
PARTITION p202302 VALUES LESS THAN ('20230301'),
PARTITION p202303 VALUES LESS THAN ('20230401'),
PARTITION p202304 VALUES LESS THAN ('20230501'),
PARTITION p202305 VALUES LESS THAN ('20230601'),
PARTITION p202306 VALUES LESS THAN ('20230701'),
PARTITION p202307 VALUES LESS THAN ('20230801'),
PARTITION p202308 VALUES LESS THAN ('20230901'),
PARTITION p202309 VALUES LESS THAN ('20231001'),
PARTITION p202310 VALUES LESS THAN ('20231101'),
PARTITION p202311 VALUES LESS THAN ('20231201'),
PARTITION p202312 VALUES LESS THAN ('20240101'),
PARTITION p202401 VALUES LESS THAN ('20240201'),
PARTITION p202402 VALUES LESS THAN ('20240301'),
PARTITION p202403 VALUES LESS THAN ('20240401'),
PARTITION p202404 VALUES LESS THAN ('20240501'),
PARTITION p202405 VALUES LESS THAN ('20240601'),
PARTITION p202406 VALUES LESS THAN ('20240701'),
PARTITION p202407 VALUES LESS THAN ('20240801'),
PARTITION p202408 VALUES LESS THAN ('20240901'),
PARTITION p202409 VALUES LESS THAN ('20241001'),
PARTITION p202410 VALUES LESS THAN ('20241101'),
PARTITION p202411 VALUES LESS THAN ('20241201'),
PARTITION p202412 VALUES LESS THAN ('20250101'),
PARTITION p202501 VALUES LESS THAN ('20250201'),
PARTITION p202502 VALUES LESS THAN ('20250301'),
PARTITION p202503 VALUES LESS THAN ('20250401'),
PARTITION p202504 VALUES LESS THAN ('20250501'),
PARTITION p202505 VALUES LESS THAN ('20250601'),
PARTITION p202506 VALUES LESS THAN ('20250701'),
PARTITION p202507 VALUES LESS THAN ('20250801'),
PARTITION p202508 VALUES LESS THAN ('20250901'),
PARTITION p202509 VALUES LESS THAN ('20251001'),
PARTITION p202510 VALUES LESS THAN ('20251101'),
PARTITION p202511 VALUES LESS THAN ('20251201'),
PARTITION p202512 VALUES LESS THAN ('20260101'),
PARTITION p202601 VALUES LESS THAN ('20260201'),
PARTITION p202602 VALUES LESS THAN ('20260301'),
PARTITION p202603 VALUES LESS THAN ('20260401'),
PARTITION p202604 VALUES LESS THAN ('20260501'),
PARTITION p202605 VALUES LESS THAN ('20260601'),
PARTITION p202606 VALUES LESS THAN ('20260701'),
PARTITION p202607 VALUES LESS THAN ('20260801'),
PARTITION p202608 VALUES LESS THAN ('20260901'),
PARTITION p202609 VALUES LESS THAN ('20261001'),
PARTITION p202610 VALUES LESS THAN ('20261101'),
PARTITION p202611 VALUES LESS THAN ('20261201'),
PARTITION p202612 VALUES LESS THAN ('20270101'),
PARTITION p202701 VALUES LESS THAN ('20270201'),
PARTITION p202702 VALUES LESS THAN ('20270301'),
PARTITION p202703 VALUES LESS THAN ('20270401'),
PARTITION p202704 VALUES LESS THAN ('20270501'),
PARTITION p202705 VALUES LESS THAN ('20270601'),
PARTITION p202706 VALUES LESS THAN ('20270701'),
PARTITION p202707 VALUES LESS THAN ('20270801'),
PARTITION p202708 VALUES LESS THAN ('20270901'),
PARTITION p202709 VALUES LESS THAN ('20271001'),
PARTITION p202710 VALUES LESS THAN ('20271101'),
PARTITION p202711 VALUES LESS THAN ('20271201'),
PARTITION p202712 VALUES LESS THAN ('20280101')
)
tablespace user_data;

ALTER TABLE ARCSDBCB.CCP020 DROP CONSTRAINT CCP020_PRIMARY CASCADE DROP INDEX;

ALTER TABLE ARCSDBCB.CCP020 DROP PRIMARY KEY;
ALTER TABLE ARCSDBCB.CCP020 ADD CONSTRAINT CCP020_PRIMARY PRIMARY KEY (
CDATE,
GCODE,
CCODE,
SCODE,
RCODE,
RCOUNTER,
SALESDATE,
SALESTIME,
CARDTYPE
)
USING INDEX;


DROP INDEX arcsdbcb.ccp020_INDEX01;
DROP INDEX arcsdbcb.ccp020_index02;

CREATE INDEX ARCSDBCB.CCP020_INDEX01 ON ARCSDBCB.CCP020 (
CDATE
);

CREATE INDEX ARCSDBCB.CCP020_INDEX02 ON ARCSDBCB.CCP020 (
PAIDPLANDATE
);

【デフォルトユーザ領域変更】

SQL> CREATE TABLESPACE 表領域名
 DATAFILE 'データファイル名（フルパス指定可）.dbf' SIZE 100M
 AUTOEXTEND ON NEXT 500K MAXSIZE 1024M;


SQL> alter session set container=arcsdbms;
SQL> create tablespace user_data datafile '/u01/app/oracle/oradata/ORCL/datafile/user_data.dbf' size 1M autoextend on next 512k maxsize  1G;

SQL> SELECT USERNAME, DEFAULT_TABLESPACE FROM dba_users where username = 'ARCSDBCB';

SQL> ALTER USER ARCSDBCB DEFAULT TABLESPACE USER_DATA;
ORA-00959: 表領域'USER_DATA'は存在しません。 
→コンテナを指定していなかった為。


【統計情報更新】
$ sqlp / as sysdba
SQL>show con_name
SQL> show pdbs
SQL> alter session set container=arcsdbms;
SQL> analyze table テーブル名 compute statistics;
SQL> analyze table テーブル名 estimate statistics sample 10 percent;
SQL> analyze table テーブル名 delete statistics;
SQL> analyze index arcsdbcb.ccp020_index01 validate structure;

又は、
SQL> execute dbms_stats.gather_database_stats_job_proc();

 


時刻:

0 件のコメント:

コメントを投稿

登録: コメントの投稿 (Atom)

シャットダウン時の後処理 （shutdown）

# vi /etc/systemd/system/drop.service [Unit] Description= stop httpgwd DefaultDependencies=no Before=shutdown.target RefuseManualStart=true ...

  • Zabbix history_uint.ibd 肥大化して困った。
    history_uint.ibdが肥大化して52Gになった。 -rw-r-----. 1 mysql mysql 52495908864  6月 14 09:20 history_uint.ibd -rw-r-----. 1 mysql mysql  7977566208 ...
  • ZABBIX SNMP Trap 設定
    【snmp/snmptrap】 # yum -y install net-snmp # yum -y install net-snmp-utils # yum -y install snmptt # yum install perl-Sys-Syslog # fi...
  • LVM ファイルシステムのマウント
    # mount /dev/nvme0n1p3 /mnt/m2 mount: /mnt/m2: 未知のファイルシステムタイプ 'LVM2_member' です. #  fdisk -l /dev/nvme0n1 ディスク /dev/nvme0n1: 953....