Shulou(Shulou.com)06/01 Report--

Build the library manually:

Find the official document and open the main index

Find the management-- create database

Stop the existing database and free up memory

1. Set the environment variable-- ORACLE_SID

two。 Create a password file

3. Create a parameter file for the text

4. Create a binary parameter file

5. Establish the path described in the parameter file

6. Start the database to nomount

7.create database...

8. Run the script: @ $ORACLE_HOME/rdbms/admin/catalog to create a data dictionary

@ $ORACLE_HOME/rdbms/admin/catproc to create the package of the application

9. Set up a sample scheme

Conn system/a@$ORACLE_HOME/sqlplus/admin/pupbldbr/ > @ $ORACLE_HOME/sqlplus/admin/pupbld

@ $ORACLE_HOME/rdbms/admin/utlsampl.sql

Open the official document

Supporting Documentation

Administratior's Guide

2 creating and configuring an Oracle Database

Item 3: Creating a Database with the CREATE DATABASE Statement

Step 9: Issue the CREATE DATABASE Statement

1. Shut down all other databases.

two。 Create a password file for a new database (yy)

ORACLE user execution

Cd $ORACLE_HOME/dbs

Rapwd file=orapwyy password=a

3. Create a parameter file for the text

Strings spfileqq.ora (find an existing parameter file, paste and modify the contents)

Modify the corresponding database name and file path.

For more information, please see step 3 of this document.

4. Set the environment variable: ORACLE_SID=yy

Export ORACLE_SID=yy

5. Create a binary parameter file

Conn / as sysdba

Create spfile from pfile

6. Establish the path described in the parameter file

Cd / u01/app/oracle/admin

Cp-r qq yy goes to this directory and renames qq directory replication to yy

Set up a data file storage directory

Cd / u01/app/oracle/oradata

Mkdir yy

7. Modify the manual database building script of the official document, change the database name, path, password, etc.

For details, please see step 7 below.

8. Start the database to nomount, and log to the $ORACLE_BASE/admin/ instance name / adump directory during the startup of the database to nomount.

Startup

Report 205 error

Run the modified create database script in step 7 to create the database.

After the execution of the script, the database is created, and a naked library is built with no data dictionary.

9. Run the script: @ $ORACLE_HOME/rdbms/admin/catalog to create a data dictionary

@ $ORACLE_HOME/rdbms/admin/catproc to create the package of the application

10. Create a sample scenario

SYS user execution

Alter user system identified by a

Conn system/a@$ORACLE_HOME/sqlplus/admin/pupbldbr/ > @ $ORACLE_HOME/sqlplus/admin/pupbld

@ $ORACLE_HOME/rdbms/admin/utlsampl.sql

Details of step 3:

.audit _ file_dest='/u01/app/oracle/admin/yy/adump'

.audit _ trail='db'

. compatible='11.2.0.0.0'

.control _ files='/u01/app/oracle/oradata/yy/control01.ctl','/u01/app/oracle/oradata/yy/control02.ctl'

.db _ block_size=8192

.db _ domain=''

.db _ name='yy'

.diagnostic _ dest='/u01/app/oracle'

.open _ cursors=300

.pga _ aggregate_target=308281344

.salary = 150

.remote _ login_passwordfile='EXCLUSIVE'

.sga _ target=924844032

.undo _ tablespace='UNDOTBS1'

Step 7 details:

CREATE DATABASE yy

USER SYS IDENTIFIED BY a

USER SYSTEM IDENTIFIED BY a

LOGFILE GROUP 1 ('/ u01 SIZE) SIZE 20m

GROUP 2 ('/ u01 SIZE) SIZE 20m

GROUP 3 ('/ u01 SIZE) SIZE 20m

CHARACTER SET US7ASCII

NATIONAL CHARACTER SET AL16UTF16

EXTENT MANAGEMENT LOCAL

DATAFILE'/ u01 SIZE REUSE

SYSAUX DATAFILE'/ u01 SIZE SYSAUX DATAFILE'/ u01 REUSE

DEFAULT TABLESPACE users

DATAFILE'/ u01qqapp.oracle.oradataUniplicationyusers01.dbf'.

SIZE 500M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED

DEFAULT TEMPORARY TABLESPACE tempts1

TEMPFILE'/ u01qqapp.oracle.oradata.temp01.dbf'

SIZE 20M REUSE

UNDO TABLESPACE undotbs1

DATAFILE'/ u01qapplash oracle.oradataUniplicateyOnOnDotbs01.dbf'

SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED

Experiment 2: master the three stages of database startup

Delete the parameter files of the yy database and move the control files, data files and log files of the yy database to another location.

Original directory $ORACLE_BASE/oradata/yy new directory: $ORACLE_BASE/oradata/tt

1. View database location

Select name from v$datafile

Union all

Select name from v$controlfile

Union all

Select name from v$tempfile

Union all

Select member from v$logfile

Path: / u01/app/oracle/oradata/tt/

two。 Shut down the database

Shutdown immediate

3. Delete the parameter files of the database, and move the data files, log files, and control files of the database to other directories.

Cd $ORACLE_HOME/dbs

Rm-rf inityy.ora

Rm-rf spfileyy.ora

Cd $ORACLE_BASE/oradata

Mv yy tt

4. Specify the instance name and start the database

Startup

Error: ORA-01078 cannot find parameter file

Create a new parameter file based on the log file under / u01/app/oracle/diag/rdbms/yy/yy/trace.

Cd $ORACLE_HOME/dbs

Vi inityy.ora pastes the following part of the log file you just saw into the text and saves and exits.

Processes = 150

Sga_target = 884m

Control_files = "/ u01/app/oracle/oradata/yy/control01.ctl"

Control_files = "/ u01/app/oracle/oradata/yy/control02.ctl"

Db_block_size = 8192

Compatible = "11.2.0.0.0"

Undo_tablespace = "UNDOTBS1"

Remote_login_passwordfile= "EXCLUSIVE"

Db_domain = ""

Audit_file_dest = "/ u01/app/oracle/admin/yy/adump"

Audit_trail = "DB"

Db_name = "yy"

Open_cursors = 300

Pga_aggregate_target = 294m

Diagnostic_dest = "/ u01/app/oracle"

Note: this step states that parameter files are required during the database startup to numount phase.

5. After creating the text parameter file in step 4, connect to the database as SYS, create the binary parameter file, and start the database.

Export ORACLE_SID=yy

Sqlplus / as sysdba

Create spfile from pfile

Startup

Error: ORA-00205 control file error

View the status of the database: the database is in the STARTED state.

This step explains: when starting to the nomount phase, as long as the parameter file is correct.

In the mount database, you need to use the control file.

6. Modify the location of the control file in the parameter file:

Alter system set control_files='/u01/app/oracle/oradata/tt/control01.ctl'

'/ u01App scope=spfile to oracle, oradata, to TTX control 02.ctl`

7. After the previous modification is completed, close the database and start it again.

Shutdown abort

Startup nomount; succeeded

Alter database mount; succeeded

Alter database opne; reported an error

Error: ORA-01157 cannot find the data file because the data file has been moved to another path.

8. Modify the database and re-specify the location of the data files in the database: (all data files and log files should be executed, which can be completed by writing scripts)

Alter database rename file'/ u01qqappActionoradata 'Undotbs01.dbf'

To'/u01/app/oracle/oradata/yy/undotbs01.dbf'

9. Open the database after successfully reassigning the path

Alter database open

Dump directory / u01/app/oracle/diag/rdbms/yy/yy/trace

Modify parameter control_files= new path

Database mount started successfully open error report

Modify data files, temporary files, data files

/ u01/app/oracle/oradata/rr/control01.ctl

/ u01/app/oracle/oradata/rr/control01.ctl

Select 'alter database rename file''| | name | |'| | chr (10) | | 'to''' | |

Replace (name,'tt','yy') | |''; 'from v$datafile

Union all

Select 'alter database rename file''| | name | |'| | chr (10) | | 'to''' | |

Replace (name,'tt','yy') | |''; 'from v$tempfile

Union all

Select 'alter database rename file''| | member | |'| | chr (10) | | 'to''' | |

Replace (member,'tt','yy') | |''; 'from v$logfile

Select 'alter database rename file''| | name | |'| | chr (10) | |'to''| |

'/ newdb' | | substr (name,instr (name,'/',-1)) | |'; 'from v$datafile

Segment segment

Range extend

Allocated once, contiguous, ORACLE block

A paragraph has at least one range, and can have many ranges.

There are three modes of range

1. When an object is created, what is allocated is called the initial scope

two。 As the data grows, allocate additional ranges

3. Manually allocate the range

Conn / as sysdba

Grant select any dictionary to scott

Conn scott/tiger

Select from dba_free_space where tablespace_name='USERS'

2.create table t1 as select from emp

Create table t2 as select from emp

3.select extent_id,file_id,block_id,blocks from dba_extents

Where segment_name='T1'

4.insert into t1 select from t1

Insert into T2 select * from T2

Select extent_id,file_id,block_id,blocks from dba_extents

Where segment_name='T1'

Select extent_id,file_id,block_id,blocks from dba_extents

Where segment_name='T2'

Manually allocate space:

Alter table T1 allocate extent; does not specify a size, and the system allocates it based on the size already in the table.

Alter table T1 allocate extent (size 2G); specify the size

Block block

SET LONG 1000

SQL > SELECT DBMS_METADATA.GET_DDL ('TABLE','T2') FROM DUAL

CREATE TABLE T1 SEGMENT CREATION IMMEDIATE AS SELECT * FROM EMP where 0x9

SHOW PARAMETER SEGMENT

ALTER SYSTEM SET deferred_segment_creation=FALSE

Range recovery 3 mode

1. Manually recycle unused areas (brand new)

Alter table T1 allocate extent (size 10m)

Alter table t1 deallocate unused

Tabs=user_tables

Select table_name,initial_extent from tabs where table_name='T2'

Create table T2 storage (initial 1m) as select * from emp

2.truncate table T1; (keep initial range)

3.drop table T1 purge; (completely delete space)

Alter table T2 allocate extent (size 10m)

Select extent_id,file_id,block_id,blocks from dba_extents

Where segment_name='T2'

DELETE

1.DML

two。 Generate a large number of fallbacks

3. Generate a large number of logs

4. Can rollback

5. Manual commit

6. Do not recycle space

7. Do not recycle high water level

8. Can be deleted conditionally

TRUNCATE

1.DDL

two。 Generate less fallback

3. Generate a small number of logs

4. No, rollback.

5. Automatic commit

6. Recycle space

7. Recovery of high water level

8. Cannot be deleted conditionally

Show parameter rec

Alter system set recyclebin=off scope=spfile

Block:

The basic space for database storage. The default is 8K, which is specified when the database is built.

Rowid line identification

Is calculated based on the location where the data exists.

18-bit 64-bit string

AAASdN AAE AAAACr AAA

Object file block line

Select rowid,ename,dbms_rowid.rowid_block_number (rowid) block# from emp

ROWID_BLOCK_NUMBER

ROWID_OBJECT

ROWID_RELATIVE_FNO

ROWID_ROW_NUMBER

Select rowid,ename,dbms_rowid.rowid_block_number (rowid) a

Dbms_rowid.ROWID_OBJECT (rowid) o

Dbms_rowid.ROWID_RELATIVE_FNO (rowid) f

Dbms_rowid.ROWID_ROW_NUMBER (rowid) r from emp

Select dbms_rowid.rowid_create (0pr 75335pr 4pr 151je 0)

Dbms_rowid.rowid_create (1, 753, 35, 4, 151, 0) from dual

Conn scott/123456

Drop table t1 purge

Create table t1 as select from emp

Insert into t1 select from emp where deptno=10

Commit

Select ename,sal from t1 order by 1

Delete from t1 wai where rowid

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.