oracle简单使用

1、创建用户与用户授权
create user username identified by pwd;–创建用户
grant connect,resource,dba,sysdba to username;–授权
2、创建表空间/临时表空间、删除表空间
create tablespace tablespaceName
logging
datafile ‘H:\app\Administrator\oradata\orcl\tablespaceName.dbf’
size 50m
autoextend on
next 50m maxsize 20480m
extent management local;

create temporary tablespace temp
tempfile 'H:\app\Administrator\oradata\orcl\temp.dbf'
size 50m
autoextend on
next 50m maxsize 20480m
extent management local;

drop tablespace temp including contents and datafiles;

3、导入导出
3.1 exp和imp
exp username/password@ip/orcl file=d:\orclfile.dmp owner=(username);
imp username/password@ip/orcl file=d:\orclfile.dmp fromuser=(username) touser=(username);
3.2 expdp和impdp
A、创建创建文件夹和目录
手动创建一个目录H:\app\WZELearning
B、sql创建目录
create directory WZELearning as ‘H:\app\PATH’;
C、授权目录给用户
grant read,write on directory PATH to username;
D、导出
expdp username/password@orcl directory=PATH dumpfile=elms0827.dmp schemas=username;
E、导出
先把导入的.dmp文件放到PATH对应的目录下
执行命令
impdp username/password@orcl directory=PATH dumpfile=elms0827.dmp schemas=username;
4、设置密码不过期
查询用户的profile
SELECT username,PROFILE FROM dba_users;
查看用户密码过期时间
SELECT * FROM dba_profiles s WHERE s.profile=‘DEFAULT’ AND resource_name=‘PASSWORD_LIFE_TIME’;
改为永不过期
ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;
5、查看oracle字符集
select userenv(‘language’) from dual;
6、配置TNS
在G:\app\Administrator\product\11.2.0\dbhome_1\NETWORK\ADMIN目录下修改tnsnames.ora文件
并重启监听服务
ORACLR_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
(CONNECT_DATA =
(SID = CLRExtProc)
(PRESENTATION = RO)
)
)

ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.159)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)