Oracle-平时常用的SQL
时间：2021-07-01 10:21:17
帮助过：5人阅读
查看Oracle数据库版本
SELECT * FROM v$version;
--查询数据库所有模式用户
SELECT username, profile, default_tablespace, temporary_tablespace
FROM dba_users;
--检查表空间的自动扩展开关
SELECT tablespace_name,SUBSTR (
file_name,
1,
50), AUTOEXTENSIBLE
FROM dba_data_files;
--在指定的用户下运行脚本
ALTER SESSION
SET CURRENT_SCHEMA
= TSSH;
--检查在当前数据库模式下是否存在指定的表
SELECT table_name
FROM user_tables
WHERE table_name
= ‘表名‘;
--检查在当前表中是否存在指定的列
SELECT * FROM user_tab_cols
WHERE table_name
= ‘TABLE_NAME‘ AND column_name
= ‘COLUMN_NAME‘;
--显示对象的DDL状态信息，TABLE可替换对象名称VIEWS等
SELECT DBMS_METADATA.get_ddl (
‘TABLE‘,
‘表名‘,
‘模式用户‘)
FROM DUAL;
--在表空间中添加数据文件
ALTER TABLESPACE data01
ADD ‘/work/oradata/STARTST/data01.dbf‘ SIZE 1000M AUTOEXTEND
OFF;
--给指定的表空间增加大小
ALTER DATABASE ‘/u01/app/Test_data_01.dbf‘ RESIZE 2G;
--给出以 GB 为单位的数据库的实际大小
SELECT SUM (bytes)
/ 1024 / 1024 / 1024 AS GB
FROM dba_data_files;
--查询读取耗费前十个SQL
SELECT *
FROM (
SELECT ROWNUM,
SUBSTR(a.sql_text, 1,
200) sql_text,
TRUNC(a.disk_reads / DECODE(a.executions,
0,
1, a.executions)) reads_per_execution,
a.buffer_gets,
a.disk_reads,
a.executions,
a.sorts,
a.address
FROM v$sqlarea a
ORDER BY 3 DESC)
WHERE ROWNUM
< 10;
--在 Oracle 生成随机数据
SELECT LEVEL empl_id,
MOD (ROWNUM, 50000) dept_id,
TRUNC (DBMS_RANDOM.VALUE (1000,
500000),
2) salary,
DECODE (ROUND (DBMS_RANDOM.VALUE (
1,
2)),
1,
‘M‘,
2,
‘F‘) gender,
TO_DATE (
ROUND (DBMS_RANDOM.VALUE (
1,
28))
|| ‘-‘
|| ROUND (DBMS_RANDOM.VALUE (
1,
12))
|| ‘-‘
|| ROUND (DBMS_RANDOM.VALUE (
1900,
2010)),
‘DD-MM-YYYY‘)
dob,
DBMS_RANDOM.STRING (‘x‘, DBMS_RANDOM.VALUE (
20,
50)) address
FROM DUAL
CONNECT BY LEVEL < 10000;
常用SQL不定时更新
Oracle-平时常用的SQL
标签：读取 tst value pre column start 空间 acl and
