Oracle Statements

 

--create tablespaceCREATE 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