The most common commands in oracle

1: enable the Service:
OracleServiceACCP and OracleOraHome92TNSListener
2: Start sqlplus in the running state to enter the Oracle editing window.
3: Open Table space:
Create tablespace space name
DataFile 'e: Control name. dbf' size 10 M space storage disk and space size
Autoextend on space can be increased
/Submit
4. Create a user:
Create user Username
Identified by user password
The Default tablespace space name specifies the user's tablespace.
/
5. Authorize the user:
Grant connect, resource to user name;
Operations from 1 to 5 are performed by administrators.
6. User Logon:
Conn username/user password;
7. Create a table: Create table Name (column name data type, column name data type );
8: add data to the table:
Insert into table name values (parameter 1, parameter 2 );
Table creation operations from 6 to 8
Use the system/manager Administrator to log on to a new user and create a new user;
Then, log on to the user who created the table again and grant the new user the operation permission to operate on the table created by the original user: grant select on table name to the new user name;
Log on with the new user again for query:

9: clear screen
10: Create a new table using an existing table
Create table new table name as select */or a column or multiple columns in the existing table from the existing table name; restrictions:
For example, create table book as select * from bookItems where name = 'Return of kings ';
11. grant permissions to users
Grant select on table name to the user name to be granted;
----- Allow users to query records of the table
Grant update on table name to the user name to be granted;
----- Allow users to update records of the table
Grant all on table name to the user name to be granted;
----- Allow users to insert, delete, update, and query records of the table
12. Change and delete a user
Change User Password:
Alter user Username identified by new password;
Delete A User:
Drop user Username;
13: Create a primary key for the table
Alter table name add constraint pk _ column name primary key (column name );
Or the alter table name add constraint pri _ column name primary key (column name );
14. delete a table
Drop table name;
15. Delete table data
Delete from table name;
16: add primary and foreign key constraints to the two tables
Alter table name <foreign key> add constraint frk _ foreign key (foreign key) references table name (primary key)
For example, alter table books add constraint frk_id foreign key (id) references bookStyle (styleId );

17: Create a range partition (create a partition while creating the target)
For example:
Create table Name (column name type, column name type, column name type) -- Create table
Partition by range (Partition based column name)
(
Partition name 1 values less than (condition );
Partition name 2 values less than (condition );
)
18: Insert a partition after the last partition
Alter table name add partition name values less than (maxvalue );
19: merge partitions
Merge partitions 2 and 1 (only small ones can be merged into large ones)
Alter table name: merge partitions partition name 1, partition name 2 into partition name 2;
20: Delete table partitions
Alter table Name drop partition name
Delete all data in this partition
21: partition Truncation
Alter table name truncate partition name
This partition is retained but cannot be operated on.

22: grant the user grant create public synonym to the user name when creating a common synonym;
To access a shared synonym, you must have the permission to access a shared synonym. A shared synonym can be accessed by anyone (you must have the permission to access it ), when a common synonym name is the same as a wildcard name, the user can only access a private synonym.

23: Create Sequence
1) Create sequence name
2) Start with Start serial number -- specify the Start serial number
3) Increment by the number of intervals between serial numbers -- specify the number of intervals between serial numbers
4) Maxvalue maximum number of serial numbers -- [specify the maximum number of serial numbers]
5) Minimum number of Minvalue serial numbers-specify the minimum value of the serial number [Create according to the actual situation]
6) Nocycle -- [stop generating the next value after reaching the maximum Sequence Value]
7) Number of Cache allocated sequence numbers-[specify the number of pre-allocated sequence numbers in the memory] must be added at the time of sequence creation. The remaining values can be added according to the conditions.
A sequence can be used in multiple columns or in different table objects.
A table can have multiple sequences.
When the data in the table is deleted, the serial number in the data object is also deleted (the sequence cannot automatically fill in the deleted serial number)
Sequence usage:
For example, insert into table name values (sequence name. nextval, 'column value ');
24: Find the current value of the sequence
Select sequence name. currval from dual;
25: Modify the sequence (the start with parameter of the sequence cannot be changed)
Alter sequence name sequence attribute parameter cycle;
26: delete Sequence
Drop sequence name;