Oracle Common Operations Commands

1. Oracle instance starts, stops

Sql>shutdown immediate; Oracle Stop

sql>startup; Oracle Startup

2, ORACLE monitoring start and stop

Lsnrctl Start monitoring Boot

LSNRCT Stop Monitoring stops

LSNRCTL Status Monitor state

3. View Orcle instance and database status

Sql>select status from V$instance;

Sql>select Open_mode from V$database;

4. View Oracle version

Sql>select * from V$version;

5. View Oracle Related Parameters

Sql>show parameter processes;

Sql> Show parameter SGA;

Sql> Select Name,value from v$parameter where name= ' processes ';

6. Modifying Oracle Parameters

Sql> alter system set PROCESSES=1500 Scope=spfile; #重启ORACLE使修改参数生效

7. View Data Files

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

8. View Temporary Files

Sql> Select bytes/1024/1024/1024 as "Size (G)", name from V$tempfile order by bytes;

9. Create, delete, unlock Oracle users and modify Oracle user passwords

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. View Oracle execution plan, execution time

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

Sql>select username from Dba_users; #跟踪的SQL

Sql>set autotrace off;

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

11. View the Alter log location

Sql>show parameter background_dump_dest;

Sql>select Name,value from V$parameter v where v.name like '%background% ';

12. Create a data table space, temporary table space, rollback table space

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. Extended Data table space, temporary table space

Sql>alter tablespace tablespacename Add datafile '/home/oracle/add_tablespacename.dbf ' size 1024M; #增加数据文件方式, automatic growth is not allowed

Sql> alter tablespace tablespacename add datafile '/home/oracle/add_tablespacename.dbf ' size 1024M autoextend on next 5 M 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; # Change the current data file size

#调整临时表空间文件

sql> Alter tablespace temp add tempfile '/home/oracle/temp2.dbf ' size 2048M; #扩展临时表空间大小, default autoextend off

sql> Alter tablespace temp add tempfile '/home/oracle/temp2.dbf ' size 1024M antoextend on next 128M maxsize 20 48M; #扩展临时表空间大小

sql> ALTER DATABASE tempfile '/HOME/ORACLE/TEMP.DBF ' resize 2048M;

14. View Tablespace File Size

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 user process kills, commonly used in the query process when a drop database user fails

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. Delete Table Space

Sql> drop tablespace tablespacename including contents and datafiles;

17. View the Oracle instance default temporary tablespace

Sql>select Property_name,property_value from database_properties where property_name= ' default_temp_tablespace ';

Sql>alter database default temporary tablespace temp2; #修改oracle实例默认临时表空间, cannot be deleted directly, you need to create a new temporary tablespace, modify and then delete

Oracle Common Operations Commands