oracle user, schema, tablespace, datafile

在oracle中：

1. SQL> shutdown immediate
2. SQL> startup
3. SQL> grant dba to user01 identified by pwduser01;  /* 创建用户user01, 密码 pwduser01; 自动生成 schema user01; 生成 tablespace, datafile */
4. SQL> select username, default_tablespace from dba_users; /* 查看新创建用户的表空间名称 */
5. SQL> select a.tablespace_name,a.bytes/1024/1024 "Sum MB",(a.bytes-b.bytes)/1024/1024 "used MB",b.bytes/1024/1024 "free MB", round(((a.bytes-b.bytes)/a.bytes)*100,2) "percent_used"  from (select tablespace_name,sum(bytes) bytes from dba_data_files group by tablespace_name) a, (select tablespace_name,sum(bytes) bytes,max(bytes) largest from dba_free_space group by tablespace_name) b where a.tablespace_name=b.tablespace_name order by ((a.bytes-b.bytes)/a.bytes) desc;  /* 查看表空间总大小，已用大小等信息 */
6. SQL> select * from dba_data_files ; /* 查看表空间对应的数据文件信息 */
7. SQL> alter database datafile '/fs-01/databases/orcl/system-01.dbf' resize 30000m; /* 如果表空间过小，调整表空间大小（ = 调整相应数据文件大小） */
8. SQL> alter database datafile '/fs-01/databases/orcl/system-01.dbf' autoextend on;
9. SQL> create spfile from pfile;
10. SQL> shutdown immediate;
11. SQL> startup;
12. SQL> alter system set shared_pool_size=512M scope=spfile;
13. SQL> alter system set processes=400 scope=spfile;
14. SQL> alter system set sessions=445 scope=spfile;
15. SQL> alter system set transactions=495 scope=spfile;
16. SQL> shutdown immediate
17. SQL> exit

 

查看表空间:

SQL> select * from dba_tablespaces;