"Oracle" First use oracle12c want to know some of the operations ____oracle

When you first use Oracle database, we may find it more difficult, in general, the initial use of a database, we would like to know what the operation is, the author believes that may be the following questions.

1, how to enter the SQL implementation interface

method One : Open the command prompt, enter sqlplus directly to enter the SQL interface;

method Two : Use Oracle's Sqlplus program by selecting the Installer (oracle-oradb12home1) from the Windwos Start menu to Oracle, and then "Application Development---" SQL Plus ";

method Three : It is said that you can use the tool-oracle SQL Developer, which is installed after the completion of ORACLE12C, and open the way is under "application development", with "SQL Plus" together. (Accessed using browser mode

, such as: Http://localhost:1521/isqlplus, which is available in the Oracle 10g version, but is said to have been lost from Oracle 11g, which was replaced by Oracle SQL developer. ）

When you first use a database and enter the SQL interface, you can log on using SYS or the System DBA user. Then you can manipulate the database, including building your own user, table structure, and so on. If you want to use a regular user, such as Scott, you need to unlock your initial use.

2. Unlock scott user and log in Scott user

Log on to the SYS user and log in as a sysdba, and then unlock the Scott user.

C:\users\v1000>sqlplus SYS as SYSDBA

Sql*plus:release 12.1.0.1.0 Production on Saturday October 11 10:33:26 2014

Copyright (c) 1982, 2013, Oracle. All rights reserved.

Enter Password:
Connect to:
Oracle Database 12c Enterprise Edition release 12.1.0.1.0-64bit Production
With the partitioning, OLAP, Advanced Analytics and real application testing options

Sql> Show user;
USER is "SYS"
sql> alter user Scott account unlock;

The user has changed.

After the unlock, you can log in to Scott User, command line input: Sqlplus Scott/tiger, the first time you use Scott, the system will prompt you to expire the password, you want to modify, the proposed change in the value or tiger, so easy to remember.

C:\users\v1000>sqlplus Scott/tiger

Sql*plus:release 12.1.0.1.0 Production on Saturday October 11 10:36:43 2014

Copyright (c) 1982, 2013, Oracle. All rights reserved.

ERROR:
ora-28001:the Password has expired


Change Scott's password
New Password:
Re-type the new password:
Password has changed

Connect to:
Oracle Database 12c Enterprise Edition release 12.1.0.1.0-64bit Production
With the partitioning, OLAP, Advanced Analytics and real application testing options

Sql>

Modify password command: Alter user system identified by pwd123 3, how to create an Oracle user

Create User C # #WolfOfSiberian identified by Quietwolf;

Grant CONNECT,RESOURCE,DBA to C # #WolfOfSiberian (select username from all_users;)

Commit

Reference Link: http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_8003.htm#SQLRF01503

4, how to view the current operation of users and the current use of the database

4.1 View current operation user

Show user;

4.2 Viewing the currently used database

Apply to all users:

Select Sys_context (' USERENV ', ' instance_name ') from dual;

Users with DBA authority:

Select name from V$database;

Show parameter name

4.3 View All tables in the current database

Select table_name from User_tables;

4.4 View Table Structure

DESC EMP;

4.5 Other

--View all tables under this user in the Oracle database
SELECT table_name from User_tables;

--View all tables under all users in the Oracle database
Select User,table_name from All_tables;

--View all columns under this user in the Oracle database
Select Table_name,column_name from User_tab_columns;

--View all columns under this user in the Oracle database
Select User,table_name,column_name from All_tab_columns;

--View the serial number in the Oracle database
SELECT * from User_sequences;

--All of the above objects can be queried by the following SQL statement
--Querying all of the user-generated Oracle objects
SELECT * from User_objects;

--View comments for all tables in the Oracle database
Select Table_name,comments from User_tab_comments;

--View comments for all columns in the Oracle database
Select Table_name,column_name,comments from User_col_comments;

--Annotations to table plus Oracle
COMMENT on table aa10 is ' system parameter table ';

--A note to the column plus Oracle
COMMENT on COLUMN aa10.aaa100 is ' parameter category ';

--View the properties of the column in the table, including the data type, whether it is not empty, etc.
DESC aa10;
--Through system tables, view the properties of columns in a table, including data types, whether they are not empty, etc.
SELECT table_name,column_id,column_name,data_type,data_length,data_precision,nullable
From User_tab_columns
Order BY table_name,column_id;

--View all table spaces

Selecttablespace_name,sum (bytes)/1024/1024 from Dba_data_files GROUP by Tablespace_name

--View unused table space size

Selecttablespace_name,sum (bytes)/1024/1024 from Dba_free_space group Bytablespace_name;

--View the size of the database space occupied by the tables and indexes in the database
SELECT * from User_segments;

--View the number of records in all tables
CREATE TABLE Table_count (table_name VARCHAR2 (m), columns number (20));
--through PB run the following statement, get the result set, execute the result set under PB, and finally submit
Select ' INSERT into table_count values (' | | table_name| | ', (SELECT COUNT (1) from ' | | table_name| |)); /'|| Comments from User_tab_comments;
--All the records in the Table_count are on the table.
SELECT * from Table_count;

4.6 How to view the current Oracle date default format

SELECT * from Nls_session_parameters;
SELECT * from Nls_instance_parameters;
SELECT * from Nls_database_parameters;
The Nls_time_format is the date format, which is session-level, instance-level, and database-level

5. How to create a database

method One : Use database configuration Assistant

Detailed steps See: http://blog.csdn.net/wolfofsiberian/article/details/39966549

method two : SQL command form, later after learning to add

6, Oracle SQL Plus is how to connect the database

(1) In the graphical interface mode, you need to indicate the connection descriptor in the host string.
If you are using the default installation, the connection descriptor is not specified, usually ORCL.
(2) at the command line, the format of the connection command is: Conn Username/password@connect_identifier
Username: User Name
Password: password
Connect_identifier: Connection Descriptor
If no connection descriptor is specified, it is connected to the database specified by the SYSTEM environment variable ORACLE_SID.
The database to connect to if no oracle_sid is set.
If you have two or more databases installed on your machine, you must add the @ Connection descriptor to the command.

7. How to access Oracle databases in advanced languages

See Java Operations Oracle Database: http://blog.csdn.net/wolfofsiberian/article/details/39803657

8, query SQL statements to perform error-corresponding help

Sql> host Oerr Ora 1
Oracle_home not set. Contact Oracle Support Services.

The appropriate help hints for errors are not currently available, solution see: http://blog.csdn.net/wolfofsiberian/article/details/39991017

Appendix

Oracle Online Manual: Http://docs.oracle.com/database/121/index.htm
References: http://www.cnblogs.com/tearer/archive/2012/12/13/2815601.html