LINUX下ORACLE表空间及用户创建
记录详细过程以备使用
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as sys@ip:port/sid AS SYSDBA
管理员用户登录oracle数据库
1、创建临时表空间
select name from v$tempfile;查出当前数据库临时表空间,下面创建临时表空间会使用里面的存放路径;
SQL> select name from v$tempfile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/temp01.dbf
创建临时表空间:使用上面查询到的临时表空间路径
SQL> create temporary tablespace testdb01_temp tempfile '/u01/app/oracle/oradata/orcl/testdb01_temp.dbf' size 100m reuse autoextend on next 20m maxsize unlimited;
Tablespace created
创建临时表空间后检查
SQL> select name from v$tempfile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/temp01.dbf
/u01/app/oracle/oradata/orcl/testdb01_temp.dbf
2、创建表空间
select name from v$datafile;查询出当前数据库表空间,下面创建表空间会使用里面的路径
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/system01.dbf
/u01/app/oracle/oradata/orcl/undotbs01.dbf
/u01/app/oracle/oradata/orcl/sysaux01.dbf
/u01/app/oracle/oradata/orcl/users01.dbf
/u01/app/oracle/oradata/orcl/acctdat.dbf
/u01/app/oracle/oradata/orcl/acctidx.dbf
/u01/app/oracle/oradata/orcl/datadat.dbf
/u01/app/oracle/oradata/orcl/dataidx.dbf
/u01/app/oracle/oradata/orcl/fildat.dbf
/u01/app/oracle/oradata/orcl/filidx.dbf
/u01/app/oracle/oradata/orcl/hisdat.dbf
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/hisidx.dbf
/u01/app/oracle/oradata/orcl/userdat.dbf
/u01/app/oracle/oradata/orcl/useridx.dbf
/u01/app/oracle/oradata/orcl/archdat.dbf
/u01/app/oracle/oradata/orcl/archidx.dbf
16 rows selected.
创建表空间:使用上面查询到的表空间路径
SQL> create tablespace testdb01 datafile '/u01/app/oracle/oradata/orcl/testdb01.dbf' size 100M reuse autoextend on next 40M maxsize unlimited default storage(initial 128k next 128k minextents 2 maxextents unlimited);
Tablespace created
创建临时表空间后检查
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/system01.dbf
/u01/app/oracle/oradata/orcl/undotbs01.dbf
/u01/app/oracle/oradata/orcl/sysaux01.dbf
/u01/app/oracle/oradata/orcl/users01.dbf
/u01/app/oracle/oradata/orcl/acctdat.dbf
/u01/app/oracle/oradata/orcl/acctidx.dbf
/u01/app/oracle/oradata/orcl/datadat.dbf
/u01/app/oracle/oradata/orcl/dataidx.dbf
/u01/app/oracle/oradata/orcl/fildat.dbf
/u01/app/oracle/oradata/orcl/filidx.dbf
/u01/app/oracle/oradata/orcl/hisdat.dbf
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/hisidx.dbf
/u01/app/oracle/oradata/orcl/userdat.dbf
/u01/app/oracle/oradata/orcl/useridx.dbf
/u01/app/oracle/oradata/orcl/archdat.dbf
/u01/app/oracle/oradata/orcl/archidx.dbf
/u01/app/oracle/oradata/orcl/testdb01.dbf
17 rows selected.
3、创建用户并指定表空间
SQL> create user testdb01 identified by testdb01 default tablespace testdb01 temporary tablespace testdb01_temp;
User created
4、赋予用户权限
SQL> grant dba to testdb01;
Grant succeeded
5、设置ORACLE11g登录名和密码不区分大小写
oracle 11g 以前的版本的用户名和密码是不区分大小写的;
oracle 11g 用户名和密码默认区分大小写,
可更改alter system set sec_case_sensitive_logon=false; 设置改为不区分大小写,
用DBA用户登录执行就好了,修改后立即生效,不用重启数据库实例。
标签:创建,app,dbf,oracle,orcl,oradata,LINUX,ORACLE,u01 From: https://www.cnblogs.com/dianli_jingjing/p/17799554.html