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用户占了哪几个表空间及大小
create tablespace
现有环境
SQL> 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行。
在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
/
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 fxwlq 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'
select count(table_name) from user_tables;