Link for Downloading QC and QTP from HP
https://h10078.www1.hp.com/cda/hpdc/display/main/register.jsp
Miscellaneous
REGULAR EXPRESSIONS:
- REGEX_SUBSTR(string,pattern,position,whichoccurence,instructionofmatch) character string matched.
- REGEX_INSTR(string,pattern,position,whichoccurence,optional,instructionofmatch) optional = 1 position after match otherwise position of match.
- REGEX_REPLACE(string,pattern,replace,position,optional,instructionofmatch) pattern replaced.
- CHECK (REGEXP_LIKE (columnname, regexpression)).
- everything starts at 1.
- instructionofmatch c=case sensitive i=caseinsensitive n=. to match newline m=multiline x=ignore white spaces last info has precedence.
- [anyonecharacter] [^notanycharacter].
- [:graph:] = [A-Za-z0-9punctuation].
- + one or more *zero or more ? zero or one {n1} {n1,} {n1,n2}.
- (ab|bc|cd) where | operates like OR.
- backslash Infront to tell you intend a character.
- ^starts $ends.
- 1 the first captured expression.
SESSION CONTROL STATEMENTS:
- ALTER SESSION.
- SET ROLE.
SYSTEM CONTROL STATEMENTS:
- ALTER SYSTEM.
EMBEDDED SQL STATEMENTS:
- Any DDL, DML, TCL that is integrated into a 3GL.
MISCELLANEOUS:
- SYSTEM PRIVILEGES license to drive car and OBJECT PRIVILEGES license to drive particular car.
- CREATE USER username IDENTIFIED BY password.
- ALTER USER username IDENTIFIED BY password.
- DROP USER username.
- DROP USER username CASCADE.
- CONNECT username/password.
Tools
- QC (earlier mercury Test Director) is the standard repository. Also, Clear Quest, Bugzilla.
- WinRunner/QTP.
- LoadRunner.
Transaction Control Language
TRANSACTION CONTROL LANGUAGE:
- Session based.
- SAVEPOINT name.
- ROLLOVER WORK name.
- COMIMT WORK.
Models
- Collaborative Model.
- IV V Model (Independent Verification and Validation).
- Test Centre Model.
Is there a Build Verification Test or Build Acceptance Test?
Verification typically involves reviews and meetings to evaluate documents, plans, code, requirements, and specifications. This can be done with checklists, issues list, walkthroughs, and inspection meetings. Validation typically involves actual testing and takes place after verifications are completed.
Internationalization is the process of designing a software application so that it can be adapted to various languages and regions without engineering changes. Localization is the process of adapting internationalized software for a specific region or language by adding locale-specific components and translating text.
One method involves pseudo localization.
Salient points are
- Use larger sentences. usually twice in length – Pseudo localization.
- Use larger words to know how if they get cut off vertically – Pseudo localization.
- Characters of a target language renders.
- See if provision for different currency and date is available – Requires code change.
Data Control Language
- Role and System privileges can be granted together but not with object privileges.
DATA DICTIONARY:
- Metadata.
- DBA_TABLES All tables in DB.
- ALL_TABLES All tables to which current user has privileges.
- USER_TABLES All tables owned by user.
- For above three ((TABLE_NAME, STATUS, ROW_MOVEMENT, AVG_ROW_LEN), OWNER).
SYSTEM:
- GRANT privilege TO user option.
- REVOKE privileges FROM user.
- Use ANY for any user in privileges.
- WITH ADMIN OPTION that can give user to again grant WITH ADMIN OPTION.
- Revoking this does not cascade.
- GRANT ALL PRIVILEGES TO user.
- GRANT privileges To PUBLIC All.
OBJECTS:
- PUBLIC SYNONYMNS do not need access (since PUBLIC) Alias table it references need specific permission.
- Even if permitted has to use user.table for others.
- GRANT user PRIVILEGES ON table TO user WITH GRANT OPTION They can again grant.
- ALL PRIVILEGES can be shortened to ALL for objects.
- REVOKE privileges ON table FROM user cascades for objects.
- Only INSERT, UPDATE, and REFERENCES privileges can be granted at the column level. When granting INSERT at the column level, you must include all the not null columns in the row.
- USER_SYS_PRIVS (PRIVILEGE, ADMIN_OPTION) Privileges for your user.
- USER_TAB_PRIVS ON whom we have granted privileges.
- CONNECT for (session) typical generic user.
- RESOURCE for (create table) typical application developer.
- DBA for database administrator.
- CREATE ROLE rolename.
- GRANT privilege TO rolename.
- GRANT role TO user not cascaded.
- Role and privilege independent.
SAS 70 Type II Audit – Data Security
SAS (Statement on Auditing Standards)
- Compatibility testing – testing how well software performs in a particular hardware/software/operating system/network/etc. environment.
- Exploratory testing – often taken to mean a creative, informal software test that is not based on formal test plans or test cases; testers may be learning the software as they test it.
- Ad-hoc testing – similar to exploratory testing, but often taken to mean that the testers have significant understanding of the software before testing it.
- Context-driven testing – testing driven by an understanding of the environment, culture, and intended use of software. For example, the testing approach for life-critical medical equipment software would be completely different than that for a low-cost computer game.
- Comparison testing – comparing software weaknesses and strengths to competing products.
- Mutation testing – a method for determining if a set of test data or test cases is useful, by deliberately introducing various code changes (‘bugs’) and retesting with the original test data/cases to determine if the ‘bugs’ are detected. Proper implementation requires large computational resources.
Data Manipulation Language
DATA MANIPULATION LANGUAGE:
- SELECT.
- They have projection (columns), selection (rows) and joining capabilities.
- DISTINCT or UNIQUE or ALL.
- Pseudo columns ROWNUM (before ordering) ROWID (physical).
- Asterisk.
- Literals Number, Character of String, Date, Interval.
- INTERVAL ’24-3′ YEAR(2) TO MONTH, INTERVAL ’24’ MONTH, DAY TO SECONDS.
- Operators use same precedence rules as in maths.
- VIEWS are named SELECT statements.
- TABLES are stored in data dictionary.
- SYNONYMNS are aliases for other database objects.
- DESCRIBE command provides information about the columns of the table.
- WHERE IN, LIKE(_ 1 char, % zero or more char, IS NULL NOT NULL).
- NULL is not equal to NULL.
- Empty < Blank < Numeric and Operators < Upper < Lower.
- AND >(precedence) OR NOT before column.
- ORDER BY ASC(default) DESC NULL has highest priority.
- ORDER BY n means nth column is select.
- AS is an alias for column You can use space also.
- DAUL has one row and one column with value X in every database.
- INSERT.
- UPDATE.
- DELETE.
- FUNCTIONS.
- UPPRER(s1) LOWER(s1) INITCAP(s1).
- CONCAT(s1,s2) s1 || s2.
- LPAD(s1,n,s2) RPAD(s1,n,s2) Space default.
- LTRIM(s1,2) RTRIM(s1,s2) Space default.
- TRIM(triminfo trimchar FROM trimsource) ‘LEADING’, TRAILING’ or ‘BOTH’.
- LENGTH(s1).
- INSTR(s1,s2,pos,n) nth occurrence.
- SUBSTR(s1,pos,len).
- SOUNDEX(s1).
- ROUND(n,i).
- TRUNC(n,i).
- REMAINDER(n1,n2) uses FLOOR.
- MOD(n1,n2).
- SYSDATE TO_CHAR to display time.
- ROUND(d,i).
- TRUNC(d,i) i is ‘MM’.
- NEXTDAY(d,c) c is ‘Saturday’.
- LASTDAY(d) last day of month.
- ADD_MONTHS(d,n).
- MONTHS_BETWEEN(d1,d2) d1>d2 for positive value and it is not rounded.
- NUMTOYMINTERVAL(n,interval_period) intervalunit = ‘YEAR’ or ‘MONTH’ Returns INTERVAL YEAR TO MONTH.
- NUMTODSINTERVAL(n,interval_period) intervalunit = ‘DAY’, ‘HOUR’, ‘MINUTE’ or ‘SECOND’ Returns INTERVAL DAY TO SECOND.
- NVL(e1,e2) output numeric, varchar2 or NULL e2 is the value returned if e1 is null..
- DECODE( expression , search , result [, search , result]… [, default] ) default is returned or NULL (if no d) if not matched NULL=NULL in this case IF-THEN-ELSE.
- CASE expression1 WHEN condition1 THEN result1 ELSE resultfinal END.
- NULLIF(e1,e2) returns NULL if e1=e2.
- TO_NUMBER(e1,format_model,nls_params).
- TO_CHAR(c).
- TO_DATE(c, format_model,nls_params).
- TO_TIMESTAMP(c, format_model,nls_params).
- TO_DSINTERVAL(sql_format, nls_params).
- TO_YMINTERVAL(‘y-m’).
- CAST(e AS d) For casting.
- TO_TIMESTAMP.
- EXTRACT(fm FROM e) fm fm being ‘YEAR’, ‘MONTH’, ‘DAY’, ‘HOUR’, ‘MINUTE’, ‘SECOND’, ‘TIMEZONE_HOUR’, ‘TIMEZONE_MINUTE’, ‘TIMEZONE_REGION’, ‘TIMEZONE_ABBR’.
- SYS_EXTRACT_UTC(dtz).
- AT TIME ZONE DBTIMEZONE.
- AT TIME ZONE SESSIONTIMEZONE.
- AT LOCAL.
- AGGREGATE FUNCTIONS can be used at SELECT, ORDER BY, HAVING.
- COUNT(e1).
- SUM(e1).
- MIN(e1).
- MAX(e1).
- AVG(e1).
- MEDIAN(e1) Ignores NULL.
- RANK(c1) WITHIN GROUP (ORDER BY e1).
- DENSE_RANK If there is a tie still ordering is consecutive.
- aggregate_function KEEP (DENSE_RANK FIRST ORDER BY e1) OVER (PARTITION By columnname).
- aggregate_function KEEP (DENSE_RANK LAST ORDER BY e1) OVER (PARTITION By columnname).
- HAVING or GROUP BY can occur in either order.
- GROUP BY cannot have column names.
- FOREIGN KEY is PRIMARY KEY stored in another table.
- INNER JOIN and OUTER JOIN.
- EQUI-JOIN and NON EQUIJOIN.
- (INNER) JOIN ON otherwise, WHERE.
- LEFT (OUTER) JOIN RIGHT (OUTER) JOIN FULL (OUTER) JOIN Use + OUTER is optional.
- Use table name to differentiate.
- NATURAL JOIN by common column.
- USING uses common column value or if not NULL.
- ON columnname BETWEEN min AND max.
- CROSS JOIN cartesian product.
- SUB QUERIES.
- SINGLE ROW SUBQUERY.
- MULTI ROW SUBQUERY uses IN, operator ANY/SOME, operator ALL. The IN operator returns TRUE if the comparison value is contained in the list; in this case, the results of the subquery. The ANY and ALL operators work with the equal operators. The ANY operator returns TRUE if the comparison value matches any of the values in the list. The ALL operator returns TRUE only if the comparison value matches all the values in the list.
- MULTI COLUMN SUBQUERY (col1, col2) = (SELECT col1, col2 …).
- CORRELATED SUBQUERY in SELECT, UPDATE, DELETE.
- WHERE EXISTS (subquery) WHERE NOT EXISTS (subquery).
- WITH alias1 AS (subquery1), alias2 AS (subquery2) SELECT …. WITH query is temporary table or inline view.
- UNION Union with no duplicates.
- UNION ALL Union with duplicates.
- INTERSECT Intersection with duplicates.
- MINUS Additionally, if there are two identical rows in table_A, and that same row exists in table_B, BOTH rows from table_A will be removed from the result set.
- ORDER BY only in the end (outer select) in the above cases with reference to first select or position.
- GROUP BY ROLLUP (column1, column2) In addition to the regular aggregation results we expect from the GROUP BY clause, the ROLLUP extension produces group subtotals from right to left and a grand total. If “n” is the number of columns listed in the ROLLUP, there will be n+1 levels of subtotals.
- GROUP BY CUBE (column1, column2)In addition to the subtotals generated by the ROLLUP extension, the CUBE extension will generate subtotals for all combinations of the dimensions specified. If “n” is the number of columns listed in the CUBE, there will be 2n subtotal combinations.
- GROUP BY GROUPING SETS((column1, column2), column3, NULL).
- GROUPING informs whether NULL value is stored value or created by ROLLUP or CUBE.
- SELECT * FROM TABLE tablename WHERE … STARTS WITH columnname1 = … CONNECT BY PRIOR columnname1 = columnname2 AND … (PRIOR decides direction remember PRIOR PRIMARY KEY DOWNWARDS NEXT PRIMARY KEY IS FOREIGN KEY PRIOR FOREIGN KEY UPWARDS NEXT FOREIGN KEY IS PRIMARY KEY)
- SYS_CONNECT_BY_PATH(columnname, character).
- CONNECT BY ROOT columnname Any data from root node.
- Correlated subquery is a subquery that receives value from outer query table. Here outer query is executed first and each row used by subquery. This is opposite of nested query.
- CREATE TABLE tablename AS SELECT … –Here explicit NOT NULL are copied nothing else.
- INSERT INTO tablename (columnnames) SELECT (columnnames) … –Here on constraint violation everything fails.
- UPDATE tablename SET (columnnames) = (SELECT columnnames) …
- INSERT option INTO table1 VALUES (columnnames1) INTO table2 VALUES (columnnames2) subquery –If option is ALL it processes all of the INTO clauses.
- INSERT option WHEN expression INTO table1 VALUES (columnnames1) WHEN expression INTO table2 VALUES (columnnames2) subquery –If option is ALL it processes all of the INTO clauses.
- For above you can’t use sequence geneartors as well as you have to alias the columns with table alias in subquery to use in insert.
- MERGE INTO TABLE tablenname USING tablename or subquery ON condition WHEN MATCHED THEN UPDATE SET col = expression where_clause DELETE whereclause WHEN NOT MATCHED INSERT (column1, column2) VALUES, expression1, expression2) where_clause WHERE condition.
- SELECT * FROM TABLE AS OF TIMESTAMP timestamp_expression or SCN scn_expression.
- v$SYSTEM_PARAMETER(undo_retension) in seconds.
- SELECT * FROM TABLE VERSION BETWEEN timestamp_expression1 AND timestamp_exprssion2 If VERSION_STARTXXX is NULL then row version created before if VERSIONENDXXX is NULL then row version current or deleted.
- Combining above two VERSIONS BETWEEN AS OF TIME mentioned (order important).
- SELECT * FROM USER_RECYCLEBIN or SELECT * FROM RECYCLEBIN.
- CREATE TABLE … / ALTER TABLE … ENABLE ROW MOVEMENT.
- SCN_TO_TIMESTAMP(s1).
- TIMESTAMP_TO_SCN(t1).
- The Oracle user SYS owns all base tables and user-accessible views of the data.
- For these Oracle recommend creating temporary tables out of views, extracting required data and joining them. This helps overcome lack of read consistency. For this we query V$ views.
- ALL_TAB_COMMENTS Comments on all tabs There is also DB_TAB_COMMENTS, USER_TAB_COMMENTS All updated when comments added.
- ALL_COL_COMMENTS Comments on all columns There is also DB_COL_COMMENTS, USER_COL_COMMENTS All updated when comments added.
- COMMENT ON objecttype fullObjectName c1 — objecttype can be TABLE or COMMENT.
- DICTIONARY has TABLE_NAME and COMMENTS as columns.
- USER_CATALOG (TABLE_NAME, TABLE_TYPE).
- USER_OBJECTS (OBJECT_NAME, OBJECT_TYPE< STATUS) STATUS is INVALID for VIEWS (OBJECT_TYPE = VIEW).
- USER_CONSTRAINTS (CONSTRAINT_NAME, CONSTRAINT_TYPE, R_CONSTRAINT_NAME, STATUS) STATUS = ENABLED CONSTRAINT_TYPE = P / R (foreign key) / U / C.
- USER_TAB_COLUMNS (TABLE_NAME, COLUMN_NAME).
Different Types of Testing
- Black box testing – not based on any knowledge of internal design or code. Tests are based on requirements and functionality.
- White box testing – based on knowledge of the internal logic of an application’s code. Tests are based on coverage of code statements, branches, paths, conditions.
- Grey box testing – uses a combination of black box testing and white box testing.
- Unit testing – the most ‘micro’ scale of testing; to test particular functions or code modules. Typically done by the programmer and not by testers, as it requires detailed knowledge of the internal program design and code. Not always easily done unless the application has a well-designed architecture with tight code; may require developing test driver modules or test harnesses.
- Sanity testing or Smoke testing – typically an initial testing effort to determine if a new software version is performing well enough to accept it for a major testing effort. For example, if the new software is crashing systems every 5 minutes, bogging down systems to a crawl, or corrupting databases, the software may not be in a ‘sane’ enough condition to warrant further testing in its current state.
- Incremental integration testing – continuous testing of an application as new functionality is added; requires that various aspects of an application’s functionality be independent enough to work separately before all parts of the program are completed, or that test drivers be developed as needed; done by programmers or by testers.
- Integration testing – testing of combined parts of an application to determine if they function together correctly. The ‘parts’ can be code modules, individual applications, client and server applications on a network, etc. This type of testing is especially relevant to client/server and distributed systems.
- Functional testing – black-box type testing geared to functional requirements of an application; this type of testing should be done by testers. This doesn’t mean that the programmers shouldn’t check that their code works before releasing it (which of course applies to any stage of testing).
- System testing – black-box type testing that is based on overall requirements specifications; covers all combined parts of a system.
- End-to-end testing – similar to system testing; the ‘macro’ end of the test scale; involves testing of a complete application environment in a situation that mimics real-world use, such as interacting with a database, using network communications, or interacting with other hardware, applications, or systems if appropriate.
- Regression testing – re-testing after fixes or modifications of the software or its environment. It can be difficult to determine how much re-testing is needed, especially near the end of the development cycle. Automated testing approaches can be especially useful for this type of testing.
- Acceptance testing – final testing based on specifications of the end-user or customer, or based on use by end-users/customers over some limited period of time.
- Alpha testing – testing of an application when development is nearing completion; minor design changes may still be made as a result of such testing. Typically done by end-users or others, not by programmers or testers.
- Beta testing – testing when development and testing are essentially completed and final bugs and problems need to be found before final release. Typically done by end-users or others, not by programmers or testers.
- User acceptance testing – determining if software is satisfactory to an end-user or customer.
- Load testing – testing an application under heavy loads, such as testing of a web site under a range of loads to determine at what point the system’s response time degrades or fails.
- Stress testing – term often used interchangeably with ‘load’ and ‘performance’ testing. Also used to describe such tests as system functional testing while under unusually heavy loads, heavy repetition of certain actions or inputs, input of large numerical values, large complex queries to a database system, etc.
- Performance testing – term often used interchangeably with ‘stress’ and ‘load’ testing. Ideally ‘performance’ testing (and any other ‘type’ of testing) is defined in requirements documentation or QA or Test Plans.
- Load Testing =>Test under full load of users and transactions.
- Stress Testing => Double the load.
- Spike Testing =>Very sudden ramp up and ramp down in matter of minutes.
- Endurance Testing => Test over continuous periods of time. To check for memory leakages.
- Volume Testing => The database or interface file load is increased. Here we increase the data and keep the same number of users.
- Usability testing – testing for ‘user-friendliness’. Clearly this is subjective, and will depend on the targeted end-user or customer. User interviews, surveys, video recording of user sessions, and other techniques can be used. Programmers and testers are usually not appropriate as usability testers.
- Install/uninstall testing – testing of full, partial, or upgrade install/uninstall processes.
- Recovery testing – testing how well a system recovers from crashes, hardware failures, or other catastrophic problems.
- Failover testing – typically used interchangeably with ‘recovery testing’.
- Security testing – testing how well the system protects against unauthorized internal or external access, wilful damage, etc; may require sophisticated testing techniques.
- Application Security (Example: No hard coded username and password + Level of user Access).
- Physical Security (Example: Secure ODC-Access Card).
- Data Security (Example: Separate domain + Access Controlled Repository (SVN, VSS) + No external data transfer devices).
- Remote Desktop (Not Must).
- Client Security document information should be considered.
Data Definition Language
DATA DEFINITION LANGUAGE:
- DEFAULT dafaultvalue — To set default value.
- CONSTRAINT name_PK PRIMARY KEY (fieldname).
- CONSTRAINT name_FK FOREIGN KEY (fieldname).
- CONSTRAINT name_NN NOT NULL (fieldname) — ONLY INLINE.
- CONSTRAINT name_CK CHECK (fieldname IN(x,y)).
- CONSTRAINT name_UQ UNIQUE (fieldname).
- Implicit commit occurs after DDL even if it fails on even if rolled back.
- CREATE DATABASE.
- CREATE TABLE.
- ALTER TABLE ADD (columnname) MODIFY (columnname) (No need of brackets if single data) The existing data should not be altered by a change of properties Add DEFAULT value there are NULL values and we change to NOT NULL.
- ALTER TABLE tablename RENAME columnname1 TO columname2.
- ALTER TABLE DROP (columnname) (No need of brackets if single data) If it is a primary key or foreign key we need to add CASCADE CONSTRAINT.
- ALTER TABLE tablename SET UNUSED COLUMN columnname Similar to drop though it is retained. Better performance in heavy usage.
- ALTER TABLE ADD (CONTRAINT constraintname contrainttype (columnname) Not for NULL.
- NOT NULL has to be in line not out of line (not using ADD).
- ALTER TABLE MODIFY columnname CONTRAINT constraintname contrainttype.
- ALTER TABLE tablename DROP PRIMARY KEY CASCADE or KEEP INDEX or DROP INDEX.
- ALTER TABLE tablename DROP UNIQUE (col1, colo2, …).
- To DROP NULL use modify as NULL.
- ALTER TABLE DISABLE constrainttype constraintname — PRIMARY KEY UNIQUE CHECK FOREIGN KEY.
- ALTER TABLE MODIFY constrainttype constraintname DISABLE — PRIMARY KEY UNIQUE CHECK FOREIGN KEY.
- ENABLE VALIDATE same as ENABLE.
- ENABLE NOVALIDATE same as ENABLE without validation.
- DISABLE VALIDATE same as ENABLE Disables.
- DISABLE NOVALIDATE same as DISABLE.
- ENABLE ensures that all incoming data conforms to the constraint.
- DISABLE allows incoming data, regardless of whether it conforms to the constraint.
- VALIDATE ensures that existing data conforms to the constraint.
- NOVALIDATE means that some existing data may not conform to the constrain.
- DROP TABLE tablename CASCADE CONSTRAINTS.
- ALTER TABLE … ON DELETE CASCADE or SET NULL (more relations) for deleting rows with constraints.
- To use DEFERRED create constraint with DEFERRABLE in the end and then use.
- SET CONSTRAINT ALL or contraintname DEFFERED Reverse it with IMMEDIATE.
- ALTER TABLE RENAME contraintname1 TO constraintname2.
- CREATE TABLE tablename fieldname fielddatatype CONSTRAINT — INLINE.
- CREATE TABLE tablename fielddetails, ADD CONSTRAINT constraintname CONSTRAINTTYPE fieldname — EXTERNAL.
- ALTER TABLE tablename MODIFY fieldname CONSTRAINT constraintname CONSTRAINTTYPE — INLINE.
- ALTER TABLE tablename ADD CONSTRAINT constraintname CONSTRAINTTYPE fieldname — EXTERNAL.
- FOREIGN KEY (columnname) REFERENCES tablename(columnname).
- CREATE OR REPLACE VIEW viewname AS SELECT … WITH CHECK OPTION (Now with check option any insert or update operation that makes a record disappear from the view raises a trappable runtime error).
- Views provide security and abstraction.
- They are objects and satisfy underlying table constraints.
- INLINE VIEWS.
- ALTER VIEW viewname COMPILE.
- CREATE SEQUENCE sequence_name sequence_options INCREMENT BY integer STARTS BY integer MAXVALUE integer MINVALUE integer NOMAXVALUE NOMINVALUE CYCLE NOCYCLE.
- columnname.NEXTVAL columnname.CURRVAL.
- Can use within INSERT, SELECT not in WHERE.
- First call-in session must be NEXTVAL.
- Even if INSERT fails INCREMENT occurs.
- CREATE INDEX indexname ON tablename(columnname).
- INDEX work on =, <, >, LIKE string% not on functions (unless function based index) or on inequality or %string.
- Usually five indexes on a transaction-based table.
- Skip scanning is advantageous if there are few distinct values in the leading column of the composite index and many distinct values in the non-leading key of the index
- CREATE UNIQUE INDEX indexname ON tablename(columnname)
- DROP INDEX indexname
- CREATE OR REPPLACE SYNONYMN syynonymnname for tablename
- CREATE OR REPLACE PUBLIC SYNONYMN synonymnname for tablename Here created by USER PUBLIC Everyone can see but access to be provided.
- Consider A has created a table and B needs access We can give access by.
- GRANT SELECT ON tablename TO user.
- Access by user.tablename etc.
- Now using PUBLIC SYNONYMNS user.table can be replaced with user. If user changes, we need to alter only PUBLIC SYNONYMN.
- DROP SYNONYMN synonymnname.
- DROP PUBLIC SYNONYMN synonymnname.
- First namespace searched for is the schema followed by public.
- SYNONYMN used by itself is private.
- SELECT INDEX_NAME FROM USER_INDEXES WHERE TABLE_NAME = tablename.
- CREATE TABLE table_name PRIMARY KEY USING INDEX (CREATE INDEX indexname ON tablename(columnname) We can create index on PRIMARY KEY as well as UNIQUE.
- CREATE TABLE tablename … CONTRAINT indexname(columnnames) USING INDEX (CREATE INDEX indexname ON tablename(columnnames) or … tablename(expression) to create function-based indexes. Only computation result is considered mainly not order.
- FLASHBACK_TRANSACTION_QUERY (UNDO_SQL) UNDO_SQL the query that will undo but not necessarily the ROWID.
- FLASHBACK TABLE tablename TO BEFORE DROP.
- FLASHBACK TABLE tablename TO SCN scn_expression.
- FLASHBACK TABLE tablename TO TIMESTAMP timestamp_expression.
- FLASHBACK TABLE tablename TO RESTOREPOINT restore_point_expression.
- When flashedback all constraints except refereential integrity constraints are restored.
-
EXTERNAL TABLE CREATE TABLE statments ORGANIZATION EXTERNAL (TYPE ORACLE_LOADER -- ORACLE DATA_PUMP ACCESS PARAMETERS (RECORDS DELIMITED BY NEWLINE SKIP 2 FIELDS ( fieldname fieldtype) ) LOCATION 'filepath' )
- Cannot use LOB, Foreign key constraints, UNUSED Also no INSERT, UPDATE or DELETE.