oracle often USES sql statements
- 2020-05-12 06:23:33
- OfStack
The ORACLE tutorial you are looking at is :oracle commonly used sql statements. SQL * Plus system/manager
2. Display the current connected user
SQL > show user
3. Check which users the system has
SQL > select * from all_users;
4. Create new users and authorize them
SQL > create user a identified by a; (built under SYSTEM tablespace by default)
SQL > grant connect,resource to a;
5. Connect to new users
SQL > conn a/a
6. Query all objects under the current user
SQL > select * from tab;
7. Create the first table
SQL > create table a(a number);
8. Query table structure
SQL > desc a
Insert a new record
SQL > insert into a values(1);
10. Query records
SQL > select * from a;
11. Change records
SQL > update a set a=2;
12. Delete records
SQL > delete from a;
13, rolled back
SQL > roll;
SQL > rollback;
14, submit
SQL > commit;
User authorization:
GRANT ALTER ANY INDEX TO "user_id "
GRANT "dba " TO "user_id ";
ALTER USER "user_id " DEFAULT ROLE ALL
Create user:
CREATE USER "user_id " PROFILE "DEFAULT " IDENTIFIED BY " DEFAULT TABLESPACE
"USERS " TEMPORARY TABLESPACE "TEMP " ACCOUNT UNLOCK;
GRANT "CONNECT " TO "user_id ";
User password setting:
ALTER USER "CMSDB " IDENTIFIED BY "pass_word "
Table space creation:
CREATE TABLESPACE "table_space " LOGGING DATAFILE
'C:\ORACLE\ORADATA\dbs\table_space.ora' SIZE 5M
------------------------------------------------------------------------
1. View all current objects
SQL > select * from tab;
2. Build an empty table like a table structure
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, check the size of the database, and space usage
SQL > col tablespace format a20
SQL > select b. file_id ID,
b.tablespace_name table space,
b.file_name physical file name,
b.bytes total number of bytes,
(b.bytes-sum (nvl(a.bytes,0)) has been used,
sum (nvl (a. bytes, 0)) remaining,
sum(nvl(a.bytes,0))/(b.bytes)*100 percent remaining
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 -- state of remaining table space
dba_data_files -- data file space usage
4. View the existing rollback segment and its state
SQL > col segment format a30
SQL > SELECT SEGMENT_NAME,OWNER,TABLESPACE_NAME,SEGMENT_ID,FILE_ID,STATUS FROM
DBA_ROLLBACK_SEGS;
5. View the location path of the data file
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. Display the current connected user
SQL > show user
7. Use SQL*Plus as a calculator
SQL > select 100*20 from dual;
8. Concatenate strings
SQL > select column 1 | | column 2 from table 1;
SQL > select concat(column 1, column 2) from table 1;
9. Check the current date
SQL > select to_char(sysdate,'yyyy-mm-dd,hh24:mi:ss') from dual;
10. Data replication between users
SQL > copy from user1 to user2 create table2 using select * from table1;
11. order by cannot be used in the view, but group by can be used instead to achieve the ordering purpose
SQL > create view a as select b1,b2 from b group by b1,b2;
Create users through authorization
SQL > grant connect,resource to test identified by test;
SQL > conn test/test
13. Check all the table names of the current user.
select unique tname from col;
-----------------------------------------------------------------------
Add fields to 1 table */
alter table alist_table add address varchar2(100);
Modify the field property field to be empty */
alter table alist_table modify address varchar2(80);
/* modify field name */
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
/* modify the table name */
A null value processing
Sometimes column values are required to be non-null
create table dept (deptno number(2) not null, dname char(14), loc char(13));
Add 1 column to the base table
alter table dept
add (headcnt number(3));
Modify the existing column properties
alter table dept
modify dname char(20);
Note: the column width can be reduced only if all the values in a column are empty.
You can only change the column value type if all the values in a column are empty.
The column not null can only be defined if all the values in a column are not empty.
Ex. :
alter table dept modify (loc char(12));
alter table dept modify loc char(12);
alter table dept modify (dname char(13),loc char(12));
Find the connection that is not broken
select process,osuser,username,machine,logon_time ,sql_text
from v$session a,v$sqltext b whe
-----------------------------------------------------------------
1. The data dictionary view starting with USER_ contains the information owned by the current user, and queries the table information owned by the current user:
select * from user_tables;
2. Data dictionary view starting with ALL_ contains information owned by ORACLE users,
Query all table information that the user owns or has access to:
select * from all_tables;
3. View 1 starting with DBA_ is accessible only to ORACLE database administrators:
select * from dba_tables;
4. Query ORACLE user:
conn sys/change_on_install
select * from dba_users;
conn system/manager;
select * from all_users;
5. Create database users:
CREATE USER user_name IDENTIFIED BY password;
GRANT CONNECT TO user_name;
GRANT RESOURCE TO user_name;
Format of authorization: grant (permission) on tablename to username;
Delete user (or table):
drop user(table) username(tablename) (cascade);
6. Import the data table to the built user
IMP SYSTEM/MANAGER FROMUSER = FUSER_NAME TOUSER = USER_NAME FILE = C:\EXPDAT.DMP
COMMIT = Y
Index of 7.
create index [index_name] on [table_name]( "column_name ")
2. Display the current connected user
SQL > show user
3. Check which users the system has
SQL > select * from all_users;
4. Create new users and authorize them
SQL > create user a identified by a; (built under SYSTEM tablespace by default)
SQL > grant connect,resource to a;
5. Connect to new users
SQL > conn a/a
6. Query all objects under the current user
SQL > select * from tab;
7. Create the first table
SQL > create table a(a number);
8. Query table structure
SQL > desc a
Insert a new record
SQL > insert into a values(1);
10. Query records
SQL > select * from a;
11. Change records
SQL > update a set a=2;
12. Delete records
SQL > delete from a;
13, rolled back
SQL > roll;
SQL > rollback;
14, submit
SQL > commit;
User authorization:
GRANT ALTER ANY INDEX TO "user_id "
GRANT "dba " TO "user_id ";
ALTER USER "user_id " DEFAULT ROLE ALL
Create user:
CREATE USER "user_id " PROFILE "DEFAULT " IDENTIFIED BY " DEFAULT TABLESPACE
"USERS " TEMPORARY TABLESPACE "TEMP " ACCOUNT UNLOCK;
GRANT "CONNECT " TO "user_id ";
User password setting:
ALTER USER "CMSDB " IDENTIFIED BY "pass_word "
Table space creation:
CREATE TABLESPACE "table_space " LOGGING DATAFILE
'C:\ORACLE\ORADATA\dbs\table_space.ora' SIZE 5M
------------------------------------------------------------------------
1. View all current objects
SQL > select * from tab;
2. Build an empty table like a table structure
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, check the size of the database, and space usage
SQL > col tablespace format a20
SQL > select b. file_id ID,
b.tablespace_name table space,
b.file_name physical file name,
b.bytes total number of bytes,
(b.bytes-sum (nvl(a.bytes,0)) has been used,
sum (nvl (a. bytes, 0)) remaining,
sum(nvl(a.bytes,0))/(b.bytes)*100 percent remaining
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 -- state of remaining table space
dba_data_files -- data file space usage
4. View the existing rollback segment and its state
SQL > col segment format a30
SQL > SELECT SEGMENT_NAME,OWNER,TABLESPACE_NAME,SEGMENT_ID,FILE_ID,STATUS FROM
DBA_ROLLBACK_SEGS;
5. View the location path of the data file
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. Display the current connected user
SQL > show user
7. Use SQL*Plus as a calculator
SQL > select 100*20 from dual;
8. Concatenate strings
SQL > select column 1 | | column 2 from table 1;
SQL > select concat(column 1, column 2) from table 1;
9. Check the current date
SQL > select to_char(sysdate,'yyyy-mm-dd,hh24:mi:ss') from dual;
10. Data replication between users
SQL > copy from user1 to user2 create table2 using select * from table1;
11. order by cannot be used in the view, but group by can be used instead to achieve the ordering purpose
SQL > create view a as select b1,b2 from b group by b1,b2;
Create users through authorization
SQL > grant connect,resource to test identified by test;
SQL > conn test/test
13. Check all the table names of the current user.
select unique tname from col;
-----------------------------------------------------------------------
Add fields to 1 table */
alter table alist_table add address varchar2(100);
Modify the field property field to be empty */
alter table alist_table modify address varchar2(80);
/* modify field name */
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
/* modify the table name */
A null value processing
Sometimes column values are required to be non-null
create table dept (deptno number(2) not null, dname char(14), loc char(13));
Add 1 column to the base table
alter table dept
add (headcnt number(3));
Modify the existing column properties
alter table dept
modify dname char(20);
Note: the column width can be reduced only if all the values in a column are empty.
You can only change the column value type if all the values in a column are empty.
The column not null can only be defined if all the values in a column are not empty.
Ex. :
alter table dept modify (loc char(12));
alter table dept modify loc char(12);
alter table dept modify (dname char(13),loc char(12));
Find the connection that is not broken
select process,osuser,username,machine,logon_time ,sql_text
from v$session a,v$sqltext b whe
[1] [2] next page
The ORACLE tutorial you are looking at is :oracle commonly used sql statements. re a. sql_address = b. address;-----------------------------------------------------------------
1. The data dictionary view starting with USER_ contains the information owned by the current user, and queries the table information owned by the current user:
select * from user_tables;
2. Data dictionary view starting with ALL_ contains information owned by ORACLE users,
Query all table information that the user owns or has access to:
select * from all_tables;
3. View 1 starting with DBA_ is accessible only to ORACLE database administrators:
select * from dba_tables;
4. Query ORACLE user:
conn sys/change_on_install
select * from dba_users;
conn system/manager;
select * from all_users;
5. Create database users:
CREATE USER user_name IDENTIFIED BY password;
GRANT CONNECT TO user_name;
GRANT RESOURCE TO user_name;
Format of authorization: grant (permission) on tablename to username;
Delete user (or table):
drop user(table) username(tablename) (cascade);
6. Import the data table to the built user
IMP SYSTEM/MANAGER FROMUSER = FUSER_NAME TOUSER = USER_NAME FILE = C:\EXPDAT.DMP
COMMIT = Y
Index of 7.
create index [index_name] on [table_name]( "column_name ")
Previous page [1] [2]