oracle pk_serial,Oracle SQL总结 - 1

最新推荐文章于 2021-04-02 21:03:10 发布

Joey Jiao

最新推荐文章于 2021-04-02 21:03:10 发布

阅读量450

点赞数

文章标签： oracle pk_serial

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);

快速创建表

createtablet2asselect*fromempwhererownum < 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);这样就可以插入象 <>这样的特殊字符了

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: unabletodeterminesizeofshared 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 - for shared memory

ipcrm -s - 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

@>selecta.idfromabc a, bcd bwherea.idnotin(selectidfrombcd);

----------

6 rows selected.

@>selecta.idfromabc a, bcd bwherea.idnotin(selectidfrombcd)groupbya.id;

----------

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 5PROCEDURE

40、查询是否存在全表扫描

@>select name, value from v$sysstat where name like '%table scan%';

NAME VALUE

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

table scans (short tables) 470

tablescans (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 intprimary 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 studentadd constraint pk_student primary key(studentid);

4、向表中添加外键约束 ALTER TABLE table_AADD CONSTRAINT FK_name FOREIGN KEY(id) REFERENCES table_B(id);

SQL>altertablet1addconstraintt1_fkforeignkey(deptno)referencest2(id)ondeletecascade;Tablealtered.

50、创建sequence序列

createsequence studentPKSequence startwith1 incrementby1;

打开命令行窗口，输入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';

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

Joey Jiao

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
oracle pk_serial,Oracle SQL总结 - 1

Oracle SQL总结 - 11. 查用户下面满足某条件的表的名字或条数：select count(*) from user_tables where table_name like 'TTS%'; --查出以TTS开头的表的个数查出表名中不含有'$'字符的表：select TABLE_NAME from user_tables where TABLE_NAME not like '%$%';2...
复制链接

扫一扫