a_oracle بتاريخ: 27 مارس 2008 تقديم بلاغ مشاركة بتاريخ: 27 مارس 2008 السلام عليكم ورحمه الله وبركاتهانا دخلت مقابله و اتلقيت اكثر من 30 سؤال ... و معايا منهم 27 سؤال ... لو تفضل احد الاخوه الخبراء بالاجابه عليهم باختصاروجزاه الله عنا كل خير 1-List all initialization parameter related to the following and how we can specify then: Sga , archive information, audit2- what are archive redo logs are used for? And what is the difference between them and the offline redo logs3- what is difference between locally managed and dictionary managed tablespace?4- list at least two types .0d what they are used for?5- list types of integrity constraints and where we can create them ? give example6- how can you control user password management ? give example7- what is the most important log file used by oracle instance it contains?8- write a sql statement to check the free space in each tablespace9- write a sql statement to display db block size10- write a oracle commend to open db for maintenance11- write a sql statement to create new log group12- write a sql statement to mirror a log file member13- write a sql statement to to create a locally managed tablespace14- write oracle commend convert oracle db to operate in archive log mode15- what is listener and what it is used for16- how can we configure a listener, and which file is used to configure on server client side?17- list all steps needed to tune a db?18- Which db system view contains memory allocation for a specific user session19- What is the difference between uga and pga20- what is the equation to calculate db hit ratio?21- what is the difference between logical and physical backups?22- how can we recover a db operating in archive log mode?23- how can we recover a db operating in no archive log mode?24- write export commend that will export full db?25- write export commend that will import emp table of user scott into user john from a dump file taken with full option?26- write a sql statement to display db character set?27- write a sql statement to create new user with all essential privileges for development user اقتباس رابط هذا التعليق شارك المزيد من خيارات المشاركة
noury_d بتاريخ: 27 مارس 2008 تقديم بلاغ مشاركة بتاريخ: 27 مارس 2008 أخي الكريم من اجل انشاء مستخدم من اجل السماحيات الكاملة و هو جواب السؤال 27 CREATE USER user_name IDENTIFIED BY passwordGRANT ROLE dba to user_nameولقد تم أعطائه هذه السماحية DBA من أجل أن يكون له السماحيات الشاملة مو هدا هو المطلوب من السؤال رقم 27 اقتباس رابط هذا التعليق شارك المزيد من خيارات المشاركة
a_oracle بتاريخ: 28 مارس 2008 كاتب الموضوع تقديم بلاغ مشاركة بتاريخ: 28 مارس 2008 السلام عليكمشكرا لك اخي noury_dوارجو من الاخوه االخبراء الاجابه علي باقي الاسئله لكي تفيد الجميع اقتباس رابط هذا التعليق شارك المزيد من خيارات المشاركة
a_oracle بتاريخ: 29 مارس 2008 كاتب الموضوع تقديم بلاغ مشاركة بتاريخ: 29 مارس 2008 ?????????????????????? :angry: :angry: اقتباس رابط هذا التعليق شارك المزيد من خيارات المشاركة
amrw2006 بتاريخ: 1 أبريل 2008 تقديم بلاغ مشاركة بتاريخ: 1 أبريل 2008 List all initialization parameter related to the following and how we can specify them:ًWe can know the parameter by running this commandsql>show parameter <word>EX: sql>show parameter sga Sga : sql_trace sga_max_sizesga_target lock_sga pre_page_sga archive information: remote_archive_enable standby_archive_dest archive_lag_target log_archive_config log_archive_destlog_archive_dest_nlog_archive_dest_state_nlog_archive_duplex_destlog_archive_formatlog_archive_local_firstlog_archive_max_processeslog_archive_min_succeed_destlog_archive_trace audit: audit_file_destaudit_sys_operationsaudit_trail audit_file_destaudit_sys_operationsaudit_syslog_level transaction_auditing 2- what are archive redo logs are used for? And what is the difference between them and the offline redo logsEach database contains one or more rollback segments. A rollback segment records the old values of data that was changed by each transaction (whether or not committed). Rollback segments are used to provide read consistency, to roll back transactions, and to recover the database.The Redo Log - The redo log, present for every Oracle database, records all changes made in an Oracle database. The redo log of a database consists of at least two redo log files that are separate from the datafiles (which actually store a database's data). As part of database recovery from an instance or media failure, Oracle applies the appropriate changes in the database's redo log to the datafiles, which updates database data to the instant that the failure occurred.A database's redo log can consist of two parts: the online redo log and the archived redo log.A) The Online Redo Log : Every Oracle database has an associated online redo log. The Oracle background process LGWR uses the online redo log to immediately record all changes made through the associated instance. The online redo log consists of two or more pre-allocated files that are reused in a circular fashion to record ongoing database changes. The Archived (Offline) Redo Log : Optionally, you can configure an Oracle database to archive files of the online redo log once they fill. The online redo log files that are archived are uniquely identified and make up the archived redo log. By archiving filled online redo log files, older redo log information is preserved for operations such as media recovery, while the pre-allocated online redo log files continue to be reused to store the most current database changes. Datafiles that were restored from backup, or were not closed by a clean database shutdown, may not be completely up to date. These datafiles must be updated by applying the changes in the archived and/or online redologs. This process is called recovery.Reference Oracle Docs at the following URL >http://tahiti.oracle.com/pls/tahiti/tahiti.homepage3- what is difference between locally managed and dictionary managed tablespace?When Oracle allocates space to a segment (like a table or index), a group of contiguous free blocks, called an extent, is added to the segment. Metadata regarding extent allocation and unallocated extents are either stored in the data dictionary, or in the tablespace itself. Tablespaces that record extent allocation in the dictionary, are called dictionary managed tablespaces, and tablespaces that record extent allocation in the tablespace header, are called locally managed tablespaces.SQL> select tablespace_name, extent_management, allocation_type from dba_tablespaces;TABLESPACE_NAME EXTENT_MAN ALLOCATIO------------------------------ ---------- ---------SYSTEM DICTIONARY USERSYS_UNDOTS LOCAL SYSTEMTEMP LOCAL UNIFORMDictionary Managed Tablespaces (DMT):Oracle use the data dictionary (tables in the SYS schema) to track allocated and free extents for tablespaces that is in "dictionary managed" mode. Free space is recorded in the SYS.FET$ table, and used space in the SYS.UET$ table. Whenever space is required in one of these tablespaces, the ST (space transaction) enqueue latch must be obtained to do inserts and deletes agianst these tables. As only one process can acquire the ST enque at a given time, this often lead to contention.Execute the following statement to create a dictionary managedtablespace:SQL> CREATE TABLESPACE ts1 DATAFILE '/oradata/ts1_01.dbf' SIZE 50M EXTENT MANAGEMENT DICTIONARY DEFAULT STORAGE ( INITIAL 50K NEXT 50K MINEXTENTS 2 MAXEXTENTS 50 PCTINCREASE 0);Locally Managed Tablespaces (LMT):Using LMT, each tablespace manages it's own free and used space within a bitmap structure stored in one of the tablespace's data files. Each bit corresponds to a database block or group of blocks. Execute one of the following statements to create a locally managedtablespace:SQL> CREATE TABLESPACE ts2 DATAFILE '/oradata/ts2_01.dbf' SIZE 50M EXTENT MANAGEMENT LOCAL AUTOALLOCATE;SQL> CREATE TABLESPACE ts3 DATAFILE '/oradata/ts3_01.dbf' SIZE 50M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K;Note the difference between AUTOALLOCATE and UNIFORM SIZE:AUTOALLOCATE specifies that extent sizes are system managed. Oracle will choose "optimal" next extent sizes starting with 64KB. As the segment grows larger extent sizes will increase to 1MB, 8MB, and eventually to 64MB. This is the recommended option for a low or unmanaged environment.UNIFORM specifies that the tablespace is managed with uniform extents of SIZE bytes (use K or M to specify the extent size in kilobytes or megabytes). The default size is 1M. The uniform extent size of a locally managed tablespace cannot be overridden when a schema object, such as a table or an index, is created.Also not, if you specify, LOCAL, you cannot specify DEFAULT STORAGE, MINIMUM EXTENT or TEMPORARY.Advantages of Locally Managed Tablespaces: * Eliminates the need for recursive SQL operations against the data dictionary (UET$ and FET$ tables) * Reduce contention on data dictionary tables (single ST enqueue) * Locally managed tablespaces eliminate the need to periodically coalesce free space (automatically tracks adjacent free space) * Changes to the extent bitmaps do not generate rollback informationLocally Managed SYSTEM Tablespace:From Oracle9i release 9.2 one can change the SYSTEM tablespace to locally managed. Further, if you create a database with DBCA (Database Configuration Assistant), it will have a locally managed SYSTEM tablespace by default. The following restrictions apply: * No dictionary-managed tablespace in the database can be READ WRITE. * You cannot create new dictionary managed tablespaces * You cannot convert any dictionary managed tablespaces to localThus, it is best only to convert the SYSTEM tablespace to LMT afterall other tablespaces are migrated to LMT.Segment Space Management in LMT:From Oracle 9i, one can not only have bitmap managed tablespaces, but also bitmap managed segments when setting Segment Space Management to AUTO for a tablespace. Look at this example:SQL> CREATE TABLESPACE ts4 DATAFILE '/oradata/ts4_01.dbf' SIZE 50M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;Segment Space Management eliminates the need to specify and tune the PCTUSED, FREELISTS, and FREELISTS GROUPS storage parameters for schema objects. The Automatic Segment Space Management feature improves the performance of concurrent DML operations significantly since different parts of the bitmap can be used simultaneously eliminating serialization for free space lookups against the FREELSITS. This is of particular importance when using RAC, or if "buffer busy waits" are deteted.Convert between LMT and DMT:The DBMS_SPACE_ADMIN package allows DBAs to quickly and easilyconvert between LMT and DMT mode. Look at these examples:SQL> exec dbms_space_admin.Tablespace_Migrate_TO_Local('ts1');PL/SQL procedure successfully completed.SQL> exec dbms_space_admin.Tablespace_Migrate_FROM_Local('ts2');PL/SQL procedure successfully completed.4- list at least two types .0d what they are used for?5- list types of integrity constraints and where we can create them ? give exampleIntegrity Constraints [Oracle] Data integrity allows to define certain data quality requirements that the data in the database needs to meet. If a user tries to insert data that doesn't meet these requirements, Oracle will not allow so.Constraint typesThere are five integrity constraints in Oracle.Not NullA column in a table can be specified not null. It's not possible to insert a null in such a column. The default is null. So, in the following create table statement, a null can be inserted into the column named c.create table ri_not_null ( a number not null, b number null, c number);insert into ri_not_null values ( 1, null, null);insert into ri_not_null values ( 2, 3, 4);insert into ri_not_null values (null, 5, 6);The first to records can be inserted, the third cannot, throwing a ORA-01400: cannot insert NULL into ("RENE"."RI_NOT_NULL"."A").The not null/null constraint can be altered withalter table ri_not_null modify a null;After this modification, the column a can contain null values.Unique KeyThe unique constraint doesn't allow duplicate values in a column. If the unique constraint encompasses two or more columns, no two equal combinations are allowed.create table ri_unique ( a number unique, b number);However, if a column is not explicitely defined as not null, nulls can be inserted multiple times:insert into ri_unique values (4, 5);insert into ri_unique values (2, 1);insert into ri_unique values (9, 8);insert into ri_unique values (6, 9);insert into ri_unique values (null,9);insert into ri_unique values (null,9);Now: trying to insert the number 2 again into a:insert into ri_unique values (2,7);This statement issues a ORA-00001: unique constraint (RENE.SYS_C001463 violated). Every constraint, by the way, has a name. In this case, the name is: RENE.SYS_C001463.In order to remove that constraint, an alter table ... drop constraint ... is needed:alter table ri_unique drop constraint sys_c001463;Of course, it is also possible to add a unique constraint on an existing table:alter table ri_unique add constraint uq_ri_b unique (;A unique constraint can be extended over multiple columns:create table ri_3 ( a number, b number, c number, unique (a,);It is possible to name the constraint. The following example creates a unique constraint on the columns a and b and names the constraint uq_ri_3.create table ri_3 ( a number, b number, c number, constraint uq_ri_3 unique (a,);Primary KeyOn a technical level, a primary key combines a unique and a not null constraint. Additionally, a table can have at most one primary key. After creating a primary key, it can be referenced by a foreign key.create table ri_primary_key ( a number primary key, b number);Primary keys can explicitely be named. The following create table statement creates a table with a primary key whose name is pk_name.create table ri_primary_key_1 ( a number, b number, c number, constraint pk_name primary key (a, );Foreign KeyA foreign key constraint (also called referential integrity constraint) on a column ensures that the value in that column is found in the primary key of another table.If a table has a foreign key that references a table, that referenced table can be dropped with a drop table .. cascade constraints.It is not possible to establish a foreign key on a global temporary table. If tried, Oracle issues a ORA-14455: attempt to create referential integrity constraint on temporary table.CheckA check constraint allows to state a minimum requirement for the value in a column. If more complicated requirements are desired, an insert trigger must be used.The following table allows only numbers that are between 0 and 100 in the column a;create table ri_check_1 ( a number check (a between 0 and 100), b number);Check constraints can be added after a table had been created:alter table ri_check_1 add constraint ch_b check (b > 50);It is also possible to state a check constraint that check the value of more than one column. The following example makes sure that the value of begin_ is smaller than the value of end_.create table ri_check_2 begin_ number, end_ number, value_ number, check (begin_ < end_));Disabling ConstraintsDisabling 'anonymous' constraintcreate table foo (bar number, baz number, unique (bar, baz));alter table foo disable unique (bar, baz);Disabling named constraintcreate table foo (bar number, baz number, constraint uq_foo unique (bar, baz));alter table foo disable constraint uq_foo;6- how can you control user password management ? give exampleMost Oracle database users create user accounts with the default profile. Since Oracle 8, it's possible to lock an account by creating a profile and assigning it to a user with either of these two statements:CREATE USER myuser . . . PROFILE myprofile;ALTER USER myuser PROFILE myprofile;A typical attempt to break into a database account is to try several commonly used passwords, such as "welcome" or the username. You can prevent multiple failed attempts at logging in by using the profile tag FAILED_LOGIN_ATTEMPTS:CREATE PROFILE myprofile LIMIT FAILED_LOGIN_ATTEMPTS 5 PASSWORD_LOCK_TIME 1;Users assigned to this profile will be locked out of their accounts after five login attempts with an incorrect password. The account will be inaccessible for one day or until a DBA issues the command ALTER USER <username> ACCOUNT UNLOCK.Even after several years, I've found that my old password still works on previous projects. This makes a good case for placing a limit on a password's lifetime so it will expire after a certain period (e.g., at the end of a contract). There's also an option to allow a specific grace period, which is useful for projects that aren't used very often. If the user doesn't log in until after the password expires, the user can still connect, but a warning will display until the grace period expires. Use the PASSWORD_LIFE_TIME and PASSWORD_GRACE_TIME tags on a profile to enable these features.ALTER PROFILE myprofile LIMIT PASSWORD_LIFE_TIME 30 PASSWORD_GRACE_TIME 3;Users assigned to that profile will be locked out of their accounts 30 days after the last time the password is changed. After 30 days, attempting to log in will result in warning messages for three more days before the account is locked.Many users will see these limits and simply try to reset their passwords to what they were previously using rather than using a new password each time. You can prevent users from reusing a password with the PASSWORD_REUSE_TIME and PASSWORD_REUSE_MAX tags.ALTER PROFILE myprofile LIMIT PASSWORD_REUSE_TIME 30 PASSWORD_REUSE_MAX 100;Users with this profile will not be able to reuse a password for 30 days, or until after they change the password 100 times.Finally, some users will use passwords that are easy to guess. It's possible to restrict a password's format (such as checking for a minimum width, letters, numbers, or mixed case, or verifying that the password isn't a variation of the username) by creating a PL/SQL procedure that validates passwords. You must format the procedure like this:CREATE OR REPLACE FUNCTION verify_password( userid varchar(30), password varchar(30), old_password varchar(30)) RETURN BOOLEAN. . .You can assign this function (which can be any name, but it must be owned by the SYS account) with the following:ALTER PROFILE myprofile LIMIT PASSWORD_VERIFY_FUNCTION verify_password;7- what is the most important log file used by oracle instance it contains?alert log file8- write a sql statement to check the free space in each tablespaceThis is a great report for display the actual amount of free space within an Oracle tablespace.column "Tablespace" format a13column "Used MB" format 99,999,999column "Free MB" format 99,999,999colimn "Total MB" format 99,999,999select fs.tablespace_name "Tablespace", (df.totalspace - fs.freespace) "Used MB", fs.freespace "Free MB", df.totalspace "Total MB", round(100 * (fs.freespace / df.totalspace)) "Pct. Free"from (select tablespace_name, round(sum(bytes) / 1048576) TotalSpace from dba_data_files group by tablespace_name ) df, (select tablespace_name, round(sum(bytes) / 1048576) FreeSpace from dba_free_space group by tablespace_name ) fswhere df.tablespace_name = fs.tablespace_name;This SQL quickly compares the sum of the total space within each tablespace to the sum of the free space within each tablespace. Here is a sample of the output:SQL> @tsfreeTablespace Used MB Free MB Total MB Pct. Free------------- ----------- ----------- ----------- ----------RANNOD 6 44 50 88RANNOX 5 45 50 90RBOOKX 5 0 5 0SGROUPD 2 8 10 80SGROUPX 2 8 10 80BRBS 68 32 100 32RDRUSERD 2 18 20 909- write a sql statement to display db block sizesql>show parameter db_block_size10- write a oracle commend to open db for maintenancehttp://www.filibeto.org/sun/lib/nonsun/ora...ements_1004.htm11- write a sql statement to create new log grouphttp://www.idevelopment.info/data/Oracle/D...on/DBA_34.shtml12- write a sql statement to mirror a log file member13- write a sql statement to to create a locally managed tablespace14- write oracle commend convert oracle db to operate in archive log mode15- what is listener and what it is used for16- how can we configure a listener, and which file is used to configure on server client side?17- list all steps needed to tune a db?18- Which db system view contains memory allocation for a specific user session19- What is the difference between uga and pga20- what is the equation to calculate db hit ratio?21- what is the difference between logical and physical backups?22- how can we recover a db operating in archive log mode?23- how can we recover a db operating in no archive log mode?24- write export commend that will export full db?25- write export commend that will import emp table of user scott into user john from a dump file taken with full option?26- write a sql statement to display db character set?27- write a sql statement to create new user with all essential privileges for development user اقتباس رابط هذا التعليق شارك المزيد من خيارات المشاركة
a_oracle بتاريخ: 1 أبريل 2008 كاتب الموضوع تقديم بلاغ مشاركة بتاريخ: 1 أبريل 2008 بارك الله فيك اخي amrw2006 وجعله في ميزان حسناتك باذن اللهوبانتظار تكمله باقي الاسئله اقتباس رابط هذا التعليق شارك المزيد من خيارات المشاركة
a_oracle بتاريخ: 4 أبريل 2008 كاتب الموضوع تقديم بلاغ مشاركة بتاريخ: 4 أبريل 2008 بارك الله فيك اخي amrw2006 وجعله في ميزان حسناتك باذن اللهوبانتظار تكمله باقي الاسئله اقتباس رابط هذا التعليق شارك المزيد من خيارات المشاركة
ميمو2050 بتاريخ: 17 فبراير 2013 تقديم بلاغ مشاركة بتاريخ: 17 فبراير 2013 يسلمووووووووووووووووو اقتباس رابط هذا التعليق شارك المزيد من خيارات المشاركة
Recommended Posts
انضم إلى المناقشة
يمكنك المشاركة الآن والتسجيل لاحقاً. إذا كان لديك حساب, سجل دخولك الآن لتقوم بالمشاركة من خلال حسابك.