Article From:https://www.cnblogs.com/watcsgg/p/9969489.html

1.Create tablespace

CREATE TEMPORARY TABLESPACE IBASE4JTEMP
         TEMPFILE ‘G:\app\hannasong\oradata\orcl\IBASE4JTEMP.DBF’
         SIZE 32M
         AUTOEXTEND ON
         NEXT 32M MAXSIZE UNLIMITED
         EXTENT MANAGEMENT LOCAL;
CREATE TABLESPACE IBASE4J
         LOGGING
         DATAFILE ‘G:\app\hannasong\oradata\orcl\IBASE4J.DBF’
         SIZE 32M
         AUTOEXTEND ON
         NEXT 32M MAXSIZE UNLIMITED
         EXTENT MANAGEMENT LOCAL;
 
2.Create users and authorize

create user sinosong identified by sinosong
default tablespace IBASE4J
temporary tablespace IBASE4JTEMP
profile DEFAULT;
— Grant/Revoke role privileges
grant connect to sinosong;
grant resource to sinosong;
–grant connect,resource,dba to sinosong;
— Grant/Revoke system privileges
grant create any sequence to sinosong;
grant create any view to sinosong;
grant create procedure to sinosong;
grant create table to sinosong;
grant create tablespace to sinosong;
grant create trigger to sinosong;
grant export full database to sinosong;
grant import full database to sinosong;
grant unlimited tablespace to sinosong;

 
3.Delete users and user objects
drop user sinosong cascade;
 
4.Solve the problem that Oracle 11g cannot export empty tables
select ‘alter table ‘||table_name||’ allocate extent;’ from user_tables where num_rows=0 or num_rows is null;
 
5.Field adjustment with data
For example, number – & gt; String
create table BIZ_RENTAL_FACTORING_KEY_2 as select * from BIZ_RENTAL_FACTORING_KEY ;
drop table…     create table…
insert into BIZ_RENTAL_FACTORING_KEY select * from BIZ_RENTAL_FACTORING_KEY_2;
If char – & gt; varchar cannot write *, it needs to take trim ().
 
6.Unlocking users
dbaThe user logs on to PLSQL and checks directly to cancel the lock.
Or execute through sql: alter user sinosong account unlock
 
7.Delete duplicate data and keep only one

DELETE FROM table_name a WHERE rowid > ( SELECT min(rowid) FROM table_name b WHERE b.id = a.id and b.name=a.name);
1、The redundant duplicate records in the lookup table are judged by a single field (Id).
select * from Table where Id in (select Id from table group by Id having count (Id) gt; 1)
2、Delete the redundant duplicate records in the table. The duplicate records are judged by a single field (Id), leaving only the smallest ROWID records.
DELETE from Table WHERE (id) IN (SELECT ID FROM table GROUP BY ID HAVING COUNT (id) gt; 1) AND ROWID NOT IN (SELECT MIN)(ROWID) FROM table GROUP BY ID HAVING COUNT (*) gt; 1;
3、Redundant duplicate records in lookup tables (multiple fields)
select * from Table a where (a.Id, a.seq) in (select Id, SEQ from table group by Id, SEQ having count (*) gt; 1)
4、Remove redundant duplicate records (multiple fields) from the table, leaving only records with the smallest ROWID
delete from Table a where (a.Id, a.seq) in (select Id, SEQ from table group by Id, SEQ having count (*) gt; 1) and ROWID noT in (select min (rowid) from table group by Id, SEQ having count (*) & gt; 1)
5、Redundant duplicate records (multiple fields) in the lookup table, excluding records with the smallest ROWID
select * from Table a where (a.Id, a.seq) in (select Id, SEQ from table group by Id, SEQ having count (*) gt; 1) and ROWID noT in (select min (rowid) from table group by Id, SEQ having count (*) & gt; 1)

8.Error deletion recovery of database
1、Data deletion
  Restore table
    1.Permission to open Flash storage
    ALTER TABLE tableName ENABLE row movement;
    2.Restore the table to a specified time point
    flashback table tableName to timestamp to_timestamp(‘2018-01-01 00:00:00′,’yyyy-mm-dd hh24:mi:ss’);–The latter parameter is the time point to restore
  Using Oracle snapshots to find data at a certain point in time, you can query data for a specified period of time.
    select * from tableName AS OF TIMESTAMP  (SYSTIMESTAMP – INTERVAL ‘100’ MINUTE)
    or
    select * from tableName as of timestamp to_timestamp(‘2018-01-01 00:00:00′,’yyyy-mm-dd hh24:mi:ss’);
 
2、Error deletion table
  select * from user_recyclebin;
  FLASHBACK TABLE TABLE_NAME TO BEFORE DROP;
  If you delete or modify the data in the table, you can first create a fast table to retrieve the data that deleted the status before the modification:
  CREATE TABLE QUICK_TABLE AS SELECT * FROM TABLE_NAME AS OF TIMESTAMP SYSDATE-1/24;(1/24An hour ago, 5/1440 is 5 minutes ago, or a specified date (‘2018-01-01 00:00′,’yyyyy-mm-dd hh24:mi:ss’)
 
9.Password expiration
alter user userXXX identified by xxx;
If you want to set a password that does not expire, you can log in with an administrator and then execute:
 ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;
 
10.Data import and export
expdp、impdpMethod (This method creates users, can export empty tables, or can specify table spaces)
  create directory TOUCHAN_DUMP as ‘G:/app/sinosong/dpdmp’;
  expdp ‘sys/”orcl as sysdba”‘ directory=touchan_dump file=TESTDP.dmp schemas=eximbank
  Transform users and table spaces on import
  impdp “‘sys/oracle as sysdba'” directory=touchan_dump file=TOUCHAN.20181107.DMP remap_schema=sinosong:eximtcyl remap_tablespace=IBASE4J:EXIMTCYL_DATA table_exists_action=replace
 
exp、impmode

  imp user/password file=/home/oracle/TEST.dmp fromuser=user touser=user

  exp ‘user/password’ file=G:/app/sinosong/dpdmp/TEST.dmp

  If the password has special symbols, such as user/p@ssw0rd, it can be written as exp’user/”p@ssw0rd”‘file=…

  Note that data exported by DBA users can only be imported by DBA users.

  For more detailed export, execute exp help = y to view all parameters

 
For more, please go to the original author’s link:
False deletion recovery = = “
https://blog.csdn.net/shiyu1157758655/article/details/79578009
User password expiration==”
http://www.blogjava.net/freeman1984/archive/2013/04/23/398301.html
This essay is only for notes. If infringement occurs, please reply and I will clean it up.
 
Link of this Article: Oracle Common Operations Classes

Leave a Reply

Your email address will not be published. Required fields are marked *