Oracle Operations Basics, rewind, backup, recovery, and optimization.

Oracle Basic Operations
CREATE TABLE Space
Create tablespace test//The table space name we created here is called Test, and the name can be customized

LOGGING

DataFile '/data/ora01/app/oracle/oradata/cms/test.dbf '//This is the table space data file storage place, it is recommended to put under the Oracle installation directory Oradata

Size 128M//table space Initial Size

Autoextend on//Table space Auto-grow on Open

NEXT 128M MAXSIZE 30G//Table space grows 128M each time, up to 30g,oracle single table space maximum support to 32G

EXTENT MANAGEMENT LOCAL

SEGMENT spacemanagement AUTO;

Create a user and point to tablespace, authorization
Create user test identified by 123456 default tablespace test;//Creating a users test with a password of 123456 and a table space of test

Grant DBA to test; Give the test user DBA permission

View Data backup directory

SELECT * from Dba_directories;

Exporting data
EXPDP test/123456 directory=data_pump_dir schemas=test dumpfile=test20180606. Dmpdp

According to the user export data, export test user's all data, where the user is test,test the table space of the data is test, when importing data to use the
Import data
The following statement indicates that the data exported from the test user is poured into the user best,

IMPDP best/123456 Directory=data_pump_dir dumpfile=g6a0220180308. DMPDP remap_schema=test:best remap_tablespace=test:best transform=oid:n

Import parameter Explanation:
Directory: followed by a database path, generally in the form of a variable with SQL can be viewed: select * from Dba_directories;

Data_pump_dir: Is the default variable for the Oracle database and represents a path, in the Oracle installation directory under oracle_home/admin/orcl/dpdump/, the data pump exported data files and the data files to be imported are generally placed in this directory

DumpFile: The name of the data file to import

Remap_schema: Define source user and target user to import, data file is test user export, source user is test user, import to target user best

Remap_tablespace: Define the source user tablespace and the table space to be imported to the target user, the data file is test user export, test table space is test, import to the target User best table space best, in other circumstances as long as the corresponding can, otherwise the import will error.

TRANSFORM=OID:N: This parameter ignores type error.

EXP Import Export
Exp Import Export is not explained in detail and the data pump is similar, the advantage is that you can import and export remotely, the disadvantage is that import and export are particularly slow

Exp G6a02/ninestar123[email Protected]:1521/nsdev FILE=/G6A0220180120.DMPOWNER=G6A02

Imp g6a02/[email protected] file= '/u01/software/expdat.dmp ' log= '/software/nstcsa27335dmp.log ' commit=y fromuser= G6A02 TOUSER=G6A02

Resolve type Error: Transform=oid:n when importing

Fix version issue: version=10.2.0.1.0 export, for example, from ORACLE12C exported data imported to oracle11g to add the version parameter, otherwise it will prompt for incompatible versions, but import from 11g data exported to 12c without this parameter Oracle database high version compatible low version

Unlock User: ALTER user cib_bank_sc account UNLOCK;

Query default tablespace: Select Username,default_tablespace from Dba_users;

Modify the Oracle user password: ALTER user SYS identified by Ninestar123;

Oracle failure, error file deletion: Alter DATABASE datafile '/u01/app/oracle/oradata/g6.ora ' offlinedrop; ALTER DATABASE datafile '/u01/app/oracle/oradata/g6a02.ora ' offline drop;

Query database for lock table: SELECT * FROM Fw_lockresource R where R.isfree = 0;

Delete Oracle User: Drop user ees2 cascade;

Delete Table space: Drop tablespace ees2 including contents and datafiles cascade constraint;

Modify user default tablespace: Alter users ees2 default Tablespace ees2;

Database optimization: ALTER profile DEFAULT LIMIT password_life_time UNLIMITED;

       ALTER PROFILE DEFAULT LIMIT FAILED_LOGIN_ATTEMPTS UNLIMITED;       ALTER SYSTEM SET PROCESSES=1000 SCOPE=SPFILE;修改最大连接数       ALTER SYSTEM SETOPEN_CURSORS=1000 SCOPE=BOTH;       ALTER SYSTEM SETDB_FILES=300 SCOPE=SPFILE;

Query database current number of connections: SELECT COUNT (*) from v$session;

Query database Concurrent connections: Select count (*) from v$session where status= ' ACTIVE ';

Maximum number of connections allowed for database: Select value from v$parameter where name = ' processes ';

Query maximum connection: show parameter processes;

View the number of connections for different users: Select Username,count (username) from V$session where username are not null GROUP by username;

If you are interested in this, please scan the QR code below for free for more details

Oracle Operational Basic operations, reverse library, backup, recovery, and optimization.