oracle运维相关

最新推荐文章于 2025-06-18 08:16:17 发布

another_liu

最新推荐文章于 2025-06-18 08:16:17 发布

阅读量1.3k

点赞数

CC 4.0 BY-SA版权

分类专栏： oracle

本文链接：https://blog.youkuaiyun.com/another_liu/article/details/79591073

oracle 专栏收录该内容

3 篇文章

订阅专栏

本文详细介绍Oracle数据库的EXPDP和IMPDP工具使用方法，包括导出与导入DMP文件的过程，以及表空间管理和数据库启停步骤。同时，还提供了创建用户、授权、查看表空间使用情况等实用命令。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

EXPDP和IMPDP是服务端的工具程序，他们只能在ORACLE服务端使用，不能在客户端使用。

操作系统认证登录sqlplus

sqlplus  / as sysdba

1，导出DMP文件

使用DBA用户登录数据库，查看资源文件

select * from all_directories;

新建资源目录

create directory udir as '/ufida/NC65/dmptmp';

创建导出操作用户

CREATE USER dmpuser IDENTIFIED BY dmpuser;
grant connect,resource,dba to dmpuser;

给用户dmpuser授权资源目录

grant read,write on directory udir to dmpuser;

切换到oracle用户：su - orale

使用dmpuser用户导出KB0114

expdp dmpuser/dmpuser@orcl schemas=KB0114 dumpfile=dmp20180317.dmp DIRECTORY=udir

报错ora390002,目录权限问题，使用root用户给oarcle用户授权

chown -R oracle:oinstall dmptmp/

2，导入DMP文件

在本地数据库创建用户并授权

CREATE USER kb0114 IDENTIFIED BY kb0114;
grant connect,resource,dba to kb0114;
GRANT UNLIMITED TABLESPACE TO kb0114;

创建导入操作用户

CREATE USER dmpuser IDENTIFIED BY dmpuser;
grant connect,resource,dba to dmpuser;

导入命令

impdp dmpuser/dmpuser DIRECTORY=UDIR DUMPFILE=dmp20180317.dmp SCHEMAS=kb0114

如果改变表的owner，使用下面命令

impdp dmpuser/dmpuser DIRECTORY=UDIR DUMPFILE=dmp20180317.dmp  REMAP_SCHEMA=nc65gold:kb0114

3，查看表空间使用情况语句

SELECT Upper(F.TABLESPACE_NAME) "表空间名",
       D.TOT_GROOTTE_MB "表空间大小(M)",
       D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)",
       To_char(Round((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,
                     2),
               '990.99') || '%' "使用比",
       F.TOTAL_BYTES "空闲空间(M)",
       F.MAX_BYTES "最大块(M)"
  FROM (SELECT TABLESPACE_NAME,
               Round(Sum(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,
               Round(Max(BYTES) / (1024 * 1024), 2) MAX_BYTES
          FROM SYS.DBA_FREE_SPACE
         GROUP BY TABLESPACE_NAME) F,
       (SELECT DD.TABLESPACE_NAME,
               Round(Sum(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB
          FROM SYS.DBA_DATA_FILES DD
         GROUP BY DD.TABLESPACE_NAME) D
 WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
 ORDER BY 1

查看表空间是否自动扩展

select tablespace_name,file_name,autoextensible from dba_data_files;

开启自动扩展功能语法：

alter database datafile '对应的数据文件路径信息' autoextend on;

关闭自动扩展功能语法：

alter database datafile '对应的数据文件路径信息' autoextend off;

增加数据文件

ALTER TABLESPACE NNC_DATA01 ADD DATAFILE
'D:\ORACLE\PRODUCT\10.2.0\ORADATA\EDWTEST\APP04.DBF' SIZE 5G
AUTOEXTEND ON NEXT 500M MAXSIZE 30G;

4，oracle 关闭与启动

切换到oracle用户

[root@ZB0006 /]# su - oracle

停止监听

[oracle@ZB0006 ~]$ lsnrctl stop

登录sqlplus

[oracle@ZB0006 ~]$ sqlplus / as sysdba

停止数据库

SQL> shutdown immediate

启动数据库：

SQL> startup
ORACLE instance started.

Total System Global Area 2.6991E+10 bytes
Fixed Size		    2213976 bytes
Variable Size		 2.5770E+10 bytes
Database Buffers	 1073741824 bytes
Redo Buffers		  145174528 bytes
Database mounted.
Database opened.
SQL>

启动监听

[oracle@ZB0006 ~]$ lsnrctl start

查看监听状态

[oracle@ZB0006 ~]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 17-MAR-2018 14:34:12

Copyright (c) 1991, 2009, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date                17-MAR-2018 14:33:35
Uptime                    0 days 0 hr. 0 min. 37 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/ZB0006/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ZB0006)(PORT=1521)))
Services Summary...
Service "orcl" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orclXDB" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@ZB0006 ~]$

5,其他语句

删除用户

drop user nc6668 cascade ;

创建用户授权任意使用表空间

CREATE USER nc6668 IDENTIFIED BY nc6668;  
grant connect,resource,dba to nc6668;  
GRANT UNLIMITED TABLESPACE TO nc6668;

指定用户使用某个表空间

alter user nc6668  default tablespace NC_CLONE;

查看用户使用的表空间

select username,default_tablespace from dba_users;

查看数据文件

select t1.name,t2.name  from v$tablespace t1,v$datafile t2 where t1.ts# = t2.ts#;

增加数据文件

ALTER TABLESPACE NNC_INDEX01 ADD DATAFILE  
'/ufida/NC65/data/nnc_index02.dbf' SIZE 5000M  
AUTOEXTEND ON NEXT 1024M ;

删除ORACLE中针对某个schema的统计信息

execute dbms_stats.delete_schema_stats('KB1212');

创建表空间

create tablespace NNC_DATA01 datafile 'C:\app\ldwind7\oradata\orcl\nnc_data01.dbf' size 500M autoextend on next 50M extent management local uniform size 512k;
create tablespace NNC_INDEX01 datafile 'C:\app\ldwind7\oradata\orcl\nnc_index01.dbf' size 500M autoextend on next 50M extent management local uniform size 512k;
create user livetest13 identified by livetest13 default tablespace NNC_DATA01 temporary tablespace temp;
grant connect,resource,dba to livetest13;
GRANT UNLIMITED TABLESPACE TO livetest13;

查看数据库实例名

show parameter instance_name;

修改数据库字符集



SQL> conn /as sysdba
Connected.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 1603411968 bytes
Fixed Size		    2213776 bytes
Variable Size		  469764208 bytes
Database Buffers	 1124073472 bytes
Redo Buffers		    7360512 bytes
Database mounted.
SQL> alter system enable restricted session;

System altered.

SQL> alter system set job_queue_processes=0;

System altered.

SQL> alter system set aq_tm_processes=0;

System altered.

SQL> alter database open;

Database altered.

SQL> alter database character set zhs16gbk;
alter database character set zhs16gbk
*
ERROR at line 1:
ORA-12712: new character set must be a superset of old character set

SQL> alter database character set internal_use zhs16gbk;

Database altered.

SQL> select userenv('language') from dual ;


USERENV('LANGUAGE')
----------------------------------------------------
AMERICAN_AMERICA.ZHS16GBK

--重新收集表的统计信息

select a.TABLE_NAME,

       num_rows * avg_row_len / 1024 / 1024 / 1024,

       'call dbms_stats.gather_table_stats(''' || 'hxyl''' || ',''' ||

       a.TABLE_NAME || ''');'

  from user_tables a

order by nvl(num_rows * avg_row_len, 0) desc;

--Oracle重建所有表的索引

SELECT 'alter index ' || INDEX_NAME || ' rebuild online nologging;'

  FROM USER_INDEXES

WHERE TABLESPACE_NAME like 'NNC%'

   AND STATUS = 'VALID'

   AND TEMPORARY = 'N'

   AND INDEX_TYPE = 'NORMAL'

   AND PARTITIONED = 'NO'

   and table_owner='HXYL'