1, connection
SQL * Plus system / manager
2, display the current connection user
SQL> Show User
3. Which users have yourself?
SQL> SELECT * from ALL_USERS;
4, new users and authorize
SQL> CREATE User A Identified by A; (Default is built under the system table space)
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, establish the first table
SQL> CREATE TABLE A (A Number);
8, query form structure
SQL> DESC A
9, insert a new record
SQL> INSERT INTO A VALUES (1);
10, query record
SQL> SELECT * FROM A;
11, change record
SQL> Update a set a = 2;
12, delete record
SQL> Delete from A;
13, rollback
SQL> ROLL;
SQL> ROLLBACK;
14, submit
SQL> commit;
-------------------------------------------------- -------------
http://www.oradb.net/newuser/cg_sys.htm
-------------------------------------------------- -------------
Online information is much:
http://www.oraclefan.net/ocp/sql/dba_monitor.txt
-------------------------------------------------- ----------------
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" User "Temporary TableSpace" Temp "Account UNLOCK
GRANT "Connect" to "user_id";
User password setting:
ALTER USER "cmsdb" Identified by "pass_word"
Create of table space:
Create TableSpace "Table_Space" Logging DataFile 'C: /oracle/oradata/dbs/table_space.ora' size 5m
-------------------------------------------------- ----------------------
1, check all the current objects
SQL> SELECT * from Tab;
2, build a 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 TABLESPACAT A20SQL> SELECT B.FILE_ID file ID,
B. TableSpace_name table space,
B.file_name physical file name,
B.bytes total byte,
(B.bytes-sum (NVL (A.BYTES, 0)))))
SUM (NVL (A.BYTES, 0)))
SUM (NVL (A.BYTES, 0)) / (B.BYTES) * 100 remaining percentage
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 - Space Space Space
DBA_DATA_FILES - Data File Space Occupation
4, check the existing returns and their status
SQL> Col Segment Format A30
SQL> Select segment_name, oowner, tablespace_name, segment_id, file_id, status from dba_rollback_segs
5, check the path to 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 connection user
SQL> Show User
7, put SQL * Plus as a calculator
SQL> SELECT 100 * 20 from DUAL;
8, join 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, user copy data
SQL> Copy from User1 To User2 Create Table2 Using SELECT * from Table1;
11. ORDER BY can not be used in the view, but you can replace the sorting purpose with Group By.
SQL> CREATE VIEW A AS SELECT B1, B2 from B Group B1, B2;
12, create users through authorization
SQL> Grant Connect, Resource to Test Identified by Test
SQL> CONN TEST / TEST
13. Isolated all of the current users.
Select Unique TName from Colom
-------------------------------------------------- ---------------------
/ * Add a field to a form * /
Alter Table Alist_Table Add Address varcha2 (100);
/ * Modify the field properties field is empty * /
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, / * qq change to QQ2 * /
Address from alist_table;
Drop Table Alist_Table;
Rename alist_table_copy to alist_table / * Modify Table Name * /
Null
Sometimes the required column value cannot be empty
Create Table DePt (Deptno Number (2) Not Null, DName Char (14), LOC Char (13));
Add a column in the base table
Alter Table Dept
Add (Headcnt Number (3));
Modifying already column properties
Alter Table Dept
Modify DName Char (20);
Note: Only when all values are empty, they can reduce their column value width.
It can only change its column value type only when all values of a certain column are empty.
This is not NULL can only be defined when all values are not spaced.
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));
Find unconnected
Select Process, Osuser, Username, Machine, Logon_Time, SQL_Text
From v $ session a, v $ sqltext b where a.sql_address = B.Address;
-------------------------------------------------- ---------------
1. The data dictionary view starting with USER_ contains the information owned by the current user, queries the table information of the current user:
Select * from user_tables;
2. The data dictionary view starting with all_ contains the information owned by Oracle users.
Check all table information that users have or have access to:
Select * from all_tables;
3. The view starting with DBA_Mever only Oracle Database Administrators can access:
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 database users:
Create User_name Identified by Password;
Grant Connect to User_Name;
Grant Resource to user_name;
Authorized format: GRANT on tablename to username;
Delete a user (or table):
DROP User (Table) Username (Cascade) (CASCADE);
6. Import data tables to the built user
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")
