oracle随笔

1.创建永久表空间

create tablespace test_data

datafile '/data/oracle/app/oracle/oradata/orcl/test_data.dbf'

size 100m

autoextend on

next 5m

maxsize unlimited   --或者unlimited

extent MANAGEMENT LOCAL;  

2.创建临时表空间

create temporary tablespace temp_test

tempfile '/data/oracle/app/oracle/oradata/orcl/temp_test.dbf'

size 50m

autoextend on

next 5m

maxsize unlimited  --或者unlimited

extent MANAGEMENT LOCAL;  

3.创建角色，赋予权限

create role sourcedata;

grant create session to sourcedata; --创建session的权限

grant create table to sourcedata;

grant create view to sourcedata;

grant create procedure to sourcedata;

grant create sequence to sourcedata;

grant create synonym to sourcedata;

--grant unlimited tablespace to sourcedata;    

4.创建用户

create user admin

identified by admin123

default tablespace test_data  -- 永久表空间

temporary tablespace temp_test; --临时表空间

grant sourcedata to admin; --赋予用户角色权限

grant unlimited tablespace to admin;  

5.查询表空间路径，表空间名称

select file_name,tablespace_name from dba_data_files;  

6.dba_tablespaces | user_tablespaces 和 dba_users | user_users 这4个数据字典

(1).查询 default_tablespace, temporary_tablespace

     select tablespace_name from dba_tablespaces;

     select tablespace_name from user_tablespaces;

     select default_tablespace, temporary_tablespace from dba_users where username='SCOTT';   -- oracle用户名大写

     select default_tablespace, temporary_tablespace from user_users where username='SCOTT';

(2).修改 tablespace_name

     alter user username default tablespace tablespace_name; -- 修改默认表空间名称

     alter user username temp tablespace tablespace_name; -- 修改默认表空间名称

7.dba_data_files 和 user_data_files 数据字典

exp 导出数据  

imp 导入数据