oracle数据库-1 创建表空间+数据表分区+使用数据表创建新表

用户8903028603795
244 阅读8分钟

  1. 数据量过大的表，需要将数据根据一定的规则进行拆分，进行分块保存。

  2. oracle提供了表空间，来管理分块后的数据文件。表空间仅仅是一种逻辑结构。表空间统一管理空间中的数据文件，一个数据文件只能属于一个表空间。一个数据库空间由若干个表空间组成。如图所示：

image.png

1. 创建表空间

create tablespace tab_name

datafile  'filename'

size n

[autoextend on next n1 maxsize m /of]

[permanent]

[extent management local/dictionary];
  • create tablespace：创建表空间的关键字。
  • tab_name：创建后表空间的名字。
  • datafile：指定数据文件的路径为filename。
  • size n：指定数据文件的大小。
  • [autoextend on next n1 maxsize m /of ]：表示表空间是否是自动扩展的，on 为自动扩展，of为不扩展，当自动扩展时，next n1表示自动扩展的大小,max size m 表示数据文件最大扩展到m大小。
  • [permanent] ：表示创建的表空间的类型，permanent表示永久表空间，不填都是默认永久表空间。
  • [extent management local/dictionary]：表示表空间管理的方式，local表示本地的管理模式，dictionary表示数据字典管理模式，默认都是本地管理方式。

案例1、 根据表空间创建语法，创建一个100M大小数据文件（data_access_define.dbf）的表空间data_access_define，代码如下：

create tablespace data_access_define

datafile 'E:\APP\ADMIN\ORADATA\ORCL\data_access_define.DBF'

size 100m

autoextend on next 10m maxsize 500m

permanent

extent management local;

案例解析：

创建一个data_access_define表空间，指定了数据文件为“E:\APP\ADMIN\ORADATA\ORCL\data_access_define.DBF”，表空间是自动扩展的，每次自动扩展大小为10M，最大扩展到500M，创建的是永久表空间，用来存储当前用户的数据库对象和数据，管理模式为本地管理。

我们查看数据字典dba_data_files和dba_tablespaces对创建好后的data_access_define表空间进行查询，查询代码如下：

-- 查看当前oracle中的表空间

   select t.TABLESPACE_NAME, --表空间名

   t.FILE_NAME, --文件名

   t.AUTOEXTENSIBLE, --是否自动扩展

   t.BYTES / 1024 / 1024, --表空间初始大小

   t.MAXBYTES / 1024 / 1024, --表空间最大扩展到多少

   b.CONTENTS, --表空间类型

   b.EXTENT_MANAGEMENT --表空间管理模式

   from dba_data_files t, dba_tablespaces b

   where t.TABLESPACE_NAME = b.TABLESPACE_NAME

-- 修改表空间的名称

 alter tablespace data_access_define rename to data_center_define;
 注意： 这个不会修改表空间对应的物理文件data_access_define.DBF的名字，如果需要修改DBF的名称，手动重命名。

-- 修改自动增长（200m）及最大容量值(4096m)

 ALTER DATABASE DATAFILE 'F:\BAIDUNETDISKDOWNLOAD\ORADATA\REPORT\data_access_define.DBF' AUTOEXTEND ON NEXT 200M MAXSIZE 4096M;

2. 对旧表的数据进行复制及创建对应的分区表作为新表

1. 修改原表的名称

data_index---->修改为data_index_old;

2. 使用原表的数据创建新表

1）创建新表并复制旧表结构和数据

CREATE TABLE table_new AS SELECT * FROM  table_old;

2）创建新表并复制旧表部分结构和部分数据

CREATE TABLE table_new AS SELECT c1, c2, c3, c4 FROM table_old;

3）创建新表并复制旧表结构和数据后重新指定新表列名

CREATE TABLE table_new (c1,c2,c3,c4) AS SELECT c5, c6, c7, c8 FROM table_old;

4）创建新表并只复制旧表结构

CREATE TABLE table_new AS SELECT * FROM table_old WHERE 1=2;

参考资料： www.likecs.com/show-405505…

找到原先表的ddl，cv一下建表语句，加入分区语句创建新表。以修改名称后的data_index_old为例，创建新的data_index。

CREATE TABLE "模式"."DATA_INDEX" 
   (	"ID" NUMBER(20,0), 
	"BASE_ID" NUMBER(20,0) NOT NULL ENABLE, 
	"CUSTOMER_CODE" NVARCHAR2(50), 
	"REPORT_CODE" NVARCHAR2(20), 
	"INDEX_CODE" NVARCHAR2(20), 
	"INDEX_NUM" NUMBER(11,0), 
	"INDEX_NAME" NVARCHAR2(120), 
	"INDEX_VALUE1" NUMBER, 
	"INDEX_VALUE2" NUMBER, 
	"MODIFY_TIME" DATE, 
	"CREATE_TIME" DATE, 
	"DELETED" NUMBER(11,0), 
	"VERSION" NUMBER(11,0)
   ) 
   -- 后面的是分区语句， 以report_code为分区字段，同年份的划分到一个表空间，最后追加一个兜底的表空间。
 partition by list(REPORT_CODE)(
partition datacenter2017 values('2017_12_001','2017_03_002','2017_06_002','2017_09_002','2017_12_002','2017_01_003','2017_02_003','2017_03_003','2017_04_003','2017_05_003','2017_06_003','2017_07_003','2017_08_003','2017_09_003','2017_10_003','2017_11_003','2017_12_003') tablespace DATA_CENTER_2017,
partition datacenter2018 values('2018_12_001','2018_03_002','2018_06_002','2018_09_002','2018_12_002','2018_01_003','2018_02_003','2018_03_003','2018_04_003','2018_05_003','2018_06_003','2018_07_003','2018_08_003','2018_09_003','2018_10_003','2018_11_003','2018_12_003') tablespace DATA_CENTER_2018,
partition datacenter2019 values('2019_12_001','2019_03_002','2019_06_002','2019_09_002','2019_12_002','2019_01_003','2019_02_003','2019_03_003','2019_04_003','2019_05_003','2019_06_003','2019_07_003','2019_08_003','2019_09_003','2019_10_003','2019_11_003','2019_12_003') tablespace DATA_CENTER_2019,
partition datacenter2020 values('2020_12_001','2020_03_002','2020_06_002','2020_09_002','2020_12_002','2020_01_003','2020_02_003','2020_03_003','2020_04_003','2020_05_003','2020_06_003','2020_07_003','2020_08_003','2020_09_003','2020_10_003','2020_11_003','2020_12_003') tablespace DATA_CENTER_2020,
partition datacenter2021 values('2021_12_001','2021_03_002','2021_06_002','2021_09_002','2021_12_002','2021_01_003','2021_02_003','2021_03_003','2021_04_003','2021_05_003','2021_06_003','2021_07_003','2021_08_003','2021_09_003','2021_10_003','2021_11_003','2021_12_003') tablespace DATA_CENTER_2021,
partition datacenter2022 values('2022_12_001','2022_03_002','2022_06_002','2022_09_002','2022_12_002','2022_01_003','2022_02_003','2022_03_003','2022_04_003','2022_05_003','2022_06_003','2022_07_003','2022_08_003','2022_09_003','2022_10_003','2022_11_003','2022_12_003') tablespace DATA_CENTER_2022,
partition datacenter2023 values('2023_12_001','2023_03_002','2023_06_002','2023_09_002','2023_12_002','2023_01_003','2023_02_003','2023_03_003','2023_04_003','2023_05_003','2023_06_003','2023_07_003','2023_08_003','2023_09_003','2023_10_003','2023_11_003','2023_12_003') tablespace DATA_CENTER_2023,
partition datacenter2024 values('2024_12_001','2024_03_002','2024_06_002','2024_09_002','2024_12_002','2024_01_003','2024_02_003','2024_03_003','2024_04_003','2024_05_003','2024_06_003','2024_07_003','2024_08_003','2024_09_003','2024_10_003','2024_11_003','2024_12_003') tablespace DATA_CENTER_2024,
partition datacenterdefine values(DEFAULT) tablespace DATA_CENTER_DEFINE

)

注意：如果不需要行的表空间，可以讲 partition datacenter2017 values(***) tablespace DATA_CENTER_2024,，后的 tablespace DATA_CENTER_2024 去掉即可。

avatar
文章
阅读
粉丝