DBA命令大全

/1 运行SQLPLUS工具 sqlplus

2 以OS的默认身份连接 / as sysdba 3 显示当前用户名 show user

4 直接进入SQLPLUS命令提示符 sqlplus /nolog

5 在命令提示符以OS身份连接 connect / as sysdba 6 以SYSTEM的身份连接

connect system/xxxxxxx@服务名 7 显示当然用户有哪些表 select * from tab;

8 显示有用户名和帐户的状态

select username,account_status from dba_users; 9 将SCOTT帐号解锁(加锁)

alter user scott account unlock(lock); 10 以SCOTT的身份连接并且查看所属表 connect scott/tiger select * from tab;

11 查看EMP的表结构及记录内容 desc emp

select empno,ename from emp;

12 以OS的身份登看SGA,共享池,CACHE的信息 connect / as sysdba

show sga

select name,value/1024/1024 from v$sga;

show parameter shared_pool_size

select value/1024/1024 from v$parameter where name ='shared_pool_size'; show parameter db_cache_size

select value/1024/1024 from v$parameter where name ='db_cache_size'; 13 查看所有含有SIZE的信息 show parameter size

bitmap_merge_area_size integer 1048576 create_bitmap_area_size integer 8388608

db_16k_cache_size big integer 0 db_2k_cache_size big integer 0 db_32k_cache_size big integer 0 db_4k_cache_size big integer 0 db_8k_cache_size big integer 0 db_block_size integer 4096

db_cache_size big integer 33554432 db_keep_cache_size big integer 0 db_recycle_cache_size big integer 0 NAME TYPE VALUE

------------------------------------ ----------- ------------- global_context_pool_size string hash_area_size integer 1048576

java_max_sessionspace_size integer 0 java_pool_size big integer 33554432 large_pool_size big integer 8388608 max_dump_file_size string UNLIMITED object_cache_max_size_percent integer 10 object_cache_optimal_size integer 102400 olap_page_pool_size integer 33554432

oracle_trace_collection_size integer 5242880 parallel_execution_message_size integer 2148

NAME TYPE VALUE

------------------------------------ ----------- ------------- sga_max_size big integer 143727516

shared_pool_reserved_size big integer 2516582 shared_pool_size big integer 50331648 sort_area_retained_size integer 0 sort_area_size integer 524288 workarea_size_policy string AUTO

14 显示SGA的信息 select * from v$sgastat; POOL NAME BYTES

----------- -------------------------- ---------- fixed_sga 453532

buffer_cache 33554432 log_buffer 656384

shared pool subheap 46884 shared pool KGK heap 3756 shared pool KQR M PO 586792 shared pool KQR S PO 180232 shared pool KQR S SO 5128

shared pool sessions 410720 shared pool sql area 2144664 shared pool 1M buffer 2098176

POOL NAME BYTES

----------- -------------------------- ---------- shared pool KGLS heap 901756 shared pool parameters 8352

shared pool free memory 38687204 shared pool PL/SQL DIANA 420816 shared pool FileOpenBlock 695504 shared pool PL/SQL MPCODE 135692 shared pool library cache 2985576 shared pool miscellaneous 4889396 shared pool MTTR advisory 21164 shared pool PLS non-lib hp 2068 shared pool XDB Schema Cac 4966300 POOL NAME BYTES

----------- -------------------------- ---------- shared pool joxs heap init 4220 shared pool kgl simulator 563260 shared pool sim memory hea 44184 shared pool table definiti 1728 shared pool trigger defini 1896 shared pool trigger inform 1140 shared pool trigger source 448 shared pool type object de 69120 shared pool Checkpoint queue 282304 shared pool VIRTUAL CIRCUITS 265160 shared pool dictionary cache 1610880 POOL NAME BYTES

----------- -------------------------- ---------- shared pool KSXR receive buffers 1033000 shared pool character set object 323724 shared pool FileIdentificatonBlock 323292 shared pool message pool freequeue 834752 shared pool KSXR pending messages que 841036 shared pool event statistics per sess 1718360 shared pool fixed allocation callback 180 large pool free memory 8388608 java pool free memory 33554432 已选择42行。

15 显示PGA的信息

select * from v$pgastat; NAME VALUE UNIT

---------------------------------------------------------------- ---------- ---------

aggregate PGA target parameter 16777216 bytes aggregate PGA auto target 7640064 bytes global memory bound 838656 bytes total PGA inuse 8293376 bytes total PGA allocated 13106176 bytes maximum PGA allocated 22090752 bytes total freeable PGA memory 0 bytes

PGA memory freed back to OS 0 bytes

total PGA used for auto workareas 0 bytes maximum PGA used for auto workareas 4096 bytes total PGA used for manual workareas 0 bytes NAME VALUE UNIT

---------------------------------------------------------------- ---------- ---------

maximum PGA used for manual workareas 4096 bytes over allocation count 0 bytes processed 8783872 bytes extra bytes read/written 0 bytes cache hit percentage 100 percent 已选择16行。

17 在$ORACLE_HOME/sqlplus/admin/glogin.sql中加入环境变量,以后每次启动生效 define _editor=vi set line 2000

18 将当前命令随加到文件中 save c:a.sql append

19 将指定文件的命读出缓冲区 get c:a.sql 20 执行脚本语句 @ c:a.sql

21 将输入保存到指定文件中 spool c:O.LOG select * from v$sga; spool off

22 设定行大小 set linesize 2000 23 设定页大小 set pagesize 10 24 设定字符列格式 col ename format a30 25 设定数字列格式

col sal format 999,999.999

26 10G查看文件$ORACLE_HOME/install/protlist显示端口 http://127.0.0.1:5560/isqlplus

9i查看文件$ORACLE_HOME/Apache/Apache/ports.ini显示端口 http://127.0.0.1:7778/isqlplus http://127.0.0.1:7778/isqlplusdba

27 启动Oracle 9i监听程序

Oracle的监听程序主要是为客户端的连接提供接口 $ lsnrctl start

28 关闭Oracle 9i监听程序 $ lsnrctl stop

29 启动Oracle Web Server

$ cd $ORACLE_HOME/Apache/Apache/bin $ ./startJServ.sh

/database/oracle/product/9i/Apache/Apache/bin/apachectl start: httpd started 30 关闭Oracle Web Server

$ cd $ORACLE_HOME/Apache/Apache/bin

$ ./stopJServ.sh

/database/oracle/product/9i/Apache/Apache/bin/apachectl stop: httpd stopped 31 启动Oracle Web Server后默认的端口号是7777 unix

cd $ORACLE_HOME/Apache/Apache/bin/

htpasswd $ORACLE_HOME/sqlplus/admin/iplusdba.pw admin windows

cd D:oracleora92ApacheApachebin

htpasswd D:oracleora92/sqlplus/admin/iplusdba.pw admin New password: *****

Re-type new password: ***** Adding password for user admin

32 pfile: $ORACLE_BASE/admin/实例名/Pfile spfile: $ORACLE_HOME/dbs

236 追加临时表空间

alter tablespace temp add tempfile 'd:oradatamydbtemp01.dbf' size 10m reuse; 237 Rman的连接 connect target /

238 显示要备份的数据库文件 report schema;

239 在RMAN的指定位置来备份文件 run {

allocate channel ch1 type disk format 'e:rman%U.bak' maxpiecesize=2g; backup database; }

240 在RMAN的显示威者默认设置 show all; backup database;

241 配置缺省的路径

CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT 'E:RMAN%U.BAK' MAXPIECESIZE 2G; 242 清除缺省配置

CONFIGURE CHANNEL DEVICE type DISK clear; 243 配置并行数

CONFIGURE DEVICE TYPE DISK PARALLELISM 2; 244 配置自动备份控制文件

CONFIGURE CONTROLFILE AUTOBACKUP on; 245 配置有益度

CONFIGURE RETENTION POLICY TO REDUNDANCY 2;

246 配置可以恢复到7天内的保留窗口

CONFIGURE RETENTION POLICY TO recovery window of 7; 247 备份表空间

backup tablespace users; 248 备份数据文件 backup datafile 6; 249 备份前一天的重做日志

backup archivelog until time 'sysdate-1'; 250 备份SP文件 backup spfile;

251 备份当前的控制文件 backup current controlfile; 252 列出所有的备份集 list backupset ;

253 列出所有的汇总备份集 list backupset summary; 254 列出具体的备份信息 list backupset 16 255 删除备份集13 delete backupset 13; 256 不提示删除所有的备份集 delete noprompt backupset;

257 凡是USERS的备份都删除

delete backup of tablespace users; 258 列出哪些不需要的备份集 report obsolete ; 259 边备份边压缩数据库

backup as compressed backupset database; 260 增量备份时的0级备份一个表空间

backup incremental level 0 tablespace users; 261 2级备份累积备份

backup incremental level 2 cumulative tablespace users; 262 删除指定日期的归档日志

DELETE ARCHIVELOG UNTIL TIME 'SYSDATE-1/24';

263 在RMAN恢复指定表空间 RUN {

SQL 'ALTER TABLESPACE USERS OFFLINE IMMEDIATE'; RESTORE TABLESPACE USERS;

RECOVER TABLESPACE USERS;

SQL 'ALTER TABLESPACE USERS ONLINE'; }

264 在RMAN的恢复控制文件 STARTUP NOMOUNT

RESTORE CONTROLFILE FROM AUTOBACKUP; ALTER DATABASE MOUNT; RESTORE DATABASE VALIDATE;

RECOVER DATABASE ;

ALTER DATABASE OPEN RESETLOGS;

265 查询数据库的字符集

select * from database_properties where property_name like 'NLS_CHA%'; NLS_LANG=SIMPLIFIED CHINESE_CHINA.ZHS16GBK

266 导出数据到一个文件中

exp userid=system/oracle file=full.dmp full=y

exp userid=system/oracle file=full1.dmp,full2.dmp,full3.dmp filesize=2g full=y direct=y log=full.log feedback=10000 267 导出指定用户的表

exp userid=system/oracle file=gary.dmp owner=gary,wei direct=y log=gary exp userid=system/oracle file=gary.dmp owner=gary direct=y log=gary.log 268 导出指定表

exp userid=system/oracle file=gary_a.dmp tables=gary.a direct=y log=gary 269 导出指定表

exp userid=system/oracle file=gary_a1.dmp tables=gary.a query='where owner=''GARY''' log=gary.LOG 270 查看导出的帮助信息 exp -help

271 删除gary的用户 drop user gary cascade;

272 重建用户GARY

CREATE USER \273 授权用户GARY

GRANT UNLIMITED TABLESPACE,CONNECT,RESOURCE TO \

274 导入用户GARY的数据

imp userid=system/oracle fromuser=gary touser=gary show=y

imp userid=system/oracle fromuser=gary touser=gary file=full.dmp log=imp.log imp userid=system/oracle fromuser=gary touser=wei file=gary.dmp log=imp.log 275 查询状态

select object_name,object_type,owner from dba_objects where status='INVALID'; EXEC UTL_RECOMP.RECOMP_PARALLEL

276 建立对象,允许可以用这个目录来导入导出 create directory mydir as 'e:expdata'; 277 授权用户的权限

grant read,write on directory mydir to gary;

278 导出数据

expdp userid=gary/gary dumpfile=gary directory=mydir PARALLEL=2 schemas=gary job_name=gary_job

expdp userid=gary/gary attach=gary_job 268 查询任务

SELECT * FROM V$DATAPUMP_JOB; 269 导入数据

impdp userid=system/oracle dumpfile=gary directory=mydir job_name=gary_job

本文来源：https://www.bwwdw.com/article/r0kf.html