Oracle SQL总结 - 1

1. 查用户下面满足某条件的表的名字或条数:
select count(*) from user_tables where table_name like 'TTS%'; --查出以TTS开头的表的个数
 
查出表名中不含有'$'字符的表:
select TABLE_NAME from user_tables where TABLE_NAME not like '%$%';
 
2. 查询用户表信息
select table_name, status from user_tables where length(table_name) < 5;--查看名字长度小于5的表
3. 创建表
create table t2(id number, name varchar(20), birthday date, nowstamp timestamp);
 
快速创建表
create table t2 as select * from emp where rownum < 5;
 
4. 插入表
insert into t2 values(1, 'zhangsan', sysdate, to_timestamp(sysdate));
 
5. 查表
select rowid, id, name, birthday, nowstamp from t2;
 
6. 格式化表: col nowstamp format a20;
 
7. 查出当前用户下的所有表的字段数
select table_name,Count(*) as columns from user_tab_columns Group By table_name;
8. select table_name,Count(*) As columns from user_tab_columns Group By table_name having table_name like 'HT%'; --注意使用having子句
9. 查出某个表的字段数(注意:表大小敏感)
select table_name,Count(*) as columns from user_tab_columns Group By table_name having table_name='PRODUCT_DRAFT';
10. create table t2 select * from t1;--用表t1的表结构和数据来创造t2
create table t2 select * from t1;--用表t1的表结构和数据来创造t2
11. insert into t2(name, id) select name, id from t1 where id=1; --选出表t1上的数据来插入t2表,注意字段必须对应起来。

12. 删除表的字段
alter table tab_name drop COLUMN col_name;
13. 增加表的字段
alter table tab_name add col_name varchar(32);
14. 根据名字查询sequnence
select * from user_sequences where sequence_name='SEQ_PRODUCT_DRAFT'; --普通用户也有权限
15. 在sql developer中导出成insert的sql,选中表之后导出为sql则可
16. *在执行sql脚本时(导入insert语句), 提示’Enter value for nbsp:‘, 即执行导入时发现叫你输入 nbsp;的值,原因是因为 sqlplus 把 &作为一个变量的开头,所以每次执行这条语句时会提醒你。
解决方法:只要把 define 的属性设置为: off 就可以了(set  define  off);这样就可以插入象 &nbsp;&lt;&gt;这样的特殊字符了

17. 查看是否处于归档方式: archive log list;
修改为归档方式:alter system set log_archive_start=true scope=spfile; 然后shutdown immediate; 再startup mount(打开控制文件,不打开数据文件); 再alter database archivelog将数据库切换为归档方式; 最后alter databse open(将数据库打开); 当再次用 archive log list查看时,已经处于归档方式了,就可以做备份工作了(alter tablespace tt_space begin backup, 备份完成, 使用alter tablespace tt_space end backup)。

18. 查看service的名字:show parameter service, 一般来讲它为oracle的global数据库名字。
show parameter service;
show parameter name;
show parameter domain;

19. 查出所有表及其记录数
select TABLE_NAME, count(*) from user_tables group by TABLE_NAME
20. 在子表中查询, 相当于连接操作,都会进行笛卡尔乘积
select * from ht_task_flow_node_product_1 where task_flow_id in(
select id from ht_task_flow_product_1 where name='AndOrUrgentForceend'
)
21. 以管理员身份进入系统,查询有哪些用户连入到当前的oracle数据库:
select sid, serial#, username, status from v$session;

22. 根据21, 查询中要剔除的用户的sid和serial#, 使用alter system kill session 'sid, serial#'; 其中的sid和serial#根据21中查询出来的而定。
alter system kill session 'sid, serial#'; 其中的sid和serial#根据21中查询出来的而定。
23. 数据库语言
select userenv('LANGUAGE') from dual;
select * from V$NLS_PARAMETERS
24. 将查询结果进行保存,使用spool
第一步:spool /home/oracle/moree-sql/result.txt
第二步:select username, default_tablespace from dba_users;
第三步:spool off
25. Oracle中产生随机数
产生从5.5到40之间的随机数:
select DBMS_RANDOM.VALUE(5.5,40) from dual;
26. 连接oracle时,出现ORA-27121: unable to determine size of shared memory segment错误
SQL> conn dba1/dba1                                                                
ERROR:
ORA-01034: ORACLE not available
ORA-27121: unable to determine size of shared memory segment
Linux Error: 13: Permission denied
主要是因为oracle安装程序没有给oracle这个可执行程序设置正确的setuid。这样设置一下:
$ cd $ORACLE_HOME/bin
$ chmod 6751 oracle

用ipcs -a查看占用的orphaned shared memory segments and semaphores,
用ipcs -a 找到root占用的ID,然后用
ipcrm -m <ID> - for shared memory 
ipcrm -s <ID> - for semaphores 

27、查询表的约束
(1) 查询MEMBER表中的所有约束及其类型
select constraint_name, constraint_type, table_name from user_constraints where table_name = 'MEMBER';
结果为:
CONSTRAINT_NAME CO TABLE_NAME
--------------- -- ---------------
MEMBER_PK             P    MEMBER
SYS_C0017557        C    MEMBER
SYS_C0017558        C    MEMBER
SYS_C0017559        C    MEMBER
SYS_C0017560        C    MEMBER
SYS_C0017561        C    MEMBER
SYS_C0017562        C    MEMBER
SYS_C0017563        C    MEMBER

已选择8行。
 
再查相应字段:
select column_name from user_cons_columns where table_name = 'MEMBER' and    CONSTRAINT_NAME = 'MEMBER_PK';
结果为:
select column_name from user_cons_columns where table_name = 'MEMBER' and        CONSTRAINT_NAME = 'MEMBER_PK';
 
select        column_name    
     from        user_constraints        c,user_cons_columns        col    
     where        c.constraint_name=col.constraint_name        and        c.constraint_type='P'        and        c.table_name='表名'
28、查询中包含与或操作
查询表ORD_ORDER_ITEM中的biz_status的状态为5种中的一种,并且product_code的状态码为3种的一种, 最后查询出这样数据的总条数。
select count(*) from ORD_ORDER_ITEM where (biz_status='issue_ready' or biz_status='service' or biz_status='closed' or biz_status='cancel' or biz_status='suspend') and (product_code='pc001' or product_code='pc005' or product_code='pc090');
查询两个表连接之后的总记录数
SELECT COUNT(b.id) FROM subscription AS A,subscription_detail AS B WHERE A.package_id='128479' AND A.id=B.subscription_id;
29、IN和NOT IN, 在做的时候一定要避免产生的笛卡尔乘机的影响,特别是在数据量比较大的情况下,常常会导致OutofMemory的问题。
@>select * from abc;

                ID NAME             ADDR
---------- ---------- ---------------
                 1 zhangsan     shanghai
                 2 lisi             shanghai
                 3 wangwu         chengdu
                 4 zhaoliu        chengdu
                 5 zhangsan     chengdu

@>select * from bcd;

                ID UNIVERSITY
---------- --------------------
                 1 qinghua
                 3 fudan
                 4 tongji

@>select a.id from abc a, bcd b where a.id not in (select id from bcd);

                ID
----------
                 2
                 5
                 2
                 5
                 2
                 5

6 rows selected.
@>select a.id from abc a, bcd b where a.id not in (select id from bcd) group by a.id;

                ID
----------
                 2
                 5
30. 查看Oracle使用的字符集
oracle@b2b_plat_13619:/home/oracle>echo $NLS_LANG
AMERICAN_AMERICA.US7ASCII
31:查看Oracle的版本:
select banner from sys.v$version;
查看安装了哪些选项: select * from sys.v$option;
 
32、用户解锁(unlock)和修改密码
alter user scott identified by tiger account unlock;
33、查询Instance和是否为主库还是备库
SQL> select name, database_role from v$database;

NAME                                 DATABASE_ROLE
-------------------- ------------------------------------------------
OTTER                                PRIMARY
34、where、group by、order by顺序
select * from tb where ... group by ... order by ...
35、SQL执行次数查询
查询出执行次数最多的10条语句
select SQL_TEXT, EXECUTIONS from (select SQL_TEXT, EXECUTIONS from v$sqlarea order by EXECUTIONS desc) where rownum <= 10;
36、删除表空间
删除表空间(不包括对应的数据文件)
drop tablespace users including contents;
删除表空间(包括对应的数据文件)
drop tablespace users including contents and datafiles;
37、数据文件丢失的处理办法:
描述:错误的删掉了一个数据文件,导致数据库在重启的时候出现问题,报错为数据文件无法找到。
ERROR at line 1:
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: '/home/oracle/oradata/moree/users01.dbf'
解决方案:
 step1:startup mount
 step2:alter database datafile '/home/oracle/oradata/moree/users01.dbf'offline drop;
 step3:shutdown immediate
 step4: startup
当在startup的时候,出现数据文件丢失的提示,但是此时仍然可以查看哪些数据文件错误的视图:v$recover_file, 使用select * from v$recover_file;

38、查看当前处于读写密集的文件
select NAME , PHYRDS , PHYWRTS    from v$filestat f, v$datafile d where f.FILE#    = d.FILE#    order by PHYWRTS desc ;
运行结果:
@>select NAME , PHYRDS , PHYWRTS    from v$filestat f, v$datafile d where f.FILE#    = d.FILE#    order by PHYWRTS desc ;

NAME                                                                   PHYRDS        PHYWRTS
--------------------------------------------- ---------- ----------
/home/oracle/oradata/moree/perfstat.dbf                            217             1135
/home/oracle/oradata/moree/undotbs01.dbf                            20                278
/home/oracle/oradata/moree/undotbs02.dbf                            13                200
/home/oracle/oradata/moree/system01.dbf                            608                102
/home/oracle/oradata/moree/undotbs03.dbf                             9                 72
/home/oracle/oradata/moree/tools01.dbf                                 3                    1
/home/oracle/oradata/moree/APPINDX1M01.dbf                         3                    1
/home/oracle/oradata/moree/APP_DATA1K01.dbf                        3                    1
/home/oracle/oradata/moree/APPDATA1M01.dbf                         3                    1
/home/oracle/oradata/moree/MCSHADOWTS01.dbf                        3                    1
/home/oracle/oradata/moree/APPINDX1K02.dbf                         3                    1
/home/oracle/oradata/moree/APP_DATA1K05.dbf                        3                    1
/home/oracle/oradata/moree/APP_DATA1K04.dbf                        3                    1
39、查询在当前用户下有哪些存储过程
SELECT * FROM ALL_SOURCE where TYPE='PROCEDURE' AND TEXT LIKE '%INSERT%'; --查询ALL_SOURCE中,(脚本代码)内容与0997500模糊匹配的类型为PROCEDURE(存储过程)的信息。 根据GROUP BY TYPE 该ALL_SOURCE中只有以下5种类型 1 FUNCTION 2 JAVA SOURCE 3 PACKAGE 4 PACKAGE BODY 5
PROCEDURE
40、查询是否存在全表扫描
@>select name, value from v$sysstat where name like '%table scan%';

NAME                                                                                    VALUE
---------------------------------------- ----------
table scans (short tables)                                            470
table scans (long tables)                                                 0
table scans (rowid ranges)                                                0
table scans (cache partitions)                                        0
table scans (direct read)                                                 0
table scan rows gotten                                             352560
table scan blocks gotten                                            45669

7 rows selected.
通过查询table scans (long tables) 的内容,知道当前是否存在全表扫描的情况。

41、获取系统时间
select sysdate from dual;
select to_char(sysdate, 'DD-MON-yyyy HH24:MI:SS') from dual;
42、有相同的,取一个, 去重
mysql> select distinct type from element;
+---------+
| type        |
+---------+
| PACKAGE |
| PRODUCT |
| FEATURE |
+---------+
3 rows in set (0.00 sec)
43、删除多条记录
mysql> select * from member;
+----+----------+---------------------+
| id | name         | birthday                        |
+----+----------+---------------------+
|    1 | zhangsan | 2010-04-09 10:26:08 |
|    2 | lisi         | 2010-04-09 10:26:08 |
|    3 | zhangsan | 2010-04-09 10:26:08 |
|    4 | wangwu     | 0000-00-00 00:00:00 |
+----+----------+---------------------+
4 rows in set (0.00 sec)

mysql> delete from member where id in (1,2);
Query OK, 2 rows affected (0.17 sec)

mysql> delete from member where id = 3 or id=4;
Query OK, 2 rows affected (0.05 sec)

mysql> select * from member;
Empty set (0.00 sec)
44、字符串拼接
方式1:||
方式2:concat函数
 
45、连接远程的Oracle
语法:sqlplus name/password@ip:port/sid,前提是服务器端需要启动listener,用于监听远端程序的连接。
Oracle启动listener的方式:lsnrctl start
 
sqlplus moree/moree@ip:1521/otter

46、查询备份文件的位置

SQL> show parameter db_recovery_file_dest

NAME                                                                 TYPE                VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                                string            /home/oracle/base/flash_recove
                                                                                                 ry_area
db_recovery_file_dest_size                     big integer 2G
47、增加、修改、删除字段
修改表字段
将表A中的a字段名修改为字段名为c
alter  TABLE A rename column a to c
 
增加字段
为表A增加字段d
alter  TABLE A  add d char(200)

删除字段
在表A中删除字段e
ALTER TABLE A DROP COLUMN e

 
48、同时更新多个字段的内容
中间使用','分割开
UPDATE UserList SET UserName = 'Admin', UserPassword = 'pwd' WHERE UserID = 3

49、添加、删除主外键

1、创建表的同时创建主键约束
 (1)无命名 create table student ( studentid int primary key not null, studentname varchar(8), age int);
 
(2)有命名 create table students ( studentid int , studentname varchar(8), age int, constraint yy primary key(studentid));
  
2、删除表中已有的主键约束
 (1)无命名可用 SELECT * from user_cons_columns; 查找表中主键名称得student表中的主键名为SYS_C002715 alter table student drop constraint SYS_C002715;
 
(2)有命名 alter table students drop constraint yy;
 
3、向表中添加主键约束 alter table student add constraint pk_student primary key(studentid);
  
4、向表中添加外键约束 ALTER TABLE table_A ADD CONSTRAINT FK_name FOREIGN KEY(id) REFERENCES table_B(id);
SQL> alter table t1 add constraint t1_fk foreign key(deptno) references t2(id) on delete cascade;

Table altered.

50、创建sequence序列

create sequence studentPKSequence start with 1 increment by 1;

打开命令行窗口,输入sqlplus /nolog,进入sqlplus命令行

SQL>conn sys/password as sysdba;

SQL>drop user "username" cascade; --删除用户

SQL>alter database datafile 'datafile路径' resize __M; --缩放空间表大小

如:alter database datafile 'd:\oracle\..\USERS01.DBF' resize 500M;     将users01.dbf缩放至500M大小

 

如果在删除用户时提示:无法删除当前已连接的用户

则表明当前用户在数据库session中有连接,可以查询出来并kill掉这些连接

 

SQL>select username, sid, serial# from v$session where username="用户名";

结果:

username                              sid                serial#

用户名                                     151                  51

SQL>alter system kill session '151, 51';

这样,便可以删除此用户了。