The Oracle tutorial you are looking at is: Oracle Common SQL statements. Sql*plus System/manager
2. Display the current connected user
Sql> Show User
3. See which users the system has
Sql> select * from All_users;
4. New user and authorize
Sql> create User a identified by A; (default is built under System tablespace)
Sql> Grant Connect,resource to A;
5. Connect to New User
Sql> Conn a/a
6. Query all objects under current user
Sql> select * from tab;
7, establish the first table
Sql> CREATE Table A (a number);
8. Query table structure
Sql> desc A
9. Insert new record
Sql> INSERT into a values (1);
10. Inquiry record
Sql> select * from A;
11, change the record
sql> update a set a=2;
12, delete the record
Sql> Delete from A;
13. Roll 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
To 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 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, the construction of a table and the same empty 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 where 1=2;
3, look at the size of the database, and space use
SQL > Col tablespace format A20
SQL > select b.file_id file ID,
B.tablespace_name table Space,
B.file_name Physical file name,
The total number of bytes b.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--The state of space remaining in the table space
Dba_data_files--Data file space occupancy
4. View existing rollback segments and their status
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, the 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 (Columns 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. Copy data between users
SQL > Copy from user1 to User2 create table2 a using select * from table1;
11, the view can not use order by, but can be replaced by a group by to achieve the sorting purpose
SQL > CREATE View A as select B1,b2 from B group by B1,B2;
12, through the way of authorization to create users
SQL > Grant Connect,resource to test identified by test;
SQL > Conn Test/test
13, find out the current user all table name.
Select unique Tname from col;
-----------------------------------------------------------------------
/* Add a field to a table * *
ALTER TABLE alist_table add address varchar2 (100);
/* Modify field Property field is 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 changed to qq2*/
Address from alist_table;
drop table alist_table;
Rename Alist_table_copy to Alist_table
/* Modify the table name * *
Null value processing
Sometimes it is required that the column value cannot be empty
CREATE TABLE Dept (deptno Number (2) not NULL, Dname char (), loc char (13));
Add a column to the base table
ALTER TABLE Dept
Add (headcnt number (3));
Modify an existing column property
ALTER TABLE Dept
Modify Dname char (20);
Note: You can reduce the width of column values only if all the values in a column are empty.
The column value type can be changed only if all the values of a column are empty.
You can define not NULL only if all of the values in a column are not empty.
Cases:
ALTER TABLE Dept Modify (Loc char (12));
ALTER TABLE Dept Modify LOC Char (12);
ALTER TABLE Dept Modify (Dname char (), loc char (12));
Find a disconnected connection
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 Common SQL statements. Re a.sql_address=b.address;
-----------------------------------------------------------------
1. The data dictionary view starting with User_ contains information owned by the current user, querying the table information owned by the current user:
SELECT * from User_tables;
2. The data dictionary view starting with All_ contains information that is owned by Oracle users.
Query all table information that the user owns or has access to:
SELECT * from All_tables;
3. Views starting with Dba_ are generally accessible only to Oracle database administrators:
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;
Authorized format: Grant (permission) on TableName to username;
Delete User (or table):
Drop User (table) Username (tablename) (cascade);
6. Import data tables to the built users
IMP System/manager Fromuser = fuser_name Touser = user_name FILE = C:\EXPDAT. CM7
COMMIT = Y
7. Index
CREATE INDEX [index_name] on [table_name] ("column_name")
prev [1] [2]
