Oracle建立表空间和用户
需求来自要把原数据导入到新的机器的新空间和新用户里面。
1、我们查看一下原来库的建表空间语句。
//注意只导业务表空间即可，有些是系统空间是每台机器，根据情况不同设定的，不用管它。
2、新建表空间的步骤 建物理表空间，建用户，建权限。
系统采样(防止下面的语句跟大家执行的不匹配)
1、oracle版本 11.2.0.4.0
2、系统版本
[oracle@shdb02 /]$ cat /etc/redhat-release Red Hat Enterprise Linux Server release 6.4 (Santiago)
3、内核版本
[oracle@shfpdb02 /]$ uname -r 2.6.32-358.el6.x86_64
一、查看当前的表空间创建语句
//要迁移的oracle表空间信息采集.(集群版)
[oracle@shdb02 /]$ echo $ORACLE_SID
fp2
[oracle@shdb02 /]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Mon Mar 21 13:04:17 2022 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options SQL> SELECT dbms_lob.substr(DBMS_METADATA.GET_DDL('TABLESPACE', TS.tablespace_name)) FROM DBA_TABLESPACES TS; CREATE TABLESPACE "SYSTEM" DATAFILE SIZE 524288000 AUTOEXTEND ON NEXT 10485760 MAXSIZE 32767M LOGGING ONLINE PERMANENT BLOCKSIZE 8192 EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS SEGMENT SPACE MANAGEMENT MANUAL ALTER DATABASE DATAFILE '+DATA/fp/datafile/system.14577.1006784737' RESIZE 1415577600
CREATE TABLESPACE "SYSAUX" DATAFILE SIZE 419430400 AUTOEXTEND ON NEXT 10485760 MAXSIZE 32767M LOGGING ONLINE PERMANENT BLOCKSIZE 8192 EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO ALTER DATABASE DATAFILE '+DATA/fp/datafile/sysaux.264.1006784737' RESIZE 22376611840 CREATE UNDO TABLESPACE "UNDOTBS1" DATAFILE SIZE 26214400 AUTOEXTEND ON NEXT 5242880 MAXSIZE 32767M BLOCKSIZE 8192 EXTENT MANAGEMENT LOCAL AUTOALLOCATE ALTER DATABASE DATAFILE '+DATA/fp/datafile/undotbs1.265.1006784737' RESIZE 886046720 CREATE TEMPORARY TABLESPACE "TEMP" TEMPFILE SIZE 8307867648 AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M
DBMS_LOB.SUBSTR(DBMS_METADATA.GET_DDL('TABLESPACE',TS.TABLESPACE_NAME)) -------------------------------------------------------------------------------- EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1048576 CREATE TABLESPACE "USERS" DATAFILE SIZE 5242880 AUTOEXTEND ON NEXT 1310720 MAXSIZE 32767M LOGGING ONLINE PERMANENT BLOCKSIZE 8192 EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO
ALTER DATABASE DATAFILE '+DATA/fp/datafile/users.266.1006784737' RESIZE 580648960 CREATE UNDO TABLESPACE "UNDOTBS2" DATAFILE SIZE 26214400 AUTOEXTEND ON NEXT 26214400 MAXSIZE 32767M BLOCKSIZE 8192 EXTENT MANAGEMENT LOCAL AUTOALLOCATE ALTER DATABASE DATAFILE '+DATA/fp/datafile/undotbs2.14573.1006785011' RESIZE 3643801600
CREATE TABLESPACE "EXAMPLE2" DATAFILE SIZE 104857600 AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M NOLOGGING ONLINE PERMANENT BLOCKSIZE 8192 EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO ALTER DATABASE DATAFILE '+DATA/fp/datafile/example.14574.1006784845' RESIZE 363069440 CREATE BIGFILE TABLESPACE "SH" DATAFILE SIZE 1073741824 AUTOEXTEND ON NEXT 104857600 MAXSIZE 33554431M LOGGING ONLINE PERMANENT BLOCKSIZE 8192 EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO ALTER DATABASE DATAFILE '+DATA/fp/datafile/sh.14578.1007732255' RESIZE 33894170624 CREATE BIGFILE TABLESPACE "MA" DATAFILE SIZE 1073741824 AUTOEXTEND ON NEXT 104857600 MAXSIZE 33554431M LOGGING ONLINE PERMANENT BLOCKSIZE 8192 EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO ALTER DATABASE DATAFILE '+DATA/fp/datafile/ma.14576.1007732257' RESIZE 5477761024 CREATE BIGFILE TABLESPACE "SH2" DATAFILE SIZE 104857600 AUTOEXTEND ON NEXT 104857600 MAXSIZE 33554431M LOGGING ONLINE PERMANENT BLOCKSIZE 8192 EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO CREATE TABLESPACE "TE" DATAFILE '+DATA/fp/datafile/te.dbf'
SIZE 524288000 AUTOEXTEND ON NEXT 8192 MAXSIZE 32767M LOGGING ONLINE PERMANENT BLOCKSIZE 8192 EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO ALTER DATABASE DATAFILE '+DATA/fpmai/datafile/te.dbf' RESIZE 1396572160 11 rows selected.
在新的机器的oracle上执行（单机版）
1、创建表空间
oracle@prd:/home/oracle$export ORACLE_SID=fp
oracle@prd:/home/oracle$echo $ORACLE_SID
fp
oracle@prd:/home/oracle$sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Mon Mar 21 14:23:59 2022
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
CREATE BIGFILE TABLESPACE "SH2" DATAFILE '/u02/oracle/oradata/fp/sh201.dbf'
SIZE 104857600 AUTOEXTEND ON NEXT 104857600 MAXSIZE 33554431M
LOGGING ONLINE PERMANENT BLOCKSIZE 8192 EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO;
Tablespace created.
2、创建用户
oracle@prd:/home/oracle$export ORACLE_SID=fp
oracle@prd:/home/oracle$echo $ORACLE_SID
fp
oracle@prd:/home/oracle$sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Mon Mar 21 14:23:59 2022
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> create user SHFPPJ identified by Shfpbi12 default tablespace SHFPPJ;
User created.
3、用户授权
oracle@prd:/home/oracle$export ORACLE_SID=fp
oracle@prd:/home/oracle$echo $ORACLE_SID
fp
oracle@prd:/home/oracle$sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Mon Mar 21 14:23:59 2022
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> grant connect,resource,dba to SH2;
Grant succeeded.
SQL> grant create table to SH2;
Grant succeeded.
SQL> grant create tablespace to SH2;
Grant succeeded.
SQL> grant execute any procedure to SH2;
Grant succeeded.
SQL> grant create view to SH2;
Grant succeeded.
SQL> grant create session to SH2;
Grant succeeded.
SQL> grant select any table TO SH2;
Grant succeeded.
SQL> grant unlimited tablespace to SH2;
Grant succeeded.
SQL> grant delete any table to SH2;
Grant succeeded.
SQL> grant update any table to SH2;
Grant succeeded.
查看库中的信息
1、 查查看用户和默认表空间的关系。 SQL> set pagesize 0 SQL> set line 9999 SQL> select username,default_tablespace from dba_users; SYS SYSTEM SYSTEM SYSTEM SH SH SH2 SH2 OUTN SYSTEM APPQSSYS SYSAUX DBSNP SYSAUX DIP SH ORACLE_OCM SH 9 rows selected. 2、查看表空间的名称及大小 SQL>SELECT t.tablespace_name, round(SUM(bytes / (1024 * 1024)), 0) ts_size FROM dba_tablespaces t, dba_data_files d WHERE t.tablespace_name = d.tablespace_name GROUP BY t.tablespace_name;
SYSAUX 325 UNDOTBS1 200 SYSTEM 350 SH2 100 SH 23648 3、查看表空间物理文件的名称及大小 (红色业务表) SQL>SELECT tablespace_name, file_id, file_name, round(bytes / (1024 * 1024), 0) total_space FROM dba_data_files ORDER BY tablespace_name;
SH 4 /u01/oracle/oradata/fp/sh01.dbf 23648 SH2 5 /u01/oracle/oradata/fp/sh201.dbf 100 SYSAUX 2 /u01/oracle/oradata/fp/sysaux01.dbf 325 SYSTEM 1 /u01/oracle/oradata/fp/system01.dbf 350 UNDOTBS1 3 /u01/oracle/oradata/fp/undotbs1.dbf
