oracle常用sql语句
正在看的ORACLE教程是:oracle常用sql语句。SQL*Plus system/manager2、显示当前连接用户
SQL> show user
3、查看系统拥有哪些用户
SQL> select * from all_users;
4、新建用户并授权
SQL> create user a identified by a;(默认建在SYSTEM表空间下)
SQL> grant connect,resource to a;
5、连接到新用户
SQL> conn a/a
6、查询当前用户下所有对象
SQL> select * from tab;
7、建立第一个表
SQL> create table a(a number);
8、查询表结构
SQL> desc a
9、插入新记录
SQL> insert into a values(1);
10、查询记录
SQL> select * from a;
11、更改记录
SQL> update a set a=2;
12、删除记录
SQL> delete from a;
13、回滚
SQL> roll;
SQL> rollback;
14、提交
SQL> commit;
用户授权:
GRANT ALTER ANY INDEX TO "user_id "
GRANT "dba " TO "user_id ";
ALTER USER "user_id " DEFAULT ROLE ALL
创建用户:
CREATE USER "user_id " PROFILE "DEFAULT " IDENTIFIED BY " DEFAULT TABLESPACE
"USERS " TEMPORARY TABLESPACE "TEMP " ACCOUNT UNLOCK;
GRANT "CONNECT " TO "user_id ";
用户密码设定:
ALTER USER "CMSDB " IDENTIFIED BY "pass_word "
表空间创建:
CREATE TABLESPACE "table_space " LOGGING DATAFILE
'C:\ORACLE\ORADATA\dbs\table_space.ora' SIZE 5M
------------------------------------------------------------------------
1、查看当前所有对象
SQL > select * from tab;
2、建一个和a表结构一样的空表
SQL > create table b as select * from a where 1=2;
SQL > create table b(b1,b2,b3) as select a1,a2,a3 from a where 1=2;
3、察看数据库的大小,和空间使用情况
SQL > col tablespace format a20
SQL > select b.file_id 文件ID,
b.tablespace_name 表空间,
b.file_name 物理文件名,
b.bytes 总字节数,
(b.bytes-sum(nvl(a.bytes,0))) 已使用,
sum(nvl(a.bytes,0)) 剩余,
sum(nvl(a.bytes,0))/(b.bytes)*100 剩余百分比
from dba_free_space a,dba_data_files b
where a.file_id=b.file_id
group by b.tablespace_name,b.file_name,b.file_id,b.bytes
order by b.tablespace_name
/
dba_free_space --表空间剩余空间状况
dba_data_files --数据文件空间占用情况
4、查看现有回滚段及其状态
SQL > col segment format a30
SQL > SELECT SEGMENT_NAME,OWNER,TABLESPACE_NAME,SEGMENT_ID,FILE_ID,STATUS FROM
DBA_ROLLBACK_SEGS;
5、查看数据文件放置的路径
SQL > col file_name format a50
SQL > select tablespace_name,file_id,bytes/1024/1024,file_name from
dba_data_files order by file_id;
6、显示当前连接用户
SQL > show user
7、把SQL*Plus当计算器
SQL > select 100*20 from dual;
8、连接字符串
SQL > select 列1 | |列2 from 表1;
SQL > select concat(列1,列2) from 表1;
9、查询当前日期
SQL > select to_char(sysdate,'yyyy-mm-dd,hh24:mi:ss') from dual;
10、用户间复制数据
SQL > copy from user1 to user2 create table2 using select * from table1;
11、视图中不能使用order by,但可用group by代替来达到排序目的
SQL > create view a as select b1,b2 from b group by b1,b2;
12、通过授权的方式来创建用户
SQL > grant connect,resource to test identified by test;
SQL > conn test/test
13、查出当前用户所有表名。
select unique tname from col;
-----------------------------------------------------------------------
/* 向一个表格添加字段 */
alter table alist_table add address varchar2(100);
/* 修改字段 属性 字段为空 */
alter table alist_table modify address varchar2(80);
/* 修改字段名字 */
create table alist_table_copy as select ID,NAME,PHONE,EMAIL,
QQ as QQ2, /*qq 改为qq2*/
ADDRESS from alist_table;
drop table alist_table;
rename alist_table_copy to alist_table
/* 修改表名 */
空值处理
有时要求列值不能为空
create table dept (deptno number(2) not null, dname char(14), loc char(13));
在基表中增加一列
alter table dept
add (headcnt number(3));
修改已有列属性
alter table dept
modify dname char(20);
注:只有当某列所有值都为空时,才能减小其列值宽度。
只有当某列所有值都为空时,才能改变其列值类型。
只有当某列所有值都为不空时,才能定义该列为not null。
例:
alter table dept modify (loc char(12));
alter table dept modify loc char(12);
alter table dept modify (dname char(13),loc char(12));
查找未断连接
select process,osuser,username,machine,logon_time ,sql_text
from v$session a,v$sqltext b whe
[1]
正在看的ORACLE教程是:oracle常用sql语句。re a.sql_address=b.address;-----------------------------------------------------------------
1.以USER_开始的数据字典视图包含当前用户所拥有的信息, 查询当前用户所拥有的表信息:
select * from user_tables;
2.以ALL_开始的数据字典视图包含ORACLE用户所拥有的信息,
查询用户拥有或有权访问的所有表信息:
select * from all_tables;
3.以DBA_开始的视图一般只有ORACLE数据库管理员可以访问:
select * from dba_tables;
4.查询ORACLE用户:
conn sys/change_on_install
select * from dba_users;
conn system/manager;
select * from all_users;
5.创建数据库用户:
CREATE USER user_name IDENTIFIED BY password;
GRANT CONNECT TO user_name;
GRANT RESOURCE TO user_name;
授权的格式: grant (权限) on tablename to username;
删除用户(或表):
drop user(table) username(tablename) (cascade);
6.向建好的用户导入数据表
IMP SYSTEM/MANAGER FROMUSER = FUSER_NAME TOUSER = USER_NAME FILE = C:\EXPDAT.DMP
COMMIT = Y
7.索引
create index [index_name] on [table_name]( "column_name ")
[2]

This article explores optimizing MySQL memory usage in Docker. It discusses monitoring techniques (Docker stats, Performance Schema, external tools) and configuration strategies. These include Docker memory limits, swapping, and cgroups, alongside

This article addresses MySQL's "unable to open shared library" error. The issue stems from MySQL's inability to locate necessary shared libraries (.so/.dll files). Solutions involve verifying library installation via the system's package m

The article discusses using MySQL's ALTER TABLE statement to modify tables, including adding/dropping columns, renaming tables/columns, and changing column data types.

This article compares installing MySQL on Linux directly versus using Podman containers, with/without phpMyAdmin. It details installation steps for each method, emphasizing Podman's advantages in isolation, portability, and reproducibility, but also

This article provides a comprehensive overview of SQLite, a self-contained, serverless relational database. It details SQLite's advantages (simplicity, portability, ease of use) and disadvantages (concurrency limitations, scalability challenges). C

This guide demonstrates installing and managing multiple MySQL versions on macOS using Homebrew. It emphasizes using Homebrew to isolate installations, preventing conflicts. The article details installation, starting/stopping services, and best pra

Article discusses configuring SSL/TLS encryption for MySQL, including certificate generation and verification. Main issue is using self-signed certificates' security implications.[Character count: 159]

Article discusses popular MySQL GUI tools like MySQL Workbench and phpMyAdmin, comparing their features and suitability for beginners and advanced users.[159 characters]


Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

AI Hentai Generator
Generate AI Hentai for free.

Hot Article

Hot Tools

PhpStorm Mac version
The latest (2018.2.1) professional PHP integrated development tool

SublimeText3 Mac version
God-level code editing software (SublimeText3)

mPDF
mPDF is a PHP library that can generate PDF files from UTF-8 encoded HTML. The original author, Ian Back, wrote mPDF to output PDF files "on the fly" from his website and handle different languages. It is slower than original scripts like HTML2FPDF and produces larger files when using Unicode fonts, but supports CSS styles etc. and has a lot of enhancements. Supports almost all languages, including RTL (Arabic and Hebrew) and CJK (Chinese, Japanese and Korean). Supports nested block-level elements (such as P, DIV),

Notepad++7.3.1
Easy-to-use and free code editor

Safe Exam Browser
Safe Exam Browser is a secure browser environment for taking online exams securely. This software turns any computer into a secure workstation. It controls access to any utility and prevents students from using unauthorized resources.
