从头开始db-oracle

最新推荐文章于 2025-08-04 16:22:27 发布

weixin_34006468

最新推荐文章于 2025-08-04 16:22:27 发布

阅读量78

点赞数

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/createyuan/p/5877052.html

本文详细介绍了如何在 Oracle 数据库中使用 RPM 安装包安装软件、配置环境变量、创建和管理用户、备份与恢复数据等操作。同时，还提供了导出和导入数据的具体步骤，包括不同模式的导出与导入方法。

rpm -ivh http://download.fedoraproject.org/pub/epel/6/i386/epel-release-6-7.noarch.rpm
rpm -ivh http://download.fedoraproject.org/pub/epel/6/x86_64/epel-release-6-8.noarch.rpm
rpm --import /etc/pki/rpm-gpg/RPM-GPG-KEY-EPEL-6
yum install rlwrap
su - oracle
echo "alias sqlplus='rlwrap sqlplus'" >> ~/.bash_profile
source ~/.bash_profile
sqlplus / as sysdba
show user;
alter user hr identified by zxcasd account unlock;
exit
sqlplus hr/zxcasd
show user;
select * from session_privs;
select * from dba_sys_privs;
select * from dba_tab_privs;
alter user hr identified by 123456 replace zxcasd;

select table_name from user_tables;

set pagesize 200

set linesize 200
desc jobs
select * from jobs;
desc locations
select * from locations;

select userenv('language') from dual;

exit

export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK

session profile

[oracle@allinone ~]$ cat login.sql
set pagesize 200
set linesize 200
define _editor=vi
set time on

set timing on

glogin profile

vi /u01/app/oracle/product/11.2.0.4/dbhome_1/sqlplus/admin/glogin.sql

先备份相关内容，完全，用户，表
exp help=y
exp hr/123456
EXP SYSTEM/123456 BUFFER=64000 FILE=full.dmp FULL=Y
EXP hr/123456 BUFFER=64000 FILE=hr.dmp OWNER=hr
EXP hr/123456 BUFFER=64000 FILE=hr-xue.dmp TABLES=xue

imp hr/123456
IMP SYSTEM/123456 BUFFER=64000 FILE=full.dmp FULL=Y
IMP hr/123456 BUFFER=64000 FILE=hr.dmp FROMUSER=hr TOUSER=hr
IMP hr/123456 BUFFER=64000 FILE=hr-xue.dmp TABLES=xue ignore=y

只测试了导出的几种模式，和导入的表模式,用户模式。其它的导入没有成功。

如果表结构存在，imp的时候要加入ignore=y,忽略错误。或者直接删除表数据及表结构，用drop不用delete.

增删改相关行或表，再用备份的数据进行恢复。
sqlplus hr/123456
set pagesize 200
set linesize 200
create table xue(id integer,name varchar(25));
insert into xue values(1,'wang');
insert into xue values(2,'liu');
commit;
将表删除
drop table xue;

如果有索引关系的表，用下面
drop table mytest cascade constraints;
留下表结构，删除一行或所有数据。
delete from regions where region_id=5;
delete from regions

用户模式恢复

删除用户下所有对象，然后再恢复，以便没有残留。

用sys帐户去操作

drop user hr cascade;
要退出所有的hr用户连接，才能正确执行。
drop tablespace USERS INCLUDING CONTENTS;
不能删除默认永久表空间。
create user hr profile default identified by 123456 default tablespace USERS temporary tablespace TEMP account unlock;
grant dba to hr;
grant connect,resource to hr;

imp hr/123456 buffer=64000 file=hr.dmp fromuser=hr touser=hr　　就不会报错了。

SQL> select username,account_status from dba_users;

USERNAME               ACCOUNT_STATUS
------------------------------ --------------------------------
SYS                   OPEN
SYSTEM                   OPEN
PLATFORM_USER               OPEN
PLATFORM_MALL               OPEN
LCPSYS                   OPEN
MONITOR                OPEN
OUTLN                   EXPIRED & LOCKED
MGMT_VIEW               EXPIRED & LOCKED

转载于:https://www.cnblogs.com/createyuan/p/5877052.html