|
[ Pobierz całość w formacie PDF ]
.You mustdetermine the cause of the problem.Does the user's tablespace quota need to beincreased, or do you need to allocate more space to the tablespace? Prepare a step-by-step list that explains how you will gather the necessary information from the datadictionary.You do not need to list specific table or view names.1.Look up the error in your database documentation.2.Query the data dictionary for information on the table, its current size,tablespace quota on the user, and space allocated in the tablespace (thetablespace that holds the target table).3.Determine how much space the user needs to finish inserting the data.4.What is the real problem? Does the user's tablespace quota need to be increased,or do you need to allocate more space to the tablespace?5.If the user does not have a sufficient quota, then increase the quota.If thecurrent tablespace is filled, you may want to allocate more space or move thetarget table to a tablespace with more free space.6.You may decide not to increase the user's quota or not to allocate more spaceto the tablespace.In either case you may have to consider purging old data orarchiving the data off to tape.These steps are not irrevocable.Your action plan may vary depending upon yourcompany policy or your individual situation.Day 17, "Using SQL to Generate SQL Statements"Quiz Answers1.From which two sources can you generate SQL scripts?You can generate SQL scripts from database tables and the data dictionary.2.Will the following SQL statement work? Will the generated output work?SQL> SET ECHO OFF SQL> SET FEEDBACK OFFSQL> SPOOL CNT.SQLSQL> SELECT 'COUNT(*) FROM ' || TABLE_NAME || ';'2 FROM CAT3 /Yes the SQL statement will generate an SQL script, but the generated script willnot work.You need select 'select' in front of count(*):SELECT 'SELECT COUNT(*) FROM ' || TABLE_NAME || ';'Otherwise, your output will look like this:COUNT(*) FROM TABLE_NAME;which is not a valid SQL statement.3.Will the following SQL statement work? Will the generated output work?SQL> SET ECHO OFFSQL> SET FEEDBACK OFFSQL> SPOOL GRANT.SQLSQL> SELECT 'GRANT CONNECT DBA TO ' || USERNAME || ';'2 FROM SYS.DBA_USERS3 WHERE USERNAME NOT IN ('SYS','SYSTEM','SCOTT')4 /Once again, yes and no.The statement will generate an SQL script, but the SQLthat it generates will be incomplete.You need to add a comma between theprivileges CONNECT and DBA:SELECT 'GRANT CONNECT, DBA TO ' || USERNAME || ';'4.Will the following SQL statement work? Will the generated output work?SQL> SET ECHO OFFSQL> SET FEEDBACK OFFSQL> SELECT 'GRANT CONNECT, DBA TO ' || USERNAME || ';'2 FROM SYS.DBA_USERS3 WHERE USERNAME NOT IN ('SYS','SYSTEM','SCOTT')4 /Yes.The syntax of the main statement is valid, and the SQL that will begenerated will grant CONNECT and DBA to all users selected.5.True or False: It is best to set feedback on when generating SQL. False.You do not care how many rows are being selected, as that will not be partof the syntax of your generated statements.6.True or False: When generating SQL from SQL, always spool to a list or log filefor a record of what happened.False.You should spool to an.sql file, or whatever your naming convention isfor an SQL file.However, you may choose to spool within your generated file.7.True or False: Before generating SQL to truncate tables, you should alwaysmake sure you have a good backup of the tables.True.Just to be safe.8.What is the ed command?The ed command takes you into a full screen text editor.ed is very similar to vion a UNIX system and appears like a Windows Notepad file.9.What does the spool off command do?The spool off command closes an open spool file.Exercise Answers1.Using the SYS.DBA_USERS view (Personal Oracle7), create an SQL statementthat will generate a series of GRANT statements to five new users: John, Kevin,Ryan, Ron, and Chris.Use the column called USERNAME.Grant them Select accessto history_tbl.SQL> SET ECHO OFFSQL> SET FEEDBACK OFFSQL> SPOOL GRANTS.SQLSQL> SELECT 'GRANT SELECT ON HISTORY_TBL TO ' || USERNAME || ';'2 FROM SYS [ Pobierz całość w formacie PDF ] |
Archiwum
|