用户
创建用户并指定表空间
create user username
identified by password
default tablespace user_data
temporary tablespace user_temp;
例如
create user bh
identified by bh
default tablespace user_data
temporary tablespace user_temp;
给用户授予权限
grant connect,resource,dba to username;
例如
grant connect,resource,dba to bh;
删除用户
drop user bh
drop user bh cascade;
表,表空间
创建表
CREATE TABLE BH.PRODUCT
(
PROID CHAR(10),
CATEGORYID CHAR(4),
PRONAME VARCHAR2(20),
DESCRIPTION VARCHAR2(500)
);
查看有哪些表空间
SQL> SELECT TABLESPACE_NAME FROM DBA_TABLESPACES;
TABLESPACE_NAME
------------------------------
SYSTEM
UNDOTBS
SYSAUX
TEMP
查看当前临时表空间
SQL> select tablespace_name,file_name,bytes/1024/1024 file_size,autoextensible from dba_temp_files;
TABLESPACE_NAME FILE_NAME FILE_SIZE AUT
TEMP /u01/app/oracle/oradata/rac1/temp01.dbf 20 YES
--tablespace_name 表空间名字
--file_name 表空间存放地址
--bytes/1024/1024 file_size 表空间大小
--autoextensible 是否自动扩展
创建临时表空间
create temporary tablespace user_temp
tempfile '/u01/app/oracle/oradata/rac1/user_temp.dbf'
size 50m
autoextend on
next 50m maxsize 20480m
extent management local;
SQL> select tablespace_name,file_name,bytes/1024/1024 file_size,autoextensible from dba_temp_files;
TABLESPACE_NAME
------------------------------
FILE_NAME
--------------------------------------------------------------------------------
FILE_SIZE AUT
---------- ---
TEMP
/u01/app/oracle/oradata/rac1/temp01.dbf
20 YES
USER_TEMP
/u01/app/oracle/oradata/rac1/user_temp.dbf
5 YES
创建数据表空间
create tablespace user_data
logging
datafile '/u01/app/oracle/oradata/rac1/user_data.dbf'
size 50m
autoextend on
next 50m maxsize 20480m
extent management local;
Oracle中如何查看SERVICE_NAME