#get ddl of materialized view in oracle

[oraclerider]

How to Generate table DDL

in this practice we are going to learn how to generate table DDL, view DDL, Materialized View DDL and user DDL.

Hi, in this practice we are going to learn how to generate table DDL, view DDL, Materialized View DDL and user DDL. You also read below articles: DB LINK DDL GET TABLESPACE DDLGET ROLE DDL Get more oracle scritps How do you get DDL of a table in Oracle? In real time environment some times we need to DDL of an existing table. We can perfo this activity with the help of dbms_metadata.get_ddl…

[fql-us]

Fauna Query Language for SQL users | Fauna Documentation

Fauna Query Language for SQL users This section describes a number of common structured query language (SQL) queries and their Fauna Query Language (FQL) equivalents. While it is almost impossible to provide an exhaustive comparison of every variation of all SQL commands, we provide a very basic comparison of the most used DDL (Data Definition Language) and DML (Data Manipulation Language) queries. As you gain more experience with the Fauna Query language, the ease and power of its syntax should become evident. Complex queries that are difficult, or even impossible, in SQL can be composed very easily in FQL. One very important difference between the two is that FQL is not a declarative language as SQL. Hence the actual path of execution needs to be provided for each query — FQL requires the developer to specify an index in most queries. Conceptual equivalents Relational FaunaDB Schema Database Table Class Row Instance Index/Materialized Views Index In these examples below, we use two tables, dept (departments) and emp (Employees) to compare basic operations in both query languages. These tables have been extensively used in Oracle documentation to explain various SQL basics. SQL> DESC emp Name Null? Type ----------------------------------------- -------- EMPNO NOT NULL NUMBER(4) ENAME VARCHAR2(10) JOB VARCHAR2(9) MGR NUMBER(4) HIREDATE DATE SAL NUMBER(7,2) COMM NUMBER(7,2) DEPTNO NUMBER(2) SQL> DESC dept Name Null? Type ----------------------------------------- -------- DEPTNO NOT NULL NUMBER(2) DNAME VARCHAR2(14) LOC VARCHAR2(13) ZIP NUMBER Data definition language CREATE DATABASE In some relational databases, like MySQL, a database can be created with: CREATE DATABASE employees; CREATE DATABASE Fauna is a multi-tenant database and databases can be created like a nested tree. CreateDatabase({name: "employees"}) CREATE TABLE CREATE TABLE dept( deptno NUMBER(2,0), dname VARCHAR2(14), loc VARCHAR2(13), CONSTRAINT pk_dept PRIMARY KEY (deptno) ) CREATE CLASS CreateClass({name: "dept"}); FaunaDB doesn’t enforce the structure of a class at the time of creation. However, if we know that every instance of this class should have a deptno field, we can create a unique index on the deptno field which emulates a relational database’s primary key. CreateIndex({ name: "dept_by_deptno", source: Class("dept"), terms: [{ field: [ "data", "deptno" ] }], unique: true }) ALTER TABLE ADD COLUMN ALTER TABLE dept ADD (zip NUMBER); No direct correlation As instances do not have a predefined schema, there is no straightforward equivalent to adding a term (equivalent to a column) to all instances without any values. The Fauna equivalent would be to run Update on the instance. Update( Ref(Class("dept"), "224507299921658368"), { data: { zip: 10001 } } ) TRUNCATE TABLE In SQL, truncate removes all records, but preserves the structure of the table. TRUNCATE TABLE dept; DELETE INSTANCES In FQL, the equivalent would be to delete all records from the table. Map( Paginate( Match(Index("all_depts")) ), Lambda("X", Delete(Var("X"))) ) The all_depts index is a class index that indexes the entire class. CreateIndex({ name: "all_depts", source: Class("dept") }) DROP TABLE DROP TABLE dept; DELETE CLASSES and INDEXES The Delete command can be used to remove tables and indexes. Unlike in SQL, dropping a table doesn’t remove the underlying indexes automatically. The indexes need to be removed manually. Delete a Class Delete(Class("dept")) Delete an Index Delete(Index("all_depts")) Data manipulation language INSERT RECORD INSERT INTO dept (deptno, dname, loc) VALUES (10, "ACCOUNTING", "NEW YORK"); CREATE INSTANCE Create( Class("dept"), { data: { "deptno": 10, "dname": "ACCOUNTING", "loc": "NEW YORK" } } ) UPDATE UPDATE dept SET loc = "AUSTIN" WHERE deptno = 10; UPDATE Update( Select("ref", Get( Match(Index("dept_by_deptno"), 10) ) ), { data: { loc: "AUSTIN" } } ) Running the Replace command on an entire document is also a form of Update. This is similar to a Delete followed by an Insert. Replace( Ref(Class("dept"), "224572974137606656"), { data: { "deptno": 10, "dname": "ACCOUNTING", "loc": "AUSTIN" } } ) DELETE DELETE FROM dept WHERE deptno = 10; DELETE You can use the reference as the key to delete a specific record. Delete( Ref(Class("dept"), "224532222499095041") ) Alternatively, you can delete a record based on a specific index column. Delete( Select( "ref", Get( Match(Index("dept_by_deptno"), 10) ) ) ) Query SELECT: ALL ROWS SELECT * FROM dept; GET ALL INSTANCES Just like in relational databases, selecting all instances from a class results in a full scan. In SQL, the server automatically selects the appropriate indexes based on the specified columns. In FaunaDB, indexes must be specified explicitly. You need a class index to run a full scan: CreateIndex({ name: "all_depts", source: Class("dept") }) Once the index is in place, run the query below. Map( Paginate( Match(Index("all_depts")) ), Lambda("X", Get(Var("X"))) ) SELECT: Based on a single Parameter SELECT * FROM dept WHERE deptno = 10; GET: Based on a single Parameter We can use the unique index we created earlier to enforce the primary key. Map( Paginate( Match(Index("dept_by_deptno"), 10) ), Lambda("X", Get(Var("X"))) ) SELECT: Based on a single Parameter with a NOT SELECT * FROM dept WHERE deptno != 10; GET: Based on a single Parameter with a NOT Unlike SQL, we create this list as a difference between two indexes, the class index and the unique index on the deptno. Map( Paginate( Difference( Match(Index("all_depts")), Match(Index("dept_by_deptno"), 10) ) ), Lambda("x", Get(Var("x"))) ) SELECT: Based on a condition SELECT * FROM emp WHERE sal > 2000 GET: Based on a condition In order to accomplish this, we need an index on the sal term along with the Refs that point to the location of each instance. CreateIndex({ name: "emp_by_sal", source: Class("emp"), values: [ {field: ["data", "sal"]}, {field: ["ref"]} ], }) After the index has built, we can get the results with: Map( Paginate( Match(Index("emp_by_sal")), { after: 2000 } ), Lambda("x", Get(Select(1, Var("x")))) ) Observe the Lambda function. The Select command gets the Ref fields, and then passes them to the Get command. Alternatively, we could have used Lambda(["sal", "ref"], Get(Var("ref"))) as we know the index returns two different values. SELECT: GROUP BY Query to select the maximum salary by department SELECT MAX(sal), deptno FROM emp GROUP BY deptno; GET: Grouped instances FQL can accomplish such queries using two indexes, the first on deptno and the second on deptno and salary. CreateIndex({ name: "emp_by_deptno", source: Class("emp"), values: [{ field: ["data","deptno"] }] }) CreateIndex({ name: "deptno_by_sal", source: Class("emp"), terms: [{ field: ["data","deptno"] }], values: [{ field: ["data","sal"] }] }) The second index deptno_by_sal stores the values sorted by sal within each deptno group. Since Get() returns the first element that matches the index, it returns the maximum value of sal. Map( Paginate( Distinct( Match(Index("emp_by_deptno")) ) ), gid => Get( Match(Index("deptno_by_sal"), Gid) ) ) EQUI-JOIN two tables SELECT e.* FROM emp e, dept d WHERE e.deptno = d.deptno AND d.dname = "SALES"; GET instances joined by two classes We need two indexes to accomplish this join in FQL. When we JOIN two classes, the value of one index is joined to the term of another index. Index #1 (Class: Dept, Term: dname, Value: deptno) CreateIndex({ name: "dept_by_name", source: Class("dept"), terms: [{ field: ["data", "dname"] }], values: [{ field: ["data","deptno"] }] }) Index #2 (Class: emp, Term: deptno) CreateIndex({ name: "emp_by_deptno", source: Class("emp"), terms: [{ field: ["data","deptno"] }] }) Query Map( Paginate( Join( Match(Index("dept_by_name"), "SALES"), Index("emp_by_deptno") ) ), Lambda("X", Get(Var("X"))) ) Stay tuned for another set of examples in the near future. If you are looking for a specific example, feel free to email me at deba@fauna.com.

[stokedevwebsite]

DBA Interview Questions with Answer Part 20‎

What is Checkpoint SCN and Checkpoint Count? How we can check it?Checkpoint is an event when the database writer is going to flush the dirty buffers into the datafiles. This an ongoing activity and in the result checkpoint number constantly incremented in the datafile header and controfile and the background process CKPT take care of this responsibility.How can you find length of Username and Password?You can find the length of username with below query. The password is hashed (#) so there is no way to get their length.You can use special characters ($, #, _) without single quotes and any other characters must be enclosed in single quotation.Select length (username), usernamefrom dba_users;The minimum length for password is at least 1 character where as maximum depends on database version. In 10g it is restricted to 17 characters long.What are the restrictions applicable while creating view?– A view can be created referencing tables and views only in the current database.– A view name must not be the same as any table owned by that user.– You can build view on other view and on procedure that references views.For More information you can click on the below link: Common Interview Question & AnswerWhat is difference between Delete/Drop/Truncate?DELETE is a command that only removes data from the table. It is DML statement. Deleted data can be rollback (when you delete all the data get copied into rollback first then deleted). We can use where condition with delete to delete particular data from the table.Where as DROP commands remove the table from data dictionary. This is DDL statement. We cannot recover the table before oracle 10g, but flashback feature of oracle 10g provides the facility to recover the drop table.While TRUNCATE is a DDL command that delete data as well as freed the storage held by this table. This free space can be used by this table or some other table again. This is faster because it performs the deleted operation directly (without copying the data into rollback).Alternatively you can enable the row movement for that table and can use shrink command while using the delete command.SQL> Create table test     (      Number s1, Number s2     );SQL> Select bytes, blocks from user_segments     where segment_name = ‘test’;Bytes       block----------  -------65536       8SQL> insert into t select level, level*3     From dual connect by level  Select bytes, blocks from user_segments     where segment_name = ‘test’;Bytes       block----------  -------131072      16SQL> Delete from test;3000 rows deleted.SQL> select bytes,blocks from user_segments     where segment_name = 'test';Bytes       block----------  -------131072      16SQL> Alter table t enable row movement;SQL> Alter table t shrink space;Table alteredSQL> Select bytes,blocks from user_segments      where segment_name = 'test';Bytes       block----------  -------65536       8What is difference between Varchar and Varchar2?Varchar2 can store upto 4000 bytes where as Varchar can only store upto 2000 bytes. Varchar2 can occupy space for NULL values where as Varchar2 will not specify any space for NULL values.What is difference between Char and Varchar2?A CHAR values have fixed length. They are padded with space characters to match the specified length where as VARCHAR2 values have a variable length. They are not padded with any characters. In which Language oracle has been developed?Oracle is RDBMS package developed using C language.What is difference between Translate and Replace?Translate is used for character by character substitution where as Replace is used to substitute a single character with a word.What is the fastest query method to fetch data from table?Using ROWID is the fastest method to fetch data from table.What is Oracle database Background processes specific to RAC?LCK0—Instance Enqueue Process LMS—Global Cache Service Process LMD—Global Enqueue Service Daemon LMON—Global Enqueue Service Monitor Oracle RAC instances use two processes, the Global Cache Service (GCS) and the Global Enqueue Service (GES) to ensure that each oracle RAC database instance obtain the block that it needs to satisfy as query or transaction. The GCS and GES maintain records of the statuses of each data file and each cached block using a Global Resource Directory (GRD). The GRD contents are distributed across all of the active instances.What is SCAN in respect of oracle RAC?Single client access name (SCAN) is a new oracle real application clusters (RAC) 11g releases 2 features that provides a single name for client to access an oracle database running in a cluster. The benefit is clients using SCAN do not need to change if you add or remove nodes in the clusters.Why do we have a virtual IP (VIP) in oracle RAC?Without VIP when a node fails the client wait for the timeout before getting error where as with VIP when a node fails, the VIP associated with it is automatically failed over to some other node and new node re-arps the world indicating a new MAC address for the IP. Subsequent packets sent to the VIP go to the new node, which will send error RST packets back to the clients. This results in the clients getting errors immediately.Why query fails sometimes?Rollback segments dynamically extent to handle large transactions entry loads. A single transaction may occupy all available free space in rollback segment tablespace. This situation prevents other user using rollback segments. You can monitor the rollback segment status by querying DBA_ROLLBACK_SEGS view.What is ADPATCH and OPATCH utility? Can you use both in Application?ADPATCH is a utility to apply application patch and OPATCH is a utility to apply database patch. You have to use both in application for applying in application you have to use ADPATCH and for applying in database you have to use OPATCH.What is Automatic refresh of Materialized view and how you will find last refresh time of Materialized view?Since oracle 10g complete refresh of materialized view can be done with deleted instead of truncate. To force the instance to do the refresh with truncate instead of deleted, parameter AUTOMIC_REFRESH must be set to FALSEWhen it is FALSE Mview will be faster and no UNDO will be generated and whole data will be inserted.When it is TRUE Mview will be slower and UNDO will be generated and whole data will be inserted. Thus we will have access of all time even while it is being refreshed.If you want to find when the last refresh has taken place. You can query with these view: dba_mviews or dba_mview_analysis or dba_mview_refresh_times SQL> select MVIEW_NAME, to_char(LAST_REFRESH_DATE,’YYYY-MM-DD HH24:MI:SS’) from dba_mviews; -or- SQL> select NAME, to_char(LAST_REFRESH,’YYYY-MM-DD HH24:MI:SS’) from dba_mview_refresh_times; -or- SQL> select MVIEW_NAME, to_char(LAST_REFRESH_DATE,’YYYY-MM-DD HH24:MI:SS’) from dba_mview_analysis;Why more archivelogs are generated, when database is begin backup mode?During begin backup mode datafiles headers get freezed so row information can not be retrieved as a result the entire block is copied to redo logs thus more redo log generated or more log switch occur in turn more archivelogs. Normally only deltas (change vector) are logged to the redo logs.The main reason is to overcome the fractured block. A fractured block is a block in which the header and footer are not consistent at a given SCN. In a user-managed backup, an operating system utility can back up a datafile at the same time that DBWR is updating the file. It is possible for the operating system utility to read a block in a half-updated state, so that the block that is copied to the backup media is updated in its first half, while the second half contains older data. In this case, the block is fractured.For non-RMAN backups, the ALTER TABLESPACE ... BEGIN BACKUP or ALTER DATABASE BEGIN BACKUP when a tablespace is in backup mode, and a change is made to a data block, the database logs a copy of the entire block image before the change so that the database can re-construct this block if media recovery finds that this block was fractured.The block that the operating system reads can be split, that is, the top of the block is written at one point in time while the bottom of the block is written at another point in time. If you restore a file containing a fractured block and Oracle reads the block, then the block is considered a corrupt.Why is UNION ALL faster than UNION?UNION ALL faster than a UNION because UNION ALL will not eliminate the duplicate rows from the base tables instead it access all rows from all tables according to your query where as the UNION command is simply used to select related distinct information from base tables like JOIN command.Thus if you know that all the records of your query returns the unique records then always use UNION ALL instead of UNION. It will give you faster results.How will you find your instance is started with Spfile and Pfile?You can query with V$spparameter viewSQL> Select isspecified, count(*) from v$spparameter     Group by isspecified;ISSPEC   COUNT(*)------   ----------FALSE    221TRUE     39As isspecified is TRUE with some count we can say that instance is running with spfile. Now try to start your database with pfile and run the previous query again.SQL> Select isspecified, count(*) from v$spparameter     Group by isspecified;ISSPEC COUNT(*)------ ----------FALSE  258Then you will not find any parameter isspecified in spfile they all come from pfile thus you can say instance is started with pfile.Alternatively you can use the below querySQL> show parameter spfile;SQL> Select decode(count(*), 1, 'spfile', 'pfile' )     from v$spparameter     where rownum=1 and isspecified='TRUE';Why we need to enable Maintenance Mode?To ensure optimal performance and reduce downtime during patching sessions, enabling this feature shuts down the Workflow Business Events System and sets up function security so that Oracle Applications functions are unavailable to users. This provides a clear separation between normal run time operation and system downtime for patching..