Oracle Basic Knowledge

1. View the Oracle configuration information (service_name, SID, Tnsname) in an AIX environment.

Sid:

Echo $ORACLE _sid

SERVICE_NAME:

Sqlplus/as Sysdba;
Show parameter instance_name;

Show parameter service_names;

Show parameter service;

Select Instance_namefrom v$instance;

Tnsname:

NETCA view;

Tnsnames.ora View: Open Tnsnames.ora file under Network/admin:

Find. –name "Tnsnames.ora";

Cat Tnsnames.ora

2. Log in to the database when no TNS is configured.

sqlplus user/[email protected]:port/servicename

sqlplususer/[email protected]

sqlplususer/pwd---aix

sqlplus/nolog>connect user/pwd

sqlplus/ as sysdba

Format One:jdbc:oracle:thin:@//Format Two:jdbc:oracle:thin:@
Format three:jdbc:oracle:thin:@< tnsname >

3. Quickly copy a table and create an index here.

duplicating table structures and their data

CREATE TABLE Table_name_new as SELECT * from Table_name_old

Duplicate table structure only

CREATE TABLE Table_name_new as SELECT * from Table_name_oldwhere 1=2

Copy only table data

INSERT INTO Table_name_new select * from Table_name_old

Table structure is not the same

Insert INTO Table_name_new (Column1,column2 ...) selectcolumn1,column2 ... from Table_name_old

Copy table structure and its data not write log

CREATE TABLE Table_name_new nologging as SELECT * Fromtable_name_old

Setting the degree of parallelism

CREATE TABLE testa2 (t1 int) parallel;

Commit

Inserting data without writing a log

ALTER TABLE table_name nologging;

Modify the Write log again

ALTER TABLE table_name LOGGING;

Degree of parallelism:

When you view the Dba_tables data dictionary, you can find the "degree" field, which represents the degree of parallelism of the data table. The setting of this parameter is related to the I/O of the database and the efficiency of SQL execution. When the table is set to a very high degree of parallelism, the SQL optimizer may perform a full table scan of the table, causing Direct Path Read to wait.

Careful consideration is needed before using parallel queries, as parallel queries can increase the response time of the program, but

Consume more resources.

ALTER TABLE T parallel (degree 1);------directly specify the degree of parallelism of the table

ALTER TABLE t parallel; ----------set the parallelism of the table to default

To create an index:

Create [Unique]index index_name on table_name (Column_name[,column_name ...])

4. Import and export of oracle11g database.

Export:

All:

Exp Imagesys/[email protected] file=/icms/20170116.dmp full=y

User:

Exp Imagesys/imagesys @orcl file=/icms/20170116.dmp Owner=imagesys

Exp System File=/icms/lims2017116.dmp Log=/icms/icms.logowner=imagesys

Table:

Exp Imagesys/[email protected] file=/icms/lims2017116.dmptables= (table1,table2)

Export an empty table SQL statement

Select ' ALTER TABLE ' | | table_name| | ' allocate extent; ' fromuser_tables where segment_created= ' NO '

Import:
All:

Imp imagesys/[email protected] buffer=64000 file=/icms/lims2017116.dmpfull=y

User:
Imp imagesys/[email protected] file=/icms/lims2017116.dmp Fromuser=imagesys Touser=imagesys

5, the Command Line table query results output to TXT

Spool E:\log.txt;

Select Id,name from Users;

Spool off;

6, AIX, win write script to connect the database and output the query results to a text file.

Bat:

Sqlplus Arch/[email protected] @e:\runsql\log.sql>log1.txt

Sh:

Sqlplus Imagesys/imagesys @log. sql>log1.txt

Oracle Basic Knowledge