Oracle常用命令和sql

数据库启动相关：
系统命令行：
启动监听：lsnrctl start
查看监听状态:lsnrctl status
停止监听：lsnrctl stop
进入sql命令行：sqlplus / as sysdba
进入远程sql命令行：sqlplus ecm1/ecm1@//127.0.0.7:1521/orcl
Sql命令行启动： startup
Sql命令行关闭： shutdown immediate
表空间建立：

create tablespace QPCS logging datafile '/home/app/oracle/oradata/orcl/resoftdata2.dbf' size 1024m autoextend on next 1024m maxsize 10240m extent management local;

空表分配segment：

select 'alter table '||table_name||' allocate extent;' from user_tables where num_rows=0  or num_rows is null;

设置编码：
export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
查看编码：
select * from v$nls_parameters where parameter='NLS_CHARACTERSET';
删除用户：
drop user ams cascade;

创建临时表空间
create temporary tablespace qpcs_temp tempfile '/u01/oradata/orcl/qpcs_temp02.dbf' size 32m autoextend on next 32m maxsize unlimited extent management local;
创建数据表空间
create tablespace QPCS logging datafile '/u01/oradata/orcl/qpcs_data02.dbf' size 32m autoextend on next 32m maxsize unlimited extent management local;
创建用户并指定表空间
create user qpcs identified by qpcs default tablespace qpcs temporary tablespace qpcs_temp;

创建用户：
create user username identified by password;

修改用户密码：
alter user user01 identified by user10;
分配权限：
grant dba to qpcs;
DBA: 拥有全部特权，是系统最高权限，只有DBA才可以创建数据库结构。
RESOURCE:拥有Resource权限的用户只可以创建实体，不可以创建数据库结构。
CONNECT:拥有Connect权限的用户只可以登录Oracle，不可以创建实体，不可以创建数据库结构。
IMPDP用户导入：分区表
impdp resoft/resoft244@orcl DIRECTORY=dump DUMPFILE=partition_p4.dmp TABLES=PDBA:P3 logfile=imp_p1.log table_exists_action=append
EXPDP用户导出：分区表
expdp resoft/resoft244@orcl DIRECTORY=dump DUMPFILE=partition_p4.dmp TABLES=PDBA:P4 logfile=exp_p4.log
imp用户导入：
imp username/password@orcl file=temp.dmp fromuser=username touser=username

exp用户导出：
exp ams/ams@orcl owner=ams file=account1122.dmp

表导出：

exp basscan/basscan@orcl buffer=64000 file=/oracle/yufei/BRANCHSEQUENCE.dmp tables=BRANCHSEQUENCE,BRANCHSEQUENCE_PRE query=\"where id=\'asdasd\'\"  --带条件导出

DBLink远程数据库：

connect to scott identified by tigerusing '(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.5)(PORT = 1521)))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = orcl)))';

使用DBLink查询sql：
select * from tablename@dblinkname;
删除DBLink：
drop database link dblName;
查看表空间使用情况：

FROM dba_free_space GROUP BY tablespace_name; SELECT a.tablespace_name, a.bytes total, b.bytes used, c.bytes free, (b.bytes * 100) / a.bytes "% USED ", (c.bytes * 100) / a.bytes "% FREE " FROM sys.sm$ts_avail a, sys.sm$ts_used b, sys.sm$ts_free c WHERE a.tablespace_name = b.tablespace_name AND a.tablespace_name = c.tablespace_name; 

查看临时表空间：
select tablespace_name,file_name,bytes/1024/1024 "file_size(M)",autoextensible from dba_temp_files;

修改临时表空间：
alter database tempfile '/oracle/oradata/orcl/TS_RESOFT_TEMP.dbf' resize 20480m [autoextend on next 10m maxsize 2046m]
导入txt文件创建ctl控制文件：
sqlldr senwei/senwei control=$HOME/oracle/info.ctl
查看被锁的表：

select p.spid,       a.serial#,       c.object_name,       b.session_id,       b.oracle_username,       b.os_user_name  from v$process p, v$session a, v$locked_object b, all_objects c where p.addr = a.paddr   and a.process = b.process   and c.object_id = b.object_id;

查找用户下所有连接，生成杀掉语句：
Select 'alter system kill session '''||sid||','||serial#||''';' from v$session where username=upper('fms');
查看所有session：


select b.owner,b.object_name,l.session_id,l.locked_mode
from v$locked_object l, dba_objects b
where b.object_id=l.object_id;
select t2.username,t2.sid,t2.serial#,t2.logon_time
from v$locked_object t1,v$session t2
where t1.session_id=t2.sid order by t2.logon_time;


查看用户密码默认有效天数：
SELECT * FROM dba_profiles WHERE profile='DEFAULT' AND resource_name='PASSWORD_LIFE_TIME';
修改用户密码有效天数无限制：
ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED
删除分区表：
alter table pdba move partition P4 tablespace QPCS ;

select value from v$parameter where name ='processes';--数据库允许的最大连接数
select count(*) from v$process where program='Oracle.EXE(SHAD)';--当前的数据库连接数
alter system set processes = 300 scope = spfile;--修改最大连接数:

查询数据库当前进程的连接数：
select count(*) from v$process;
查看数据库当前会话的连接数：
select count(*) from v$session;
查看数据库的并发连接数：
select count(*) from v$session where status='ACTIVE';
查看当前数据库建立的会话情况：
select sid,serial#,username,program,machine,status from v$session;
查询数据库允许的最大连接数：
select value from v$parameter where name = 'processes';
或者：show parameter processes;
修改数据库允许的最大连接数：
alter system set processes = 300 scope = spfile;