1、ORACLE实例启动、停止

SQL>shutdown immediate;  oracle停止

SQL>startup;  oracle启动

2、ORACLE 监听启停

lsnrctl start 监听启动

lsnrct stop 监听停止

lsnrctl status 监听状态

3、查看ORCLE实例和数据库状态

SQL>select status from v$instance;

SQL>select open_mode from v$database;

4、查看ORACLE版本

SQL>select * from v$version;

5、查看ORACLE相关参数

SQL>show parameter processes；

SQL> show parameter sga;

SQL> select name,value from v$parameter where name=‘processes‘;

6、修改ORACLE参数

SQL> alter system set processes=1500 scope=spfile; #重启ORACLE使修改参数生效

7、查看数据文件

SQL>select bytes/1024/1024/1024 as "Size(G)",name from v$datafile order by bytes;

8、查看临时文件

SQL> select bytes/1024/1024/1024 as "Size(G)",name from v$tempfile order by bytes;

9、创建、删除、解锁ORACLE用户和修改ORACLE用户密码

SQL> create user username identified by password default tablespace tablespace; #创建oracle用户，

SQL> drop user username cascade; #删除oracle用户

SQL> alter user username account unlock; #解锁oracle用户

SQL>alter user username identified by new_password; #修改oracle用户密码

10、查看ORACLE执行计划、执行时间

SQL>set autotrace on; #打开跟踪SQL执行计划

SQL>select username from dba_users; #跟踪的SQL

SQL>set autotrace off;

SQL> set timing on; #打开执行时间统计

11、查看alter日志位置

SQL>show parameter background_dump_dest;

SQL>select name,value from v$parameter v where v.name like ‘%background%‘;

12、创建数据表空间、临时表空间、回滚表空间

SQL>create tablespace tablespacename datafile ‘/home/oracle/tablespace.dbf‘ size 1024M; #创建数据表空间

SQL>create temp tablespae temp  tempfile ‘/home/oracle/temp.dbf‘ size 1024M; #创建临时表空间

SQL> create undo tablespace undotbs datafile ‘/home/oracle/undotbs.dbf‘ size 1024M; #创建回滚表空间

13、扩展数据表空间、临时表空间

SQL>alter tablespace tablespacename add datafile ‘/home/oracle/add_tablespacename.dbf‘ size 1024M; #增加数据文件方式，不允许自动增长

SQL> alter tablespace tablespacename add datafile ‘/home/oracle/add_tablespacename.dbf‘ size 1024M autoextend on next 5M maxsize 2048M; #增加数据文件方式且允许自动增长

SQL> alter database datafile ‘/home/oracle/tablespace.dbf‘ autoextend on next 5M maxsize 2048M; #允许已存在的数据文件自动扩展

SQL> alter database datafile ‘/home/oracle/tablespace.dbf‘ resize 2048M; # 改变当前数据文件大小

#调整临时表空间文件

SQL> alter tablespace temp add tempfile ‘/home/oracle/temp2.dbf‘ size 2048M; #扩展临时表空间大小，默认autoextend off

SQL> alter tablespace temp add tempfile ‘/home/oracle/temp2.dbf‘ size 1024M antoextend on next 128M maxsize 2048M; #扩展临时表空间大小

SQL> alter database tempfile ‘/home/oracle/temp.dbf‘  resize 2048M;

14、查看表空间文件大小

SQL> select sum(d.bytes)/1024/1024/1024 ||‘G‘ as total_bytes,d.tablespace_name from dba_datafiles d where d.tablespace_name = ‘UNDOTBS‘ group by d.tablespace_name;

15、ORACLE用户进程杀死，常用于drop数据库用户失败时查询进程

SQL> set line 120

SQL>col username for a30;

SQL>select sid,serial#,username,type from v$session;

or

SQL>select sid,serial# from v$session where username=‘USERNAME‘;

16、删除表空间

SQL> drop tablespace tablespacename including contents and datafiles;

17、查看ORACLE实例默认临时表空间

SQL>select property_name,property_value from database_properties where property_name=‘DEFAULT_TEMP_TABLESPACE‘;

SQL>alter database default temporary tablespace temp2;#修改oracle实例默认临时表空间，不能直接删除，需先创建新的临时表空间、修改后再删除

ORACLE常用操作命令