Contents
现有环境
SQL> select count(table_name) from user_tables;
COUNT(TABLE_NAME)
-----------------
4403
SQL> conn fxwl/fxwl
select b.tablespace_name, tbs_size SizeMb, a.free_space FreeMb
from
(select tablespace_name, round(sum(bytes)/1024/1024 ,2) as free_space
from dba_free_space group by tablespace_name) a,
(select tablespace_name, sum(bytes)/1024/1024 as tbs_size
from dba_data_files group by tablespace_name
UNION
select tablespace_name, sum(bytes)/1024/1024 tbs_size
from dba_temp_files
group by tablespace_name ) b
where a.tablespace_name(+)=b.tablespace_name;
TABLESPACE_NAME SIZEMB FREEMB
------------------------------------------------------------ ---------- ----------
SYSAUX 5120 3588.19
UNDOTBS1 3715 3619.63
EAS_D_FXWL_TEMP2 2048 2047
USERS 5 3.69
EAS_D_FXWL_STANDARD 97340 2910.88
SYSTEM 6740 152
EAS_T_FXWL_STANDARD 5120 5119
TEMP 9047
已选择8行。
users
select * from all_users;
create schema(users)
select tablespace_name from user_tablespaces;
select USERNAME, DEFAULT_TABLESPACE from DBA_USERS;
select USERNAME, DEFAULT_TABLESPACE from DBA_USERS where DEFAULT_TABLESPACE = 'DEV_DB';
查看Oracle用户占了哪几个表空间及大小
select *
from (select owner || '.' || tablespace_name name, sum(b) g
from (select owner,
t.segment_name,
t.partition_name,
round(bytes / 1024 / 1024 / 1024, 2) b,
tablespace_name
from dba_segments t)
where owner not in
('SYS', 'OUTLN', 'SYSTEM', 'TSMSYS', 'DBSNMP', 'WMSYS')
group by owner || '.' || tablespace_name)
order by name;
create tablespace
query default tablespace
select * from database_properties where property_name like 'DEFAULT%TABLESPACE';
在RAC中创建
conn fxwl/fxwl
select USERNAME, DEFAULT_TABLESPACE from DBA_USERS;
CREATE TABLESPACE EAS_D_FXWL_STANDARD
DATAFILE '+SSD_DATA' SIZE 30G AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
LOGGING
ONLINE
SEGMENT SPACE MANAGEMENT AUTO
/
CREATE TABLESPACE EAS_D_FXWL_TEMP2
DATAFILE '+SSD_DATA' SIZE 10G AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
LOGGING
ONLINE
SEGMENT SPACE MANAGEMENT AUTO
/
CREATE TABLESPACE EAS_T_FXWL_STANDARD
DATAFILE '+SSD_DATA' SIZE 10G AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
LOGGING
ONLINE
SEGMENT SPACE MANAGEMENT AUTO
/
ALTER TABLESPACE EAS_D_FXWL_STANDARD add DATAFILE '+SSD_DATA' SIZE 30G AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED;
ALTER TABLESPACE EAS_D_FXWL_STANDARD add DATAFILE '+SSD_DATA' SIZE 30G AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED;
ALTER TABLESPACE EAS_D_FXWL_STANDARD add DATAFILE '+SSD_DATA' SIZE 30G AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED;
create user fxwl identified by fxwl default tablespace EAS_D_FXWL_STANDARD quota unlimited on EAS_D_FXWL_STANDARD;
create user fxwlq identified by system default tablespace users quota unlimited on users;
grant dba to fxwl;impdp \"system as sysdba\" schemas=fxwl directory=dump_dir dumpfile=FXWL_201611030600.DMP logfile=impdpFrom11.2.0.1.log table_exists_action=replace parfile=dmp.txt cluster=n
SQL> select name from v$tempfile; NAME ----------------------------------------------------------- D:\ORACLE\ORADATA\FXWL\TEMP01.DBF SQL> select name from v$datafile; NAME ----------------------------------------------------------- D:\ORACLE\ORADATA\FXWL\SYSTEM01.DBF D:\ORACLE\ORADATA\FXWL\SYSAUX01.DBF D:\ORACLE\ORADATA\FXWL\UNDOTBS01.DBF D:\ORACLE\ORADATA\FXWL\USERS01.DBF D:\ORACLE\ORADATA\FXWL\EAS_D_FXWL_STANDARD101.ORA D:\ORACLE\ORADATA\FXWL\EAS_D_FXWL_STANDARD102.ORA D:\ORACLE\ORADATA\FXWL\EAS_D_FXWL_STANDARD103.ORA D:\ORACLE\ORADATA\FXWL\EAS_D_FXWL_STANDARD104.ORA D:\ORACLE\ORADATA\FXWL\EAS_D_FXWL_STANDARD105.ORA D:\ORACLE\ORADATA\FXWL\EAS_D_FXWL_STANDARD106.ORA D:\ORACLE\ORADATA\FXWL\EAS_D_FXWL_STANDARD107.ORA NAME ----------------------------------------------------------- D:\ORACLE\ORADATA\FXWL\EAS_D_FXWL_TEMP2101.ORA D:\ORACLE\ORADATA\FXWL\EAS_T_FXWL_STANDARD101.ORA 已选择13行。 SQL>
dump.txt
remap_datafile="'D:/ORACLE/ORADATA/FXWL/SYSTEM01.DBF':'+DATA'" remap_datafile="'D:/ORACLE/ORADATA/FXWL/SYSAUX01.DBF':'+DATA'" remap_datafile="'D:/ORACLE/ORADATA/FXWL/UNDOTBS01.DBF':'+DATA'" remap_datafile="'D:/ORACLE/ORADATA/FXWL/USERS01.DBF':'+DATA'" remap_datafile="'D:/ORACLE/ORADATA/FXWL/EAS_D_FXWL_STANDARD101.ORA':'+DATA'" remap_datafile="'D:/ORACLE/ORADATA/FXWL/EAS_D_FXWL_STANDARD102.ORA':'+DATA'" remap_datafile="'D:/ORACLE/ORADATA/FXWL/EAS_D_FXWL_STANDARD103.ORA':'+DATA'" remap_datafile="'D:/ORACLE/ORADATA/FXWL/EAS_D_FXWL_STANDARD104.ORA':'+DATA'" remap_datafile="'D:/ORACLE/ORADATA/FXWL/EAS_D_FXWL_STANDARD105.ORA':'+DATA'" remap_datafile="'D:/ORACLE/ORADATA/FXWL/EAS_D_FXWL_STANDARD106.ORA':'+DATA'" remap_datafile="'D:/ORACLE/ORADATA/FXWL/EAS_D_FXWL_STANDARD107.ORA':'+DATA'" 'D:\ORACLE\ORADATA\FXWL\EAS_D_FXWL_TEMP2101.ORA':'+SSD_DATA' 'D:\ORACLE\ORADATA\FXWL\EAS_T_FXWL_STANDARD101.ORA':'+SSD_DATA'
impdp \"system as sysdba\" schemas=fxwl directory=dump_dir dumpfile=FXWL_201611030600.DMP logfile=impdpFrom11.2.0.1.log table_exists_action=replace cluster=n remap_datafile='D:\ORACLE\ORADATA\FXWL\SYSTEM01.DBF':'+SSD_DATA','D:\ORACLE\ORADATA\FXWL\SYSAUX01.DBF':'+SSD_DATA','D:\ORACLE\ORADATA\FXWL\UNDOTBS01.DBF':'+SSD_DATA','D:\ORACLE\ORADATA\FXWL\USERS01.DBF':'+SSD_DATA','D:\ORACLE\ORADATA\FXWL\EAS_D_FXWL_STANDARD101.ORA':'+SSD_DATA','D:\ORACLE\ORADATA\FXWL\EAS_D_FXWL_STANDARD102.ORA':'+SSD_DATA','D:\ORACLE\ORADATA\FXWL\EAS_D_FXWL_STANDARD103.ORA':'+SSD_DATA','D:\ORACLE\ORADATA\FXWL\EAS_D_FXWL_STANDARD104.ORA':'+SSD_DATA','D:\ORACLE\ORADATA\FXWL\EAS_D_FXWL_STANDARD106.ORA':'+SSD_DATA','D:\ORACLE\ORADATA\FXWL\EAS_D_FXWL_STANDARD107.ORA':'+SSD_DATA'
full with replace table_exists_action
impdp \"system as sysdba\" full=y directory=dump_dir dumpfile=FXWL_FULL_DATA_BACKUP.DMP logfile=1101.1156impdpFrom11.2.0.1_full.log table_exists_action=replace cluster=n remap_datafile='D:\ORACLE\ORADATA\FXWL\SYSTEM01.DBF':'+SSD_DATA','D:\ORACLE\ORADATA\FXWL\SYSAUX01.DBF':'+SSD_DATA','D:\ORACLE\ORADATA\FXWL\UNDOTBS01.DBF':'+SSD_DATA','D:\ORACLE\ORADATA\FXWL\USERS01.DBF':'+SSD_DATA','D:\ORACLE\ORADATA\FXWL\EAS_D_FXWL_STANDARD101.ORA':'+SSD_DATA','D:\ORACLE\ORADATA\FXWL\EAS_D_FXWL_STANDARD102.ORA':'+SSD_DATA','D:\ORACLE\ORADATA\FXWL\EAS_D_FXWL_STANDARD103.ORA':'+SSD_DATA','D:\ORACLE\ORADATA\FXWL\EAS_D_FXWL_STANDARD104.ORA':'+SSD_DATA','D:\ORACLE\ORADATA\FXWL\EAS_D_FXWL_STANDARD106.ORA':'+SSD_DATA','D:\ORACLE\ORADATA\FXWL\EAS_D_FXWL_STANDARD107.ORA':'+SSD_DATA','D:\ORACLE\ORADATA\FXWL\EAS_D_FXWL_TEMP2101.ORA':'+SSD_DATA','D:\ORACLE\ORADATA\FXWL\EAS_T_FXWL_STANDARD101.ORA':'+SSD_DATA'
full
impdp \"system as sysdba\" full=y directory=dump_dir dumpfile=FXWL_FULL_DATA_BACKUP.DMP logfile=1101.1156impdpFrom11.2.0.1_full.log remap_datafile='D:\ORACLE\ORADATA\FXWL\SYSTEM01.DBF':'+SSD_DATA','D:\ORACLE\ORADATA\FXWL\SYSAUX01.DBF':'+SSD_DATA','D:\ORACLE\ORADATA\FXWL\UNDOTBS01.DBF':'+SSD_DATA','D:\ORACLE\ORADATA\FXWL\USERS01.DBF':'+SSD_DATA','D:\ORACLE\ORADATA\FXWL\EAS_D_FXWL_STANDARD101.ORA':'+SSD_DATA','D:\ORACLE\ORADATA\FXWL\EAS_D_FXWL_STANDARD102.ORA':'+SSD_DATA','D:\ORACLE\ORADATA\FXWL\EAS_D_FXWL_STANDARD103.ORA':'+SSD_DATA','D:\ORACLE\ORADATA\FXWL\EAS_D_FXWL_STANDARD104.ORA':'+SSD_DATA','D:\ORACLE\ORADATA\FXWL\EAS_D_FXWL_STANDARD106.ORA':'+SSD_DATA','D:\ORACLE\ORADATA\FXWL\EAS_D_FXWL_STANDARD107.ORA':'+SSD_DATA','D:\ORACLE\ORADATA\FXWL\EAS_D_FXWL_TEMP2101.ORA':'+SSD_DATA','D:\ORACLE\ORADATA\FXWL\EAS_T_FXWL_STANDARD101.ORA':'+SSD_DATA'
schemas
impdp \"system as sysdba\" schemas=fxwl directory=dump_dir dumpfile=FXWL_201611030600.DMP logfile=1104.1325impdpFrom11.2.0.1_schemas.log remap_datafile='D:\ORACLE\ORADATA\FXWL\SYSTEM01.DBF':'+SSD_DATA','D:\ORACLE\ORADATA\FXWL\SYSAUX01.DBF':'+SSD_DATA','D:\ORACLE\ORADATA\FXWL\UNDOTBS01.DBF':'+SSD_DATA','D:\ORACLE\ORADATA\FXWL\USERS01.DBF':'+SSD_DATA','D:\ORACLE\ORADATA\FXWL\EAS_D_FXWL_STANDARD101.ORA':'+SSD_DATA','D:\ORACLE\ORADATA\FXWL\EAS_D_FXWL_STANDARD102.ORA':'+SSD_DATA','D:\ORACLE\ORADATA\FXWL\EAS_D_FXWL_STANDARD103.ORA':'+SSD_DATA','D:\ORACLE\ORADATA\FXWL\EAS_D_FXWL_STANDARD104.ORA':'+SSD_DATA','D:\ORACLE\ORADATA\FXWL\EAS_D_FXWL_STANDARD106.ORA':'+SSD_DATA','D:\ORACLE\ORADATA\FXWL\EAS_D_FXWL_STANDARD107.ORA':'+SSD_DATA','D:\ORACLE\ORADATA\FXWL\EAS_D_FXWL_TEMP2101.ORA':'+SSD_DATA','D:\ORACLE\ORADATA\FXWL\EAS_T_FXWL_STANDARD101.ORA':'+SSD_DATA' impdp \"system as sysdba\" schemas=fxwlq directory=dump_dir dumpfile=FXWLQ_201611031822.DMP logfile=1104.1548_fxwlq.log
新RAC环境
SQL> select count(table_name) from user_tables;
COUNT(TABLE_NAME)
- 4004 conn fxwl/fxwl
select b.tablespace_name, tbs_size SizeMb, a.free_space FreeMb from (select tablespace_name, round(sum(bytes)/1024/1024 ,2) as free_space from dba_free_space group by tablespace_name) a, (select tablespace_name, sum(bytes)/1024/1024 as tbs_size from dba_data_files group by tablespace_name UNION select tablespace_name, sum(bytes)/1024/1024 tbs_size from dba_temp_files group by tablespace_name ) b Connected. SQL> 2 3 4 5 6 7 8 9 10 11 where a.tablespace_name(+)=b.tablespace_name; TABLESPACE_NAME SIZEMB FREEMB
- SYSAUX 570 31.63 UNDOTBS1 425 2 EAS_D_FXWL_TEMP2 10240 10239 USERS 5 3.69 EAS_D_FXWL_STANDARD 122880 38968.81 SYSTEM 940 148.5 EAS_T_FXWL_STANDARD 10240 10239 UNDOTBS2 25 16.5 TEMP 5647 9 rows selected.
}}}