|
T_L T_DIAGNOSIS_TYPE 1 144 128 16
LIFEDATA_T_L T_DIAGNOSIS_TYPE 2 160 128 16
LIFEDATA_T_L T_DIAGNOSIS_TYPE 3 176 128 16
尝试对表move（本次不再次对数据文件resize 进行分析，如果需要resize减少数据文件，需要从数据文件max_block_id 进行move，随后对数据文件进行resize）
alter table PICCPROD.T_DIAGNOSIS_TYPE move tablespace LIFEDATA_T_L;
*
ERROR at line 1:
ORA-12953: The request exceeds the maximum allowed database size of 11 GB
*Move时，需要存在一倍对象空闲空间，此时空闲空间不足无法Move*
*释放空间，找出max最大对象
select segment_name,sum(blocks) from dba_extents where owner='PICCPROD' group by segment_name order by 2;
SEGMENT_NAME SUM(BLOCKS)
-------------------------------------------
T_POLICY_AUTO 221312
T_POLICY_PRODUCT 309120
drop table PICCPROD.T_POLICY_AUTO purge;
drop table PICCPROD.T_POLICY_PRODUCT purge;
alter table PICCPROD.T_DIAGNOSIS_TYPE move tablespace LIFEDATA_T_L;
select tablespace_name,segment_name,EXTENT_ID,BLOCK_ID,BYTES/1024,BLOCKS from dba_extents where owner='PICCPROD' and segment_name='T_DIAGNOSIS_TYPE';
TABLESPACE_NAME SEGMENT_NAME EXTENT_ID BLOCK_ID BYTES/1024 BLOCKS
-------------------- -------------------- ---------- ---------- ---------- ----------
LIFEDATA_T_L T_DIAGNOSIS_TYPE 0 34720 128 16
LIFEDATA_T_L T_DIAGNOSIS_TYPE 1 34736 128 16
LIFEDATA_T_L T_DIAGNOSIS_TYPE 2 34752 128 16
LIFEDATA_T_L T_DIAGNOSIS_TYPE 3 34768 128 16
*尝试对数据文件进行resize*
select file_id,bytes/1024/1024 from dba_data_files where tablespace_name='LIFEDATA_T_L';
FILE_ID BYTES/1024/1024
---------- ---------------
5 10000
alter database datafile 5 resize 5000M;
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value
--虽然数据为空，但是表对应的段，分配了存储，占用了存储
*truncate table
SQL> truncate table PICCPROD.T_DIAGNOSIS_TYPE;
Table truncated.
SQL> select tablespace_name,segment_name,EXTENT_ID,BLOCK_ID,BYTES/1024,BLOCKS from dba_extents where owner='PICCPROD' and segment_name='T_DIAGNOSIS_TYPE';
TABLESPACE_NAME SEGMENT_NAME EXTENT_ID BLOCK_ID BYTES/1024 BLOCKS
-------------------- -------------------- ---------- ---------- ---------- ----------
LIFEDATA_T_L T_DIAGNOSIS_TYPE 0 34720 128 16
LIFEDATA_T_L T_DIAGNOSIS_TYPE 1 34736 128 16
LIFEDATA_T_L T_DIAGNOSIS_TYPE 2 34752 128 16
LIFEDATA_T_L T_DIAGNOSIS_TYPE 3 34768 128 16
*数据库参数，延迟段创建
SQL> show parameter defer
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
deferred_segment_creation boolean TRUE
--参数并未关闭--false，但是之前的导入还是创建表，因此延迟段创建参数，对于数据泵导入的对象是不起作用的
*查询建表语句
--导入
impdp \'/ as sysdba\' dumpfile=PICCPROD%U.dmp directory=dump logfile=picc%U.log sqlfile=sql01.sql
ORA-39002: invalid operation
ORA-31694: master table "SYS"."SYS_SQL_FILE_FULL_01" failed to load/unload
ORA-02354: error in exporting/importing data
ORA-39776: fatal Direct Path API error loading table "SYS"."SYS_SQL_FILE_FULL_01"
ORA-12953: The request exceeds the maximum allowed database size of 11 GB
--
CREATE TABLE "PICCPROD"."T_POLICY_CHECK_ITEM" ("ITEM_ID" NUMBER(10,0) NOT NULL ENABLE,
"CUSTOMER_ID" NUMBER(10,0) NOT NULL ENABLE, "CHECK_NUM" NUMBER(4,0) NOT NULL ENABLE,
"NORMAL" CHAR(1 BYTE) DEFAULT 'Y' NOT NULL ENABLE, &qu |