Oracle Common commands under Linux

Open the graphical window:
1) Database Configuration Assistant windows (add DB Instance)
$ dbca
2) Oracle Net configuration Assistant windows (Configure listening)
$ netca
3) Open EM
$ Oemapp Dbastudio (Open Enterprise Manager graphical interface)
$ Opemapp Console (opens the Enterprise Manager graphical interface (same as the above command effect))

Common commands:
$ lsnrctl start|stop|status (Start | stop | Active state monitoring)
$ isqlplusctl start|stop (Start | Stop Isqlplus can log in to port 5560 in the browser)
$ sqlplus/nolog (start Sqlplus in a way that does not connect to the database)
$ sqlplus System/manager @ file.sql (Execute SQL script file)
$ sqlplus System/manager (login with system user Sqlplus)
$ imp system/manager file=/tmp/expfile.dmp log=/tmp/implogfile.log ignore=y fromuser=expuser touser=impuser (user-mode table data import , if no value is specified, the default value is used)
$ exp Username/password file=/tmp/expfile.dmp log=/tmp/prov114_exp.log (user mode table data export, which is The simplest way to export)

Sql> Conn/as sysdba (connect as SYSDBA user)
Sql> Startup
sql> shutdown
sql> shutdown immediate (close instance now)
sql> desc dba_users; (Query dba_users table structure)
Sql> select username from dba_users; (Query the username of all users under the current SID)
Sql> Select COUNT (*) from Username.tablename; (Query the number of rows in the TableName table)
Sql> drop user username cascade; (Delete the Oracle user named username)
Sql> SELECT DISTINCT table_name from User_tab_columns; (View all table names in the current user mode)

I. Startup and shutdown of Oracle
1, in a single-machine environment
To start or shut down an Oracle system, you must first switch to an Oracle user, as follows
Su-oracle

A. Start the Oracle system
Oracle>svrmgrl
Svrmgr>connect Internal
Svrmgr>startup
Svrmgr>quit

A. Start the Oracle system
Oracle>svrmgrl
Svrmgr>connect Internal
Svrmgr>startup
Svrmgr>quit

2, in the dual-machine environment
To start or shut down an Oracle system, you must first switch to the root user, as follows
Su-root

A. Start the Oracle system
Hareg-y Oracle

B. Shut down the Oracle system
Hareg-n Oracle

What kinds of startup methods are available for Oracle databases

Description

There are several ways to start:
1, Startup Nomount
Non-installation boot, this way startup executable: Rebuild the control file, rebuild the database

Read the Init.ora file and start the instance, which starts the SGA and background process, which requires only init.ora files

2. Startup Mount DBName
Installation starts, this way starts under executable:
Database log archiving,
Database Media Recovery,
Bring the data file online or offline,
Relocate the data file, redo the log file.

Execute "nomount", then open the control file, confirm the location of the data file and the online log file,
However, the data files and log files are not checked for validation at this time.

3. Startup Open dbname
Execute "nomount" first, then execute "Mount", and then open all the database files including the redo log file,
This way you can access the data in the database.

4, startup, equal to the following three commands
Startup Nomount
ALTER DATABASE Mount
ALTER DATABASE Open

5, startup restrict
Constrained mode start
This way the database can be started, but only users with certain privileges are allowed access
When a non-privileged user accesses, the following prompt appears:
ERROR:
Ora-01035:oracle only allow users with RESTRICTED SESSION permissions to use

6. Startup force
Forced start mode
When the database cannot be closed, you can use startup force to complete the shutdown of the database
Close the database first and then execute the normal startup database command

7. Startup pfile= parameter file name
Start mode with initialization parameter file
Read the parameter file first, and then start the database by setting it in the parameter file
Example: Startup Pfile=e:oracleadminoradbpfileinit.ora

8, startup EXCLUSIVE
Ii. how the user can effectively use the data dictionary
Oracle's data dictionary is one of the most important parts of a database, which is generated as a result of database changes,
manifests as some tables and views under the SYS user. The data dictionary name is an uppercase English character.

Data dictionary contains user information, user's permission information, all data object information, table constraints, statistical analysis database view and so on.
We cannot manually modify the information in the data dictionary.

Many times, the average Oracle user does not know how to use it effectively.

Dictionary The name and interpretation of all data dictionary tables, it has a synonym dict
Dict_column All data dictionary table field names and explanations

If we want to query the data dictionary associated with the index, we can use the following SQL statement:

Sql>select column_name,comments from Dict_columns where table_name= ' user_indexes ';

And so on, you can easily know the detailed name and explanation of the data dictionary without looking at other Oracle documentation.

The following is a list of the query usage methods for some Oracle user-common data dictionaries by category.

1. Users

View the default tablespace for the current user
Sql>select username,default_tablespace from User_users;

View the role of the current user
Sql>select * from User_role_privs;

View system and table-level permissions for the current user
Sql>select * from User_sys_privs;
Sql>select * from User_tab_privs;

2. Table

View all the tables under the user
Sql>select * from User_tables;

View a table with a name that contains a log character
Sql>select object_name,object_id from User_objects
where InStr (object_name, ' LOG ') >0;

To view the creation time of a table
Sql>select object_name,created from User_objects where Object_name=upper (' &table_name ');

View the size of a table
Sql>select sum (bytes)/(1024*1024) as "size (M)" from user_segments

where Segment_name=upper (' &table_name ');

View tables placed in Oracle's memory area
Sql>select Table_name,cache from User_tables where InStr (Cache, ' Y ') >0;

3. Index

View index number and category
Sql>select Index_name,index_type,table_name from User_indexes ORDER by TABLE_NAME;

View the fields indexed by the index
Sql>select * from User_ind_columns where Index_name=upper (' &index_name ');

To view the size of an index
Sql>select sum (bytes)/(1024*1024) as "size (M)" from user_segments
where Segment_name=upper (' &index_name ');

4. Serial number

View serial number, Last_number is the current value
Sql>select * from User_sequences;

5. View

View the name of the view
Sql>select view_name from User_views;

View the SELECT statement that created the view
Sql>set view_name,text_length from User_views;
Sql>set Long 2000; Description: The size of the set long can be set according to the Text_length value of the view
Sql>select text from User_views where View_name=upper (' &view_name ');

6. Synonyms

View the name of a synonym
Sql>select * from user_synonyms;

7. Constraint conditions

View constraints on a table
Sql>select constraint_name, Constraint_type,search_condition, R_constraint_name
From user_constraints WHERE table_name = UPPER (' &table_name ');

Sql>select C.constraint_name,c.constraint_type,cc.column_name
From User_constraints c,user_cons_columns cc
where C.owner = Upper (' &table_owner ') and c.table_name = Upper (' &table_name ')
and C.owner = Cc.owner and c.constraint_name = Cc.constraint_name
Order BY Cc.position;

8. Storage functions and procedures

View the status of functions and procedures
Sql>select object_name,status from user_objects where object_type= ' FUNCTION ';
Sql>select object_name,status from user_objects where object_type= ' PROCEDURE ';

View source code for functions and procedures
Sql>select text from All_source where Owner=user and Name=upper (' &plsql_name ');

Third, view the SQL of the database
1. View the name and size of the table space

Select T.tablespace_name, round (SUM (bytes/(1024*1024)), 0) ts_size
From Dba_tablespaces T, Dba_data_files D
where t.tablespace_name = D.tablespace_name
Group BY T.tablespace_name;

2. View the table space physical file name and size

Select Tablespace_name, file_id, file_name,
Round (bytes/(1024*1024), 0) Total_space
From Dba_data_files
Order BY Tablespace_name;

3. Check the rollback segment name and size

Select Segment_name, Tablespace_name, R.status,
(initial_extent/1024) Initialextent, (next_extent/1024) nextextent,
Max_extents, V.curext curextent
From Dba_rollback_segs R, V$ro

Oracle Common commands under Linux