Common oracle SQL statements
Common oracle SQL statements
The ORACLE tutorial is commonly used SQL statements in oracle. SQL * Plus system/manager
2. display the current connected user
SQL> show user
3. view the users in the system
SQL> select * from all_users;
4. Create and authorize a user
SQL> create user a identified by a; (created in the SYSTEM tablespace by default)
SQL> grant connect, resource to;
5. connect to a new user
SQL> conn 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 the table structure
SQL> desc
9. Insert a new record
SQL> insert into a values (1 );
10. query records
SQL> select * from;
11. change history
SQL> update a set a = 2;
12. delete records
SQL> delete from;
13. rollback
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 a user:
Create user "user_id" PROFILE "DEFAULT" identified by "DEFAULT TABLESPACE
"USERS" temporary tablespace "TEMP" account unlock;
GRANT "CONNECT" TO "user_id ";
User password settings:
Alter user "CMSDB" identified by "pass_word"
Create a tablespace:
Create tablespace "table_space" LOGGING DATAFILE
'C: \ ORACLE \ ORADATA \ dbs \ table_space.ora 'SIZE 5 M
------------------------------------------------------------------------
1. view all objects currently
SQL> select * from tab;
2. Create an empty table with the same structure as Table
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 database size and space usage
SQL> col tablespace format a20
SQL> select B. file_id File ID,
B. tablespace_name tablespace,
B. file_name physical file name,
B. Total bytes,
(B. bytes-sum (nvl (a. bytes, 0) already in use,
Sum (nvl (a. bytes, 0) remaining,
Sum (nvl (a. bytes, 0)/(B. bytes) * 100 percentage 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 -- the remaining space in the tablespace
Dba_data_files -- data file space usage
4. view existing rollback segments and their statuses
SQL> col segment format a30
SQL> SELECT SEGMENT_NAME, OWNER, TABLESPACE_NAME, SEGMENT_ID, FILE_ID, STATUS FROM
DBA_ROLLBACK_SEGS;
5. view the data file placement path
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. connection string
SQL> select column 1 | Column 2 from table 1;
SQL> select concat (column 1, column 2) from table 1;
9. query 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 views, but it can be replaced by group by for sorting purposes.
SQL> create view a as select b1, b2 from B group by b1, b2;
12. Create a user 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 a table */
Alter table alist_table add address varchar2 (100 );
/* Modify the field attribute field to null */
Alter table alist_table modify address varchar2 (80 );
/* Modify the field name */
Create table alist_table_copy as select ID, NAME, PHONE, EMAIL,
QQ as QQ2,/* Change qq to qq2 */
ADDRESS from alist_table;
Drop table alist_table;
Rename alist_table_copy to alist_table
/* Modify the table name */
Null Value Processing
The column value cannot be empty.
Create table dept (deptno number (2) not null, dname char (14), loc char (13 ));
Add a column to the base table
Alter table dept
Add (headcnt number (3 ));
Modify attributes of an existing column
Alter table dept
Modify dname char (20 );
Note: The column value width can be reduced only when all values of a column are empty.
The column value type can be changed only when all values of a column are empty.
The column not null can be defined only when all values of a column are not null.
Example:
Alter table dept modify (loc char (12 ));
Alter table dept modify loc char (12 );
Alter table dept modify (dname char (13), loc char (12 ));
Search for undisconnected connections
Select process, osuser, username, machine, logon_time, SQL _text
From v $ session a, v $ sqltext B whe
[1]
The ORACLE tutorial is commonly used SQL statements in oracle. Re a. SQL _address = B. address;
-----------------------------------------------------------------
1. The data dictionary View starting with USER _ contains information owned by the current USER and queries the table information owned by the current USER:
Select * from user_tables;
2. The data dictionary View starting with ALL _ contains information owned by ORACLE users,
Query Information about all tables owned or accessed by the user:
Select * from all_tables;
3. Only the ORACLE database administrator can access the database starting with DBA:
Select * from dba_tables;
4. query ORACLE users:
Conn sys/change_on_install
Select * from dba_users;
Conn system/manager;
Select * from all_users;
5. Create a database user:
Create user user_name identified by password;
Grant connect to user_name;
Grant resource to user_name;
Authorization format: grant (permission) on tablename to username;
Delete a user (or table ):
Drop user (table) username (tablename) (cascade );
6. import data tables to the created users
Imp system/manager fromuser = FUSER_NAME TOUSER = USER_NAME FILE = C: \ EXPDAT. DMP
COMMIT = Y
7. Index
Create index [index_name] on [table_name] ("column_name ")
[2]
