Oracle Statements

数据库  /  管理员 发布于 5年前   290

Oracle Statements

--create tablespace
CREATE TABLESPACE "TABLESPACE_NAME" LOGGING
DATAFILE 'F:\oracle\product\10.2.0\oradata\orcl\TABLESPACE_DATA_NAME.dbf' SIZE 200 M
AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED EXTENT
MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

 

-- Create an user
create user USER_NAME
identified by "password"
default tablespace SPACE_NAME
temporary tablespace TEMP
profile DEFAULT;

 

-- Grant/Revoke role privileges
grant connect to USER_NAME;
grant dba to USER_NAME;

 

-- Grant/Revoke system privileges
grant unlimited tablespace to USER_NAME;

 

数据库DB Link：

select * from dba_db_links;

create public database link link名称 connect to 远端用户名 identified by "远端"
using '(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 远端IP)(PORT = 1521)) )
(CONNECT_DATA = (SERVICE_NAME = 远端实例名)))';

select 'create table '||table_name ||' as select * from '||table_name||'@link名称;' from user_tables;

select 'drop table '|| table_name ||';' as aa from user_tables;

 

drop public database link dblink_name;

导出数据文件

exp 用户名/密码@实例名 file=将要保存的数据文件名

 

导入数据库
imp 用户名/密码@实例名 file=要导入的数据文件名 full=y

 

导入/导出指定表

imp user/password@实例名 file=D:\sampleDB.dmp log=D:\imp.log fromuser=userName tables=(table1,table2)

 

exp user/password@实例名 file=d:/sampleDB.dmp tables=(table1,table2)

 

 

用户管理：进入sqlplus / as sysdba

建用户及授权
create user 用户名 identified by 密码 default tablespace users temporary tablespace temp;

grant connect,resource,dba to 用户名;

 

删除用户
drop user 用户名 cascade;