oracle常用命令

?1。启动停止监听 lsnrctl start/stop
2. 进入sqlplus 用户命令：orcl 密码 orcl as sysdba
3. 启动实例 startup mount 实例名
4. 查询实例状态 select instance_name, status from v$instance
5. 获得数据库的名字、创建日期等；
select name,created,log_mode,open_mode from v$database;

6. 获得计算机的主机名，Oracle数据库的实例名以及数据库管理系统的版本；
select host_name,instance_name,version from v$instance;

7. 获取Oracle数据库系统的用户和创建日期；
select username,created from dba_users;
9. Oracle查询所有用户：
select * from all_users;
select * from dba_users
10。 查询用下的表
select a.TABLE_NAME from user_tables a

11。关闭命令
shutdown normal; shutdown immediate;
12。 netca 配置监听 daca 配置数据库

13。查询数据库名称 select name from v$database;

14。查询服务器名 show parameter service_names;

15. 查询表属于哪个用户
select owner from dba_tables where table_name='HSS_COMPONENT';
select * from DEV_BIPLATFORM.HSS_COMPONENT

16。查看模式
select name,log_mode from v$database;
select table_name，logging from dba_tables where table_name='tablename'；
alter table redo_test nologging;
alter database noarchivelog;

17 删除分区表中的数据
alter table table_name truncate partition p5;

重做日志文件
select * from v$logfile;
select * from v$log;
alter database add logfile group 7 '/log/redo07.log' size 200m;
alter system switch logfile;
alter database drop logfile group 3;
alter system checkpoint;

修改log_buffer
show parameter log_buffer
ALTER SYSTEM SET log_buffer=5242880 scope=spfile;

--查询等待事件
select * from v$session_wait;


怎样查看oracle当前的连接数呢？只需要用下面的SQL语句查询一下就可以了。
select * from v$session where username is not null

select username,count(username) from v$session where username is not null group by username #查看不同用户的连接数
select count(*) from v$session #连接数
Select count(*) from v$session where status='ACTIVE'　#并发连接数
show parameter processes #最大连接
alter system set processes = 300 scope = spfile;
alter system set processes = value scope = spfile; 重启数据库 #修改连接

查询用户下的表
select * from dba_users where username = 'SCOTT';

查询表所属空间
select TABLESPACE_NAME from all_tables where table_name='xs_jbxx';
select username,default_tablespace,temporary_tablespace from dba_users where username = 'SCOTT';

//配置em
emca -config dbcontrol db
//修改密码
alter?user?SYSMAN?identified?by?orcl


//修改表空间大小
alter database datafile '/ora/oradata/radius/undo.dbf' resize 10240m；

//创建表空间
create temporary tablespace zzxx_temp
tempfile '/oracle/product/10.2.0.1/db_1/oradata/orcl/zzxx_temp.dbf'
size 500m
auto

extend on
next 32m maxsize 2048m
extent management local;

--修改为nologgin
ALTER TABLESPACE xxxxx NOLOGGING;.

--查看表空间大小
select tablespace_name,
count(*) as extends,
round(sum(bytes) / 1024 / 1024, 2) as MB,
sum(blocks) as blocks
from dba_free_space
group by tablespace_name;

修改数据库编码步骤：

--查看数据库编码
SELECT PARAMETER, VALUE FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER IN ('NLS_CHARACTERSET', 'NLS_NCHAR_CHARACTERSET');

以系统用户登录sqlplus
SQL> SHUTDOWN IMMEDIATE;
SQL> startup mount;
SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION;
SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
SQL> ALTER SYSTEM SET AQ_TM_PROCESSES=0;
SQL> ALTER DATABASE OPEN;
SQL> ALTER DATABASE CHARACTER SET AL32UTF8;

索引重建
alter index IDX_XS_JBXX_XM rebuild tablespace TB_INDEX;

emctl start dbconsole启动EM console服务，使用前需要先设置ORACLE_SID环境变量
emctl stop dbconsole停止EM console服务，使用前需要先设置ORACLE_SID环境变量
emctl status dbconsole

oracle 10g解压缩
zcat 10201_database_linux_x86_64.cpio.gz | cpio -idmv