Oracle Statements

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;

 

配置sqlplus:

export ORACLE_SID=实例名

export ORACLE_BASE=/oracle/

export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/Db_1

 

ln -s $ORACLE_HOME/bin/sqlplus /usr/bin

 

 sqlplus /nolog

 conn / as sysdba

 

select sysdate from dual;

 

cd $ORACLE_HOME/network/admin

cp tnsnames.ora tnsnames20160101.ora

vi tnsnames.ora

添加：

TEST =  登陆的SID  

(DESCRIPTION =  

    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.21.1)(PORT = 1521))  IP地址和端口号  

    (CONNECT_DATA =  

      (SERVER = DEDICATED)  

      (SERVICE_NAME = orcl)  要链接数据库名  

    )  

)  

 

:wq

 

设置一下配置文件权限，以便其它用户使用

chmod 755 tnsnames.ora

 

sqlplus 用户名/密码@登陆的SID