oracle常用查询语句

  • 内容
  • 相关

--重启数据库服务:
cmd:sqlplus sys as sysdba
SQL>shutdown immediate; 
SQL>startup;

--重启监听:
lsnrctl stop
lsnrctl start

--查看状态:
lsnrctl status

--查询数据库账号登录密码是否过期
select b.username,b.profile,b.account_status,b.created,a.ptime,b.lock_date,b.expiry_date from
(select * from sys.user$) a,
(select * from dba_users) b
where a.name=b.username for update;

--去掉口令期限
ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

--创建临时表空间
create temporary tablespace imagetemp
tempfile 'E:/oracle/product/10.2.0/oradata/testserver/imagetemp.dbf'
size 8m
autoextend on
next 8m maxsize 4096m
extent management local;

--创建数据表空间
create tablespace imagedata
logging
datafile 'E:/oracle/product/10.2.0/oradata/testserver/imagedata.dbf'
size 8m
autoextend on
next 8m maxsize 4096m
extent management local;

--创建用户并指定表空间
create user image identified by sa
default tablespace imagedata
temporary tablespace imagetemp;

--给用户授予权限
grant connect,resource,dba to image;

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

--收回用户的权限
revoke unlimited tablespace from image;

--修改空间限额权限
ALTER USER image QUOTA 0 ON users;
alter user image quota 0 on system;

--设置默认表空间
alter user image default tablespace imagedata;

--设置在image用户在 imagedata表空间配额不受限
alter user image quota unlimited on imagedata;

--数据库导出
EXPDP USERID='image/sa@orcl as sysdba' schemas=image directory=DATA_PUMP_DIR dumpfile=image.dmp logfile=image.log version=10.2.0.3.0

--数据库导入
IMPDP USERID='imagenew/sa@orcl as sysdba' schemas=imageold directory=DATA_PUMP_DIR dumpfile=image.dmp logfile=image.log REMAP_SCHEMA=imageold:imagenew remap_tablespace=tbspaceold:tbspacenew version=10.2.0.3.0

imp image/sa@FSSC fromuser=imagezjky touser=image file=D:/SIIT-IMAGE/imagejtl.dmp tablespaces=imagedata ignore=y grants=n log=D:\SIIT-IMAGE\log.txt

select * from dba_tables where tablespace_name='IMAGEDATA'


--使用exp导出11g及以上版本带有空表的处理及导出(按1、2、3、4顺序)
--1.查询当前用户下的所有空表(一个用户最好对应一个默认表空间),命令如下:
select table_name from user_tables where NUM_ROWS=0 or num_rows is null;
--2.根据上述查询,可以构建针对空表分配空间的命令语句,如下:
select 'alter table '||table_name||' allocate extent;' from user_tables where num_rows=0 or num_rows is null;
--3.将第2条查询结果生成的所有sql代码全部执行
alter table SCAN_UPLOAD_BARCODE allocate extent;
alter table SCAN_UPLOAD_IMAGE allocate extent;
alter table SCAN_UPLOAD_INVOICE allocate extent;
alter table T_ADDBARCODE allocate extent;
alter table T_ADDREASON_TYPE allocate extent;
......
--4.执行导出语句
exp image/sa@orcl file=C:\image.dmp log=C:\image.log


--锁表语句查询
SELECT s.username,  
decode(l.type,'TM','TABLE LOCK', 'TX','ROW LOCK', NULL) LOCK_LEVEL,  
o.owner,o.object_name,o.object_type,  
s.sid,s.serial#,s.terminal,s.machine,s.program,s.osuser  
FROM v$session s,v$lock l,all_objects o  
WHERE l.sid = s.sid  
AND l.id1 = o.object_id(+)  
AND s.username is NOT Null 


--查看ORACLE最大游标数
show parameter open_cursors;

--查看当前打开的游标数目
select count(*) from v$open_cursor;

--查询Oracle游标使用情况的方法
select * from v$open_cursor where user_name = 'IMAGE';

--修改ORACLE最大游标数
alter system set open_cursors=1000 scope=both;

--Oracle查看和修改连接数(进程/会话/并发等等)

--1.查询数据库当前进程的连接数:
select count(*) from v$process;

--2.查看数据库当前会话的连接数:
select count(*) from v$session;

--3.查看数据库的并发连接数: 
select count(*) from v$session where status='ACTIVE';

--4.查看当前数据库建立的会话情况: 
select sid,serial#,username,program,machine,status from v$session;

--5.查询数据库允许的最大连接数: 
select value from v$parameter where name = 'processes';
或者命令:
show parameter processes; 
--查询数据库允许的最大session数:
show parameter session;

--6.查询所有数据库的连接数
select schemaname,count(*) from v$session group by schemaname;

--7.查询终端用户使用数据库的连接情况。
select osuser,schemaname,count(*) from v$session group by schemaname,osuser;

--8.查看当前不为空的连接
select * from v$session where username is not null

--9.查看不同用户的连接数
select username,count(username) from v$session where username is not null group by username;

--10.修改数据库允许的最大连接数和会话连接数:
alter system set processes = 1000 scope = spfile;
alter system set sessions = 1100 scope = spfile;
--ORACLE的连接数(sessions)与其参数文件中的进程数(process)有关,它们的关系:sessions=(1.1*process+5)
(需要重启数据库才能实现连接数的修改) 
重启数据库:
SQL>shutdown immediate; 
SQL>startup;

查看当前有哪些用户正在使用数据:
SQL>select osuser,a.username,cpu_time/executions/1000000||'s',sql_fulltext,machine
SQL>from v$session a,v$sqlarea b 
SQL>where a.sql_address = b.address 
SQL>order by cpu_time/executions desc;

备注:UNIX 1个用户session对应一个操作系统process,而Windows体现在线程。
启动oracle
su - oracle 
SQL>sqlplus system/pwd as sysdba    --进入sql 
SQL>startup   --启动数据库 
SQL>lsnrctl start   --启动监听 
sqlplus "/as sysdba" 
SQL>shutdown immediate;     --关闭数据库 
SQL>startup mount; 
SQL>alter database open; 

--11.oracle中查询被锁的表并释放session
SELECT A.OWNER,A.OBJECT_NAME,B.XIDUSN,B.XIDSLOT,B.XIDSQN,B.SESSION_ID,B.ORACLE_USERNAME, B.OS_USER_NAME,B.PROCESS, B.LOCKED_MODE, C.MACHINE,C.STATUS,C.SERVER,C.SID,C.SERIAL#,C.PROGRAM
FROM ALL_OBJECTS A,V$LOCKED_OBJECT B,SYS.GV_$SESSION C
WHERE ( A.OBJECT_ID = B.OBJECT_ID ) AND (B.PROCESS = C.PROCESS ) ORDER BY 1,2
--释放session Sql:
alter system kill session 'sid, serial#'
alter system kill session '379, 21132'
alter system kill session '374, 6938'

--12.查看占用系统io较大的session
SELECT se.sid,se.serial#,pr.SPID,se.username,se.status,se.terminal,se.program,se.MODULE,se.sql_address,st.event,st.p1text,si.physical_reads,si.block_changes
FROM v$session se, v$session_wait st,v$sess_io si,v$process pr
WHERE st.sid=se.sid AND st.sid=si.sid AND se.PADDR=pr.ADDR AND se.sid>6 AND st.wait_time=0 AND st.event NOT LIKE '%SQL%' ORDER BY physical_reads DESC

--13.找出耗cpu较多的session
select a.sid,spid,status,substr(a.program,1,40) prog,a.terminal,osuser,value/60/100 value
from v$session a,v$process b,v$sesstat c
where c.statistic#=12 and c.sid=a.sid and a.paddr=b.addr order by value desc

--14.查询session被锁的sql可以用一下语句
select sys.v_$session.osuser,sys.v_$session.machine,v$lock.sid,
sys.v_$session.serial#,
decode(v$lock.type,
'MR', 'Media Recovery',
'RT','Redo Thread',
'UN','User Name',
'TX', 'Transaction',
'TM', 'DML',
'UL', 'PL/SQL User Lock',
'DX', 'Distributed Xaction',
'CF', 'Control File',
'IS', 'Instance State',
'FS', 'File Set',
'IR', 'Instance Recovery',
'ST', 'Disk Space Transaction',
'TS', 'Temp Segment',
'IV', 'Library Cache Invalida-tion',
'LS', 'Log Start or Switch',
'RW', 'Row Wait',
'SQ', 'Sequence Number',
'TE', 'Extend Table',
'TT', 'Temp Table',
'Unknown') LockType,
rtrim(object_type) || ' ' || rtrim(owner) || '.' || object_name object_name,
decode(lmode, 0, 'None',
1, 'Null',
2, 'Row-S',
3, 'Row-X',
4, 'Share',
5, 'S/Row-X',
6, 'Exclusive', 'Unknown') LockMode,
decode(request, 0, 'None',
1, 'Null',
2, 'Row-S',
3, 'Row-X',
4, 'Share',
5, 'S/Row-X',
6, 'Exclusive', 'Unknown') RequestMode,
ctime, block b
from v$lock, all_objects, sys.v_$session
where v$Lock.sid > 6
and sys.v_$session.sid = v$lock.sid
and v$lock.id1 = all_objects.object_id; 

--启动oracle
su - oracle
sqlplus system/pwd as sysdba      --进入sql
startup                           --启动数据库
lsnrctl start                     --启动监听
sqlplus "/as sysdba"
shutdown immediate;
startup mount;
alter database open;

--SQLPlus 在连接时通常有四种方式
1. sqlplus / as sysdba
   --操作系统认证,不需要数据库服务器启动listener,也不需要数据库服务器处于可用状态。比如我们想要启动数据库就可以用这种方式进入sqlplus,然后通过startup命令来启动。
2. sqlplus username/password
   --连接本机数据库,不需要数据库服务器的listener进程,但是由于需要用户名密码的认证,因此需要数据库服务器处于可用状态才行。
3. sqlplus usernaem/password@orcl
   --通过网络连接,这是需要数据库服务器的listener处于监听状态。此时建立一个连接的大致步骤如下 
a. 查询sqlnet.ora,看看名称的解析方式,默认是TNSNAME 
b. 查询tnsnames.ora文件,从里边找orcl的记录,并且找到数据库服务器的主机名或者IP,端口和service_name 
c. 如果服务器listener进程没有问题的话,建立与listener进程的连接。 
d. 根据不同的服务器模式如专用服务器模式或者共享服务器模式,listener采取接下去的动作。默认是专用服务器模式,没有问题的话客户端就连接上了数据库的server process。
e. 这时连接已经建立,可以操作数据库了。
4.sqlplus username/password@//host:port/sid
--用sqlplus远程连接oracle命令(例:sqlplus risenet/1@//192.168.130.99:1521/risenet)


(1)表方式,将指定表的数据导出/导入。
导出:
导出一张或几张表:

代码 
$ exp user/pwd file=/dir/xxx.dmp log=xxx.log tables=table1,table2 

$ exp user/pwd file=/dir/xxx.dmp log=xxx.log tables=table1,table2

如果是分区表

代码 
$ exp user/pwd file=/dir/xxx.dmp log=xxx.log tables=table1:tablespaces1,table2:tablespaces2 

$ exp user/pwd file=/dir/xxx.dmp log=xxx.log tables=table1:tablespaces1,table2:tablespaces2

导出某张表的部分数据

代码 
$ exp user/pwd file=/dir/xxx.dmp log=xxx.log tables=table1 query=\”where col1=\’…\’   
and col2 \<…\” 

$ exp user/pwd file=/dir/xxx.dmp log=xxx.log tables=table1 query=\”where col1=\’…\’
and col2 \<…\”
导入:
导入一张或几张表

代码 
$  imp  user/pwd  file=/dir/xxx.dmp  log=xxx.log  tables=table1,table2  fromuser=dbuser touser=dbuser2 commit=y ignore=y 

$  imp  user/pwd  file=/dir/xxx.dmp  log=xxx.log  tables=table1,table2  fromuser=dbuser touser=dbuser2 commit=y ignore=y
如果是分区表

代码 
$  imp  user/pwd  file=/dir/xxx.dmp  log=xxx.log  tables=table1:tablespaces1,table2:tablespaces2  fromuser=dbuser touser=dbuser2 commit=y ignore=y 

$  imp  user/pwd  file=/dir/xxx.dmp  log=xxx.log  tables=table1:tablespaces1,table2:tablespaces2  fromuser=dbuser touser=dbuser2 commit=y ignore=y

(2)用户方式,将指定用户的所有对象及数据导出/导入。
导出:

代码 
$ exp user/pwd file=/dir/xxx.dmp log=xxx.log owner=(xx, yy) 

$ exp user/pwd file=/dir/xxx.dmp log=xxx.log owner=(xx, yy)
只导出数据对象,不导出数据  (rows=n )

$ exp user/pwd file=/dir/xxx.dmp log=xxx.log owner=user rows=n
导入:

代码 
imp  user/pwd  file=/dir/xxx.dmp  log=xxx.log  fromuser=dbuser  touser=dbuser2   
mmit=y ignore=y 

imp  user/pwd  file=/dir/xxx.dmp  log=xxx.log  fromuser=dbuser  touser=dbuser2
commit=y ignore=y
(3)全库方式,将数据库中的所有对象导出/导入导出:

代码 
$ exp user/pwd file=/dir/xxx.dmp log=xxx.log full=ycommit=y ignore=y 

$ exp user/pwd file=/dir/xxx.dmp log=xxx.log full=ycommit=y ignore=y
导入:

代码 
$ imp user/pwd file=/dir/xxx.dmp log=xxx.log fromuser=dbuser touser=dbuser2

1导出用户system密码1234 服务器zyd 的 table1 table2表

Exp system/1234@zyd file=E:\file\wenjia.dmp tables=table1,table2


2用户模式 导出用户system的所有对象 
Exp system/1234@zyd (buffer=64000) file=E:\file\dkkd.dmp owner=system

3完全模式

Exp system/1234@zyd (butter=64000) file=E:\file\dkk.dmp full=y 


Imp 完全模式
Imp system/1234@zyd (butter=6400) file=E:\file\dkk.dmp full=y

用户模式
Imp system/12342zyd (butter) file=E:\file\dkkk.dmp fromuser=gadate001
Touser=system 

表模式 
Imp system/1244@zyd file=E:\3123.dmp 


--ORA-01502: 索引或这类索引的分区处于不可用状态
--原因: 出现这个问题,可能有人move过表,或者disable 过索引。
1. alter table xxxxxx move tablespace xxxxxxx 命令后,索引就会失效。
2. alter index index_name  unusable,命令使索引失效。

--解决办法:
--1. 重建索引才是解决这类问题的完全的方法。
     alter index index_name rebuild (online);

--     或者alter index index_name rebuild;
--2. 如果是分区索引只需要重建那个失效的分区 。
     alter index index_name rebuild partition partition_name (online);

--     或者alter index index_name rebuild partition partition_name ;

--3. 或者改变当前索引的名字。

--说明:
--1. alter session set skip_unusable_indexes=true;就可以在session级别跳过无效索引作查询。
--2. 分区索引应适用user_ind_partitions。
--3. 状态分4种:
--    N/A说明这个是分区索引需要查user_ind_partitions或者user_ind_subpartitions来确定每个分区是否可用;
--    VAILD说明这个索引可用;
--    UNUSABLE说明这个索引不可用;
--    USABLE 说明这个索引的分区是可用的。

--4. 查询当前索引的状态:
select distinct status from user_indexes;

--5. 查询那个索引无效:
select index_name from  user_indexes where status <> 'VALID';

--6. 批量rebuild下:

select 'alter index '||index_name||' rebuild online;' from  user_indexes where status <> 'VALID' and index_name not like'%$$';

--给orcale用户赋予该有的权限

GRANT CREATE USER,DROP USER,ALTER USER ,CREATE ANY VIEW , 
   DROP ANY VIEW,EXP_FULL_DATABASE,IMP_FULL_DATABASE, 
      DBA,CONNECT,RESOURCE,CREATE SESSION TO 用户名字


---创建sequence 

 create sequence 序列名字
minvalue 1
maxvalue 999999999999999999999999999
start with 61
increment by 1
cache 20;


--查询出当前oralce的版本信息  

select * from v$version;

原文地址:http://www.wangdabo.com/post-100.html


本文标签:

版权声明:若无特殊注明,本文皆为《秋白》原创,转载请保留文章出处。

本文链接:oracle常用查询语句 - https://www.jqlab.cn/post-822.html

发表评论

电子邮件地址不会被公开。 必填项已用*标注