Oracle Related issues

RPM-IVH http://download.fedoraproject.org/pub/epel/6/i386/epel-release-6-7.noarch.rpm
RPM-IVH http://download.fedoraproject.org/pub/epel/6/x86_64/epel-release-6-8.noarch.rpm
RPM--import/etc/pki/rpm-gpg/rpm-gpg-key-epel-6
Yum Install Rlwrap
Su-oracle
echo "Alias sqlplus= ' Rlwrap sqlplus '" >> ~/.bash_profile
SOURCE ~/.bash_profile
Sqlplus/as SYSDBA
Show user;
Alter user HR identified by ZXCASD account unlock;
Exit
Sqlplus HR/ZXCASD
Show user;
SELECT * from Session_privs;
Viewing system permissions and object permissions
SELECT * from Dba_sys_privs;
SELECT * from Dba_tab_privs;
Change your password without the DBA's permission, but use the old password to authenticate with the following statement
Alter user HR identified by 123456 replace ZXCASD;


Select table_name from User_tables;
Desc jobs
Set PageSize 200
SELECT * from Jobs;
DESC Locations
Set Linesize 200
SELECT * from locations;


Back up related tables first
According to the user guide and by the table guide,
You need to specify the table name in terms of the table.
The backup contents are:
All the data in a table
Data and structure of a table
According to the user, the mode object under the user is exported.

Exp help=y
Exp hr/123456
Answer a few questions

Take the problem to learn!!! The problems that arise here
1.exp-00091:exporting questionable statistics. What is it?
2. Export all the tables with exp. Above need to know the table name in advance, and then one export.
by Baidu Query,
The solution to issue 1 is as follows:

Specifies that the NLS_LANG environment variable for the Linux system is the character set of the database
1) Query the character set of the database (many methods use only one)
Sql> Select Userenv (' language ') from dual;

USERENV (' LANGUAGE ')
----------------------------------------------------
American_america. Zhs16gbk

2) Set Nls_lang environment variables for Linux operating system
[[Email protected] exp] $export Nls_lang=american_america. Zhs16gbk
The environment Variables for the window system are modified as follows:
C:\>set Nls_lang=american_america. We8iso8859p1

The solution to issue 2 is as follows:
Specific command parameters can be referenced
Exp help=y
1. EXP:
There are three main ways (full, user, table)
1. Complete:
EXP system/123456 buffer=64000 file=full.dmp full=y
If you want to perform a full export, you must have special permissions
2. User mode:
EXP hr/123456 buffer=64000 file=hr.dmp owner=hr
This allows all objects of user hr to be exported to a file.
3. Table mode:
EXP hr/123456 buffer=64000 file=hr-xue.dmp Tables=xue
This allows the User HR table Xue to be exported
2. IMP:
Three modes (full, user, table)
1. Complete:
IMP system/123456 buffer=64000 file=full.dmp full=y
2. User mode:
IMP hr/123456 buffer=64000 file=hr.dmp fromuser=hr touser=hr
This allows all objects of user hr to be imported into the file. You must specify the Fromuser, touser parameter so that data can be imported.
3. Table mode:
IMP hr/123456 buffer=64000 file=hr-xue.dmp Tables=xue
This allows the User HR table Xue to be imported. One problem is that if the table exists, the data cannot be imported, just add the Ignore=y parameter to it later.


The phenomenon of the problem:
exp-00026:conflicting modes specified
Exp-00000:export terminated unsuccessfully

The backup file is generated but has no content.
This problem is mainly the exp when there are conflicting parameters.
The more common:
1. Both owner and tables are specified
2. Both full and tables are specified
3. Also specify multiple owner and full
Owner means that the entire user or multiple users are exported and tables represents only the tables that are exported.
Either export only the tables in it or export the entire user. Otherwise, it will clash.



Delete and change the related rows or tables, and then use the backup data for recovery.
Sqlplus hr/123456
Set PageSize 200
Set Linesize 200
CREATE TABLE xue (ID integer,name varchar (25));
Insert into Xue values (1, ' Wang ');
Insert into Xue values (2, ' Liu ');
Commit

Delete a table
drop table Xue;
If there is a table with indexed relationships, use the following
DROP TABLE mytest cascade constraints;

Leaves the table structure, deleting one row or all of the data.
Delete from regions where region_id=5;
Delete from regions


Imp hr/123456
Answer a few questions

Take the problem to learn!!! The problems that arise here
1. The questions to be answered are somewhat unclear and need to be carefully checked.
2. Character set issues to be dealt with.
Search by Baidu, the results are as follows:



The above method is a logical backup that imports data into a temporary file.
can also be a physical backup method, will be oradata/below all the data copied to another safe place, also known as cold
The method can also be hot prepared.

There are two types of backup methods for Oracle databases. The first class is a physical backup, which implements a full database recovery, but the database must be run in the back-up mode (the business database runs in non-return mode) and requires a great deal of external storage devices such as a tape library, a second type of backup as a logical backup, a business database in this way, This method does not require that the database be run in the back-up mode, it is simple to backup, and can not require an external storage device.
　　
Database Logical Backup method
　　
The logical backup of an Oracle database is divided into three modes: Table backup, user backup, and full backup.
　　
Table mode
　　
Backs up the specified object (table) in a user mode. The business database typically uses this backup method.
　　
If you are backing up to a local file, use the following command:
　　
Exp ICDMAIN/ICD rows=y indexes=n compress=n buffer=65536
feedback=100000 volsize=0
File=exp_icdmain_csd_yyyymmdd.dmp
Log=exp_icdmain_csd_yyyymmdd.log
Tables=icdmain.commoninformation,icdmain.serviceinfo,icdmain.dealinfo
　　
If you are backing up to a tape device directly, use the following command:
Exp ICDMAIN/ICD rows=y indexes=n compress=n buffer=65536
feedback=100000 volsize=0
File=/dev/rmt0
Log=exp_icdmain_csd_yyyymmdd.log
Tables=icdmain.commoninformation,icdmain.serviceinfo,icdmain.dealinfo
　　
Note: In case of disk space permitting, you should back up to the local server before copying to tape. For speed reasons, try not to back up directly to the tape device.
　　
User mode
　　
Backs up all objects in a user mode. The business database typically uses this backup method.
If you are backing up to a local file, use the following command:
Exp ICDMAIN/ICD owner=icdmain rows=y indexes=n compress=n buffer=65536
feedback=100000 volsize=0
File=exp_icdmain_yyyymmdd.dmp
Log=exp_icdmain_yyyymmdd.log
If you are backing up to a tape device directly, use the following command:
Exp ICDMAIN/ICD owner=icdmain rows=y indexes=n compress=n buffer=65536
feedback=100000 volsize=0
File=/dev/rmt0
Log=exp_icdmain_yyyymmdd.log
Note: If the disk has space, it is recommended that you back up to disk and then copy to tape. If the amount of database data is small, this method can be used for backup.

Full mode



1, the database test is fully exported, user Name System Password Manager exported to D:\daochu.dmp
Exp System/[email protected] file=d:\daochu.dmp full=y
2. Export the system user in the database and the SYS user's table
Exp System/[email protected] file=d:\daochu.dmp owner= (System,sys)
3. Export the table table1 and table2 in the database
Exp System/[email protected] file=d:\daochu.dmp tables= (table1,table2)
4. Export the field filed1 in table table1 in the database with the data beginning with "00"
Exp System/[email protected] file=d:\daochu.dmp tables= (table1) query=\ "where filed1 like ' 0% ' \"
The above is a common export, for compression I do not care, with WinZip to the DMP file can be very good compression. But add compress=y to the upper order.



Delete all objects under a user
Oracle Delete all objects under the user's methods not everyone, the following is to introduce you two common Oracle Delete all objects under the method, I hope you learn Oracle Delete users can be helpful.

Method 1:
Drop user HR cascade;
To exit all HR user connections to execute correctly.
Drop tablespace USERS including CONTENTS;
You cannot delete the default persistent table space.

Create user HR profile default identified by 123456 default tablespace USERS temporary tablespace TEMP account unlock;

--Authorization
Grant DBA to HR;
Grant Connect,resource to HR;


Method 2:
Write stored procedure implementation

DECLARE
TYPE Name_list is TABLE of VARCHAR2 (40);
TYPE Type_list is TABLE of VARCHAR2 (20);

Tab_name name_list:=name_list ();
Tab_type type_list:=type_list ();

Sql_str VARCHAR2 (500);
BEGIN
SQL_STR: = ' Select Uo.object_name,uo.object_type from user_objects uo where Uo.object_type isn't in (' ' INDEX ' ', ' LOB ') Orde R by uo.object_type Desc ';
EXECUTE IMMEDIATE sql_str BULK COLLECT into Tab_name,tab_type;

For I in Tab_name. First: Tab_name. Last LOOP
Sql_str: = ' DROP ' | | Tab_type (i) | | "| | Tab_name (i);
EXECUTE IMMEDIATE Sql_str;
END LOOP;
END;

Sqlplus hr/123456
DROP TABLE jobs cascade constraints; There may be a problem with this direct deletion. So try to use the following steps to solve, because after looking at, did not understand the next step.
drop TABLE Jobs
ora-02449:unique/primary keys in table referenced by foreign keys
Error, use the following method to solve

1. Check which tables have foreign keys that reference the unique/primary key of the table you want to delete.
Select A.*
From User_constraints A, user_constraints B
where b.table_name = ' MYTEST '
and A.constraint_type = ' R '
and a.r_constraint_name = B.constraint_name;

2. Generate all the Create statements that reference the foreign key to delete the table.
Select ' Select Dbms_metadata.get_ddl (' Ref_constraint ', ' ' | |
A.constraint_name | | ') from DUAL; '
From User_constraints A, user_constraints B
WHERE b.table_name = ' JOBS '
and A.constraint_type = ' R '
and a.r_constraint_name = B.constraint_name;

Select ' Drop table ' | | table_name | | ' Cascade constraints ' SQL1 from User_tables;

3. Execute the SQL statement generated in step 2 to get the foreign key definition.
Set Long 3000
Set Linesize 3000
Set pagesize 300
Execute the statement generated by step 2.
4. Delete the table.
DROP TABLE mytest cascade constraints;
5. Delete and rebuild the table.
6. Create a FOREIGN key constraint that is deleted because the table was created.
Perform the SQL statement that you obtained in step 3.
7. Check whether the foreign keys are restored and the system is normal.

Oracle Related issues