The create in tablespace NK_DATA
Datafile 'D:/oracle/product/10.2.0 oradata/former/NK_DATA1 def' size
1000 mAutoextend on next 1000 m maxsize unlimited logging
Among management, local autoallocate
The segment space management, auto;
Build user
The create user UNIREPORT
Identified by "UNIREPORT
"The default in tablespace NK_DATA
Temporary in tablespace FINTEMP
Profile DEFAULT;
Authorization
Grant connect to UNIREPORT;
Grant dba to UNIREPORT;
Grant the resource to UNIREPORT;
Grant, unlimited in tablespace to UNIREPORT;
Export
Exp SZ/SZ @ fin file=d: \ SZ DMP log=d: \ SZ log grants=y
Import
Imp FIN/FIN @ former file=F: \ FIN DMP log=F: \ FIN DMP. Log fromuser=FIN touser=FIN buffer=4096000 feedback=100000
The new table
The create table aaa (
Jl_hh NUMBER (20) not null, the default - 1
Name VARCHAR2 (20),
LSH VARCHAR2 (100)
)
The new table field
The alter table aaa add KK_BBB varchar (100);
Modify the table name
The alter table old_table_name rename to new_table_name;
Index
The create index IDX_BB_ZZ_SJZZ_HH on BB_ZZ (SJZZ_HH)
In tablespace FIN_TABLE
Pctfree 10
Initrans 2
Maxtrans 255
Storage
(
Initial 64 k
Minextents 1
The maxextents unlimited
);
Query
Select * from tablename;
Through the equivalent join
The select z.n ame, y.p osition, y.s core from TableX x inner join TableY y on x.i d=y.i d
Inner join TableZ z on x.d epartment=z.i d
Outer join
The select x.N ame, y.P osistion, y.S cored from TableX x left join TableY y ON x.I D=y.I D
Basic function
The sum, distinct, Decode, NVL, case, with, intersects, minus, substr, having
DECODE (value, if1, then1 if2, then2, if3, then3,... The else)
In sum, sum
Distinct to heavy
NVL, such as NVL (fields, 'x') field value is equal to null
Case, case the when t=1 then 'male' else 'female' end
With in meaning between the two values
Intersects returns the intersection of two input set, can choose to keep duplicates, grammar intersects (? Characters? ,? Set2? [ALL])
Minus, difference set
Substr substr (STR, pos, len);
This means, that is, starting from the pos, intercepting len characters (blank also calculate character),
Having having with the result of the arithmetic sum or other collection function to limit the output of the
With the=sign in
Can use=try not to use, in large amount of data, in speed is slow, for example (a=5 or a=6) and (5, 6) in a
SQLLDR:
Import large quantities of data using SQLLDR quickly, need to even have a file, a converted file
For/r C: \ SQLLDR % % a (*. CSV) do in type "% % a & gt;"> % % ~ dpanewcsv. TXT
A control file
The LOAD DATA
INFILE 'c: \ SQLLDR \ newcsv TXT'
APPEND INTO TABLE aaa
FIELDS TERMINATED BY ', 'OPTIONALLY ENCLOSED BY' "'
(TJR TJSJ, JL_HH)
Pay attention to the converted newcsv. TXT file, must be greater than or equal to the columns of the control file,
Set up the view
The create or replace the view v_aaa as
The select a. L_HH, a.n ame from aaa a;
Set up the counter
The create sequence SEQ_aaa
Minvalue 0
Maxvalue 99999999
Start with 1970540
Increment by 1
Cache 20
Cycle;
Synonyms
The create or replace synonym aaa_1 for FIN. Aaa; Under the same instance, even the user can build directly, but need to authorization;
No instance need to first establish a dblink;
CodePudding user response:
Thank you for your sharing, and learning!
CodePudding user response:
Thank you for your sharing, and learning!
CodePudding user response:
Access control is not very good
DBA shouldn't have to, and the resource and the DBA role, will give unlimited implied in tablespace system permissions, so the last sentence grant extra, but this sentence to revoke, for the current user quota on their default table space betterCodePudding user response:
Exp, most of the options are open, so grants=y is redundantCodePudding user response:
And three even that in and the or, in fact is not only the two, a lot of SQL to rewrite the optimize didn't your imagination, and are not widely available, SQL optimization are many factors that influence, more often has a lot to do with your data distribution, few can absolutely suitable for all scenariosCodePudding user response:
Good things, which master of the mysql under the summary!CodePudding user response:
That's fine, like a record in a blog post is better,CodePudding user response:
Thank you for your sharing!