Basic Oracle commands

Quickly create a user to import data

Sqlplus/nolog

Conn/As sysdba;

Drop user Feiyang cascade;

Create user Feiyang identified by Feiyang;

Grant create session to Feiyang;

Grant resource, connect, DBA to Feiyang;

Conn Feiyang/Feiyang;

Select table_name from user_tables;

IMP Feiyang/Feiyang @ oraclefile = D: \ Feiyang. dmp full = y;

Exp Feiyang/Feiyang @ oraclefile = D: \ Feiyang. dmp owner = (Feiyang );

 

Login:

 

Sqlplus/nolog

 

Conn apps/apps as sysdba

 

Alter user Scott identified by tiger;

 

Alter user Scott account unlock;

 

Conn Scott/tiger;

 

Forgot password:

 

Conn/As sysdba

 

Unlock system or create DBA users

 

Enable the oracledbconsoleorcl Service

 

Https: // localhost: 1158/EM/Console System: system you can modify user information on the GUI.

 

View Sid

 

Select instance_name from V $ instance;

 

View Port:

 

Oracle-oradb11g_home1 --> Configuration and porting tools --> net manager (Service name, listener

 

Order)

 

 

 

Create a user:

 

Createuser rain identified by rain

 

Modify:

 

Alter user rain identified by rain1 // Change Password

 

Alter user account unlock // unlock

 

Delete A User:

 

Drop user rain Cascade

 

Grant Permissions

 

Grantcreate session to rain // log on

 

Grant unlimited tablespace to rain // The tablespace is used to create a table.

 

Grant create table to rain // create table permission

 

Grant select on mytab to rain // grant the permission to query tables of other users

 

Grant all on mytab to rain;

 

Grant resource, connect, DBA to rain

 

Resource: You can create clusters, tables, sequences, and PL/SQL programming program objects, including processes, packages, and triggers.

 

Connect: a database connection role, used to connect to a database. It has the right to create clusters, database links, sequences, synonyms, tables, and views, and modify sessions.

 

Revoke permissions

 

Revoke create table from rain

 

Revoke all on mytab to rain

 

View message

 

 

View users:

 

Show user; // The current user

 

Select username from dba_users; // All Users

 

View table

 

Desc table_name // view the table structure

 

Sets long 100000

Setpages 0

Select dbms_metadata.get_ddl ('table', 'dept') from dual; // view the complete table structure

 

Select table_name from user_tables // view all tables under the user

 

Select * from cat // table, view, sequence

 

Select object_name, object_type, namespacefrom user_objects; // view the object

 

 

// Check whether the indexes of other users exist in the system table.

 

Select * From dba_indexes wheretablespace_name = 'system'

 

And owner not in ('sys ', 'system ');

 

 

 

Set Screen

 

 

 

Generally, the SQL plus operation under SQL> requires necessary environment settings to complete the output we need. The SET command and corresponding environment variables are used to set all environments. The following are common environment settings:

 

 

 

Show all show all parameters

 

CTRL + C Exit PLSQL or interrupt Query

 

 

SQL> set heading off -- disable the output column title. The default value is on.

 

SQL> set feedback off -- disable counting feedback of the last row. The default value is "send back on" for 6 or more records"

 

SQL> set timing on -- the default value is off. It sets the query time, which can be used to estimate the SQL statement execution time and test the performance.

 

SQL> set sqlprompt "SQL>" -- sets the default prompt. The default value is "SQL>"

 

SQL> set pagesize 0; -- sets the number of pages displayed for the query result. If it is set to 0, the result is in the most closely formatted format.

 

SQL> set linesize 1000 -- sets the number of characters displayed on a line on the screen. The default value is 100.

 

SQL> set autocommit on -- sets whether to submit automatically. The default value is off.

 

SQL> set pause on -- the default value is off. When this parameter is set to suspend, the screen will be stopped. Wait for the Enter key to display the next page.

 

SQL> set arraysize 1 -- the default value is the number of rows that can be extracted (FETCH) at a time of 15. 1-> 5000. If a long field exists, it should be smaller.

 

SQL> set long 1000 -- the default value is 80. This is used when the long field is not fully displayed.

 

SQL> set space 2 ----- set the interval between output columns (generally unavailable)

 

 

Save query results to files

 

Spool c: \ create_table. SQL;

 

Select dbms_metadata.get_ddl ('table', 'dept') from dual; // view the table creation statement

 

Spool off;

 

 

 

Import and Export

 

Data Pump

 

Exp Feiyang/Feiyang @ orclfile = D: \ Feiyang. dmp owner = (Feiyang)

 

IMP Feiyang/Feiyang @ orclfile = D: \ Feiyang. dmp full = y

 

@ There cannot be shell before and after

 

Import SQL files:

Log on to the user in cmd and run

 

@ D: \ SQL. SQL

Start and close the service

Start oracle in cmd mode:
Net start oracleserviceorcl (first start the database and then start listening to Listener Control)
LSNRCTL start
The service is closed as follows:
LSNRCTL stop
Net stop
Oracleserviceorcl