创建表空间
create temporary tablespace user_name_temp tempfile ‘/oradata/ORA11G/user_name_temp.dbf‘ size 50m autoextend on next 50m maxsize unlimited --maxsize 2048m extent management local; create tablespace user_name_tb logging datafile ‘/oradata/ORA11G/user_name_tb2.dbf‘ size 32m autoextend on next 32m maxsize unlimited extent management local default compress;
创建用户
创建用户：
create user user_name identified by user_name;
--有特殊字符需要加双引号 create user user_name identified by "123！" default tablespace user_name_tb temporary tablespace user_name_temp; grant create session to user_name; grant unlimited tablespace to user_name; grant create any table to user_name; grant drop any table to user_name; grant select any table to user_name; grant insert any table to user_name; grant update any table to user_name; grant create any procedure to user_name; grant execute any procedure to user_name; --drop user user_name; --drop tablespace user_name_tb;
插入任何数据显示：ora-01658 unable to create initial extent for segment in tablespace
alter tablespace {表空间名字} add datafile ‘物理数据文件路径‘ SIZE 『初始大小M』 AUTOEXTEND ON NEXT 『自动扩展大小M』
alter tablespace SDK_TB add datafile ‘/oradata/ORA11G/sdk_tb2.dbf‘ size 1000m autoextend on next 200m
create temporary tablespace dev_temp tempfile ‘/data/phonedb/datafile/dev_temp.dbf‘ size 50m autoextend on next 50m maxsize unlimited -- 2048m extent management local; create tablespace dev_tb logging datafile ‘/data/phonedb/datafile/dev_tb.dbf‘ size 32m autoextend on next 32m maxsize unlimited extent management local default compress;
create user user_name identified by "aaaaaa"
default tablespace DEV_TB
temporary tablespace DEV_TEMP;
-- Grant/Revoke role privileges
grant connect to user_name;
grant resource to user_name;
-- Grant/Revoke system privileges
grant create session to user_name;
grant select any table to user_name;
grant unlimited tablespace to dev;
修改用户默认表空间：
alter user user_name default tablespace dev_tb;
alter user user_name temporary tablespace dev_temp;
查看数据文件所在路径：
select * from dba_data_files order by tablespace_name, file_name;
grant select on 表名 to 用户名;
修改密码：
alter user 用户名 identified by 密码;