当前位置:首页 > 开发 > 数据库 > 正文

Oracle Statements

发表于: 2011-05-23   作者:crabdave   来源:转载   浏览:
摘要: Oracle Statements --create tablespaceCREATE TABLESPACE "TABLESPACE_NAME" LOGGINGDATAFILE 'F:\oracle\product\10.2.0\oradata\orcl\TABLESPACE_DATA_NAME.dbf' SIZE 200 MAUTOEXTEND ON NEXT 10

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;

 

 

Oracle Statements

  • 0

    开心

    开心

  • 0

    板砖

    板砖

  • 0

    感动

    感动

  • 0

    有用

    有用

  • 0

    疑问

    疑问

  • 0

    难过

    难过

  • 0

    无聊

    无聊

  • 0

    震惊

    震惊

版权所有 IT知识库 CopyRight © 2009-2015 IT知识库 IT610.com , All Rights Reserved. 京ICP备09083238号